MIME-Version: 1.0
In-Reply-To: <Zte4r3rQ9_GbCZe-@raf.org>
References: <Zte4r3rQ9_GbCZe-@raf.org>
From: "David G. Johnston" <david.g.johnston@gmail.com>
Date: Tue, 3 Sep 2024 18:43:22 -0700
Message-ID: <CAKFQuwZsJ+oGEiyxFLppF7c-JU5=00u0pwZdqjqrnV94uL6oUA@mail.gmail.com>
Subject: Re: Help with "gpg -d ... | pg_restore ..." with unimportant
 pg_restore errors
To: raf <postgres@raf.org>
Cc: "pgsql-general@postgresql.org" <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000ad48630621414c29"
Archived-At: <https://www.postgresql.org/message-id/CAKFQuwZsJ%2BoGEiyxFLppF7c-JU5%3D00u0pwZdqjqrnV94uL6oUA%40mail.gmail.com>
Precedence: bulk

--000000000000ad48630621414c29
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, September 3, 2024, raf <postgres@raf.org> 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=E2=80=A6


>   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 erro=
r
> that
> would happen next). I think those functions are ancient and irrelevant an=
d
> not
> in use, and I'm happy for pg_restore to continue, like it does when gpg i=
s
> not
> involved.


You specified =E2=80=9C-1=E2=80=9D so I don=E2=80=99t get why you believe p=
g_restore should be
continuing to execute in the face of the SQL error.

David J.

--000000000000ad48630621414c29
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tuesday, September 3, 2024, raf &lt;<a href=3D"mailto:postgres@raf.org">=
postgres@raf.org</a>&gt; wrote:<br><blockquote class=3D"gmail_quote" style=
=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">Hi,<br>
<br>
I need help!<br>
<br>
I&#39;m upgrading an ancient (but still awesome) postgresql-9.6.24 (via Ent=
erpriseDB)<br>
to (a no doubt even more awesome) postgresql-15.8 (via debian (stable) pack=
ages)<br>
but am unable to load database backups that were encrypted via gpg. Loading=
 from <br>
unencrypted backups works fine (and the millions of tests all pass! Yay!).<=
br>
<br>
I have a convenience program for handling loading called &quot;load&quot;<b=
r>
and the underlying commands that it executes look like this:<br>
<br>
=C2=A0 dropdb -h payroll -p 5433 -U postgres payroll_tst<br>
=C2=A0 createdb -h payroll -p 5433 -U postgres -T template0 -E utf8 -O admi=
n payroll_tst</blockquote><div><br></div><div>Given the following command</=
div><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 =
0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
=C2=A0 gpg --decrypt 20240904-011254-payroll_tst.<wbr>pgdump.gpg.aps24 | pg=
_restore -1 -h payroll -p 5433 -U postgres -d payroll_tst -Fc</blockquote><=
div><br></div><div>And this error</div><div>=C2=A0</div><blockquote class=
=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padd=
ing-left:1ex">
<br>=C2=A0 pg_restore: [archiver (db)] could not execute query: ERROR:=C2=
=A0 could not find function &quot;xml_is_well_formed&quot; in file &quot;/u=
sr/lib/postgresql/15/lib/<wbr>pgxml.so&quot;<br>
=C2=A0 =C2=A0 =C2=A0 Command was: CREATE FUNCTION public.xml_is_well_formed=
(<wbr>text) RETURNS boolean<br>
=C2=A0 =C2=A0 =C2=A0 LANGUAGE c IMMUTABLE STRICT<br>
=C2=A0 =C2=A0 =C2=A0 AS &#39;$libdir/pgxml&#39;, &#39;xml...</blockquote><d=
iv><br></div><div>This should be expected.=C2=A0 In particular=E2=80=A6</di=
v><div>=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 =
.8ex;border-left:1px #ccc solid;padding-left:1ex">
=C2=A0 gpg: error writing to &#39;-&#39;: Broken pipe<br>
=C2=A0 gpg: error flushing &#39;[stdout]&#39;: Broken pipe<br>
=C2=A0 gpg: handle plaintext failed: Broken pipe<br>
=C2=A0 pgrestore encountered errors<br>
<br>
I&#39;m not worried about the xml_is_well_formed error (or the xml_valid er=
ror that<br>
would happen next). I think those functions are ancient and irrelevant and =
not<br>
in use, and I&#39;m happy for pg_restore to continue, like it does when gpg=
 is not<br>
involved.</blockquote><div><br></div><div>You specified =E2=80=9C-1=E2=80=
=9D so I don=E2=80=99t get why you believe pg_restore should be continuing =
to execute in the face of the SQL error.</div><div><br></div><div>David J.<=
/div><div><br></div>

--000000000000ad48630621414c29--