Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16

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 AM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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=sectionname
>
> 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=pre-data
> --section=post-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 table:
> 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=pre-data -f ddl_defs.sql
> Search/replace ddl_defs.sql
> psql ... -f ddl_defs.sql
> pg_restore ... --section=data
> pg_restore ... --section=post-data
>
>
>
> >
> > Thanks,
> > Samson G
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>
>