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.kla...@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=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.kla...@aklaver.com > >
