public inbox for [email protected]  

help / color / mirror / Atom feed
From: Sam Son <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Muhammad Usman Khan <[email protected]>
Cc: [email protected]
Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
Date: Fri, 6 Sep 2024 18:28:00 +0530
Message-ID: <CACW-z_VtyEEQoL1cUFSm=Mmb6h=D5Bt5Ht90r0BNeD1yjFS6_A@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CACW-z_UwPK8RP68ijs5Frn4+iREOSrN99-KFH6g0v3pFVVF1+w@mail.gmail.com>
	<CAPnRvGu-7B7J1i624KcJ-V2nUkuqGLsDT4Ae=EGbAPXJ=8A4fQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<CACW-z_Xunr6cKL89h-uOE1sfvJ0E0thL+5k6NwT+B-PNq_=_=g@mail.gmail.com>
	<[email protected]>
	<CACW-z_W2cf9O4ZJyPLYugBatBf3SDpQTPj2JSGD-9C8VH8N9ZA@mail.gmail.com>
	<[email protected]>

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 <[email protected]>
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
> [email protected]
>
>


view thread (9+ messages)  latest in thread

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Issue with Restore dump with plpythonu, plpython3u installed on postgres16
  In-Reply-To: <CACW-z_VtyEEQoL1cUFSm=Mmb6h=D5Bt5Ht90r0BNeD1yjFS6_A@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox