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:
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. "
