MIME-Version: 1.0
References: <CACW-z_UwPK8RP68ijs5Frn4+iREOSrN99-KFH6g0v3pFVVF1+w@mail.gmail.com>
 <CAPnRvGu-7B7J1i624KcJ-V2nUkuqGLsDT4Ae=EGbAPXJ=8A4fQ@mail.gmail.com>
 <f53c8d83-bf1b-4c04-a2ed-3ae5116aa582@aklaver.com> <a1ead53f-8348-4dbd-b9f1-6e9c4f2633f0@aklaver.com>
 <CACW-z_Xunr6cKL89h-uOE1sfvJ0E0thL+5k6NwT+B-PNq_=_=g@mail.gmail.com>
 <c35bd12e-a362-4089-9e80-a0696b3e9844@aklaver.com> <CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com>
 <61af3fbe-40db-4900-9144-380a5b305c74@aklaver.com>
In-Reply-To: <61af3fbe-40db-4900-9144-380a5b305c74@aklaver.com>
From: Sam Son <sam89.g@gmail.com>
Date: Fri, 6 Sep 2024 18:28:00 +0530
Message-ID: <CACW-z_VtyEEQoL1cUFSm=Mmb6h=D5Bt5Ht90r0BNeD1yjFS6_A@mail.gmail.com>
Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
To: Adrian Klaver <adrian.klaver@aklaver.com>
Cc: Muhammad Usman Khan <usman.k@bitnine.net>, pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000ac2a80062172f53d"
Archived-At: <https://www.postgresql.org/message-id/CACW-z_VtyEEQoL1cUFSm%3DMmb6h%3DD5Bt5Ht90r0BNeD1yjFS6_A%40mail.gmail.com>
Precedence: bulk

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

Thanks Adrian,

I have tried your previous workaround and it worked fine. I will try this
approach as well.

Regards,
Samson G

On Thu, Sep 5, 2024 at 3:50=E2=80=AFAM Adrian Klaver <adrian.klaver@aklaver=
.com>
wrote:

> On 9/4/24 10:46, Sam Son wrote:
> > Hi Adrian,
> >
> > Thanks for your suggestions. I will try your modifications and do
> > benchmarking.
>
> I got to thinking and realized I missed an important part about
> separating the schema and data restores using the -s and -a arguments.
> This is best explained here:
>
> https://www.postgresql.org/docs/current/app-pgrestore.html
>
> --section=3Dsectionname
>
>      Only restore the named section. The section name can be pre-data,
> data, or post-data. This option can be specified more than once to
> select multiple sections. The default is to restore all sections.
>
>      The data section contains actual table data as well as large-object
> definitions. Post-data items consist of definitions of indexes,
> triggers, rules and constraints other than validated check constraints.
> Pre-data items consist of all other data definition items.
>
>
> With the modification I suggested the -s argument will result in:
>
> -s
> --schema-only
>
>      Restore only the schema (data definitions), not data, to the extent
> that schema entries are present in the archive.
>
>      This option is the inverse of --data-only. It is similar to, but
> for historical reasons not identical to, specifying --section=3Dpre-data
> --section=3Dpost-data.
>
> The issue being it includes post-data definitions as in:
>
> "Post-data items consist of definitions of indexes, triggers, rules and
> constraints other than validated check constraints. "
>
> That means when you restore the output of pg_restore -a the above items
> will be in place and will run. Among other things if there are trigger
> functions using plpython3u and said functions are not Python3 valid they
> will fail. You might also get warnings like:
>
> "
> pg_dump: warning: there are circular foreign-key constraints on this tabl=
e:
> pg_dump: detail: equipment
> pg_dump: hint: You might not be able to restore the dump without using
> --disable-triggers or temporarily dropping the constraints.
> pg_dump: hint: Consider using a full dump instead of a --data-only dump
> to avoid this problem.
> "
>
> You might be better off using something like:
>
> pg_restore ... --section=3Dpre-data  -f ddl_defs.sql
> Search/replace ddl_defs.sql
> psql ... -f ddl_defs.sql
> pg_restore ... --section=3Ddata
> pg_restore ... --section=3Dpost-data
>
>
>
> >
> > Thanks,
> > Samson G
>
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com
>
>

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

<div dir=3D"ltr">Thanks Adrian,=C2=A0<div><br></div><div>I have tried your =
previous workaround and it worked fine. I will try this approach as well.</=
div><div><br></div><div>Regards,</div><div>Samson G</div></div><br><div cla=
ss=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Sep 5, 202=
4 at 3:50=E2=80=AFAM Adrian Klaver &lt;<a href=3D"mailto:adrian.klaver@akla=
ver.com">adrian.klaver@aklaver.com</a>&gt; wrote:<br></div><blockquote clas=
s=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid r=
gb(204,204,204);padding-left:1ex">On 9/4/24 10:46, Sam Son wrote:<br>
&gt; Hi Adrian,<br>
&gt; <br>
&gt; Thanks for your suggestions. I will try your modifications and do <br>
&gt; benchmarking.<br>
<br>
I got to thinking and realized I missed an important part about <br>
separating the schema and data restores using the -s and -a arguments. <br>
This is best explained here:<br>
<br>
<a href=3D"https://www.postgresql.org/docs/current/app-pgrestore.html" rel=
=3D"noreferrer" target=3D"_blank">https://www.postgresql.org/docs/current/a=
pp-pgrestore.html</a><br>
<br>
--section=3Dsectionname<br>
<br>
=C2=A0 =C2=A0 =C2=A0Only restore the named section. The section name can be=
 pre-data, <br>
data, or post-data. This option can be specified more than once to <br>
select multiple sections. The default is to restore all sections.<br>
<br>
=C2=A0 =C2=A0 =C2=A0The data section contains actual table data as well as =
large-object <br>
definitions. Post-data items consist of definitions of indexes, <br>
triggers, rules and constraints other than validated check constraints. <br=
>
Pre-data items consist of all other data definition items.<br>
<br>
<br>
With the modification I suggested the -s argument will result in:<br>
<br>
-s<br>
--schema-only<br>
<br>
=C2=A0 =C2=A0 =C2=A0Restore only the schema (data definitions), not data, t=
o the extent <br>
that schema entries are present in the archive.<br>
<br>
=C2=A0 =C2=A0 =C2=A0This option is the inverse of --data-only. It is simila=
r to, but <br>
for historical reasons not identical to, specifying --section=3Dpre-data <b=
r>
--section=3Dpost-data.<br>
<br>
The issue being it includes post-data definitions as in:<br>
<br>
&quot;Post-data items consist of definitions of indexes, triggers, rules an=
d <br>
constraints other than validated check constraints. &quot;<br>
<br>
That means when you restore the output of pg_restore -a the above items <br=
>
will be in place and will run. Among other things if there are trigger <br>
functions using plpython3u and said functions are not Python3 valid they <b=
r>
will fail. You might also get warnings like:<br>
<br>
&quot;<br>
pg_dump: warning: there are circular foreign-key constraints on this table:=
<br>
pg_dump: detail: equipment<br>
pg_dump: hint: You might not be able to restore the dump without using <br>
--disable-triggers or temporarily dropping the constraints.<br>
pg_dump: hint: Consider using a full dump instead of a --data-only dump <br=
>
to avoid this problem.<br>
&quot;<br>
<br>
You might be better off using something like:<br>
<br>
pg_restore ... --section=3Dpre-data=C2=A0 -f ddl_defs.sql<br>
Search/replace ddl_defs.sql<br>
psql ... -f ddl_defs.sql<br>
pg_restore ... --section=3Ddata<br>
pg_restore ... --section=3Dpost-data<br>
<br>
<br>
<br>
&gt; <br>
&gt; Thanks,<br>
&gt; Samson G<br>
<br>
<br>
-- <br>
Adrian Klaver<br>
<a href=3D"mailto:adrian.klaver@aklaver.com" target=3D"_blank">adrian.klave=
r@aklaver.com</a><br>
<br>
</blockquote></div>

--000000000000ac2a80062172f53d--