public inbox for [email protected]help / color / mirror / Atom feed
Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors 3+ messages / 2 participants [nested] [flat]
* Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors @ 2024-09-04 02:23 raf <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: raf @ 2024-09-04 02:23 UTC (permalink / raw) To: [email protected] On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <[email protected]> wrote: > On Tuesday, September 3, 2024, raf <[email protected]> wrote: > > > Hi, > > > > I need help! > > > > I'm upgrading an ancient (but still awesome) postgresql-9.6.24 (via > > EnterpriseDB) > > to (a no doubt even more awesome) postgresql-15.8 (via debian (stable) > > packages) > > but am unable to load database backups that were encrypted via gpg. > > Loading from > > unencrypted backups works fine (and the millions of tests all pass! Yay!). > > > > I have a convenience program for handling loading called "load" > > and the underlying commands that it executes look like this: > > > > dropdb -h payroll -p 5433 -U postgres payroll_tst > > createdb -h payroll -p 5433 -U postgres -T template0 -E utf8 -O admin > > payroll_tst > > Given the following command > > > gpg --decrypt 20240904-011254-payroll_tst.pgdump.gpg.aps24 | pg_restore > > -1 -h payroll -p 5433 -U postgres -d payroll_tst -Fc > > And this error > > > pg_restore: [archiver (db)] could not execute query: ERROR: could not > > find function "xml_is_well_formed" in file "/usr/lib/postgresql/15/lib/ > > pgxml.so" > > Command was: CREATE FUNCTION public.xml_is_well_formed(text) > > RETURNS boolean > > LANGUAGE c IMMUTABLE STRICT > > AS '$libdir/pgxml', 'xml... > > This should be expected. In particular… > > > gpg: error writing to '-': Broken pipe > > gpg: error flushing '[stdout]': Broken pipe > > gpg: handle plaintext failed: Broken pipe > > pgrestore encountered errors > > > > I'm not worried about the xml_is_well_formed error (or the xml_valid > > error that would happen next). I think those functions are ancient > > and irrelevant and not in use, and I'm happy for pg_restore to > > continue, like it does when gpg is not involved. > > You specified “-1” so I don’t get why you believe pg_restore should be > continuing to execute in the face of the SQL error. The reason I believe pg_restore should be continuing to execute in the face of the SQL error is because I didn't supply the -e option which is described thusly in the pg_restore manual entry: -e --exit-on-error Exit if an error is encountered while sending SQL commands to the database. The default is to continue and to display a count of errors at the end of the restoration. So, since I didn't specify the -e option, pg_restore should continue to execute, and not close stdin. As I explained, when restoring from a file on disk, the pg_restore command does continue and work fine. It's only when restoring from stdin that I'm seeing this problem. Ah, I see. The manual entry also says that -1 implies -e. And when reading from a file on disk, my load script doesn't include -1. Instead, it uses the -j option. Now it all makes sense. Many thanks. It's working without the -1. I'll change the load script so that it only uses the -1 option when restoring from new backups taken after the upgrade to 15.8 (where these vestigial xml functions won't be present in the backup). > David J. cheers, raf ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors @ 2024-09-04 02:28 Tom Lane <[email protected]> parent: raf <[email protected]> 0 siblings, 1 reply; 3+ messages in thread From: Tom Lane @ 2024-09-04 02:28 UTC (permalink / raw) To: raf <[email protected]>; +Cc: [email protected] raf <[email protected]> writes: > On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <[email protected]> wrote: >> You specified “-1” so I don’t get why you believe pg_restore should be >> continuing to execute in the face of the SQL error. > The reason I believe pg_restore should be continuing to execute in the face of > the SQL error is because I didn't supply the -e option which is described > thusly in the pg_restore manual entry: But you'd better also read the para about -1: -1 --single-transaction Execute the restore as a single transaction (that is, wrap the emitted commands in BEGIN/COMMIT). This ensures that either all the commands complete successfully, or no changes are applied. This option implies --exit-on-error. regards, tom lane ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors @ 2024-09-04 02:39 raf <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 3+ messages in thread From: raf @ 2024-09-04 02:39 UTC (permalink / raw) To: [email protected] On Tue, Sep 03, 2024 at 10:28:44PM -0400, Tom Lane <[email protected]> wrote: > raf <[email protected]> writes: > > On Tue, Sep 03, 2024 at 06:43:22PM -0700, "David G. Johnston" <[email protected]> wrote: > >> You specified “-1” so I don’t get why you believe pg_restore should be > >> continuing to execute in the face of the SQL error. > > > The reason I believe pg_restore should be continuing to execute in the face of > > the SQL error is because I didn't supply the -e option which is described > > thusly in the pg_restore manual entry: > > But you'd better also read the para about -1: > > -1 > --single-transaction > Execute the restore as a single transaction (that is, wrap the > emitted commands in BEGIN/COMMIT). This ensures that either all > the commands complete successfully, or no changes are > applied. This option implies --exit-on-error. > > regards, tom lane > Yes, I saw that. Many thanks. All good now. cheer, raf ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2024-09-04 02:39 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-04 02:23 Re: Help with "gpg -d ... | pg_restore ..." with unimportant pg_restore errors raf <[email protected]> 2024-09-04 02:28 ` Tom Lane <[email protected]> 2024-09-04 02:39 ` raf <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox