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.klaver@xxxxxxxxxxx
