I have this table which contains about 250 partitions, and each partition contains 4 sub partitions (so 1,000 partitions in total), and I'm trying to get the DDLs for those partitions.
Pg_dump version: 16.4
Remote database version: 16.1
Here is the command:
pg_dump -h host -U user --table-and-children=schema.table --schema-only -v database > file.sql
This hangs for several minutes on saving search_path.
Full logs:
pg_dump: last built-in OID is 16383 pg_dump: reading extensions pg_dump: identifying extension members pg_dump: reading schemas pg_dump: reading user-defined tables pg_dump: reading user-defined functions pg_dump: reading user-defined types pg_dump: reading procedural languages pg_dump: reading user-defined aggregate functions pg_dump: reading user-defined operators pg_dump: reading user-defined access methods pg_dump: reading user-defined operator classes pg_dump: reading user-defined operator families pg_dump: reading user-defined text search parsers pg_dump: reading user-defined text search templates pg_dump: reading user-defined text search dictionaries pg_dump: reading user-defined text search configurations pg_dump: reading user-defined foreign-data wrappers pg_dump: reading user-defined foreign servers pg_dump: reading default privileges pg_dump: reading user-defined collations pg_dump: reading user-defined conversions pg_dump: reading type casts pg_dump: reading transforms pg_dump: reading table inheritance information pg_dump: reading event triggers pg_dump: finding extension tables pg_dump: finding inheritance relationships pg_dump: reading column info for interesting tables pg_dump: flagging inherited columns in subtables pg_dump: reading partitioning data pg_dump: reading indexes pg_dump: flagging indexes in partitioned tables pg_dump: reading extended statistics pg_dump: reading constraints pg_dump: reading triggers pg_dump: reading rewrite rules pg_dump: reading policies pg_dump: reading row-level security policies pg_dump: reading publications pg_dump: reading publication membership of tables pg_dump: reading publication membership of schemas pg_dump: reading subscriptions pg_dump: reading dependency data pg_dump: saving encoding = UTF8 pg_dump: saving standard_conforming_strings = on pg_dump: saving search_path = If I try the same command but without asking for the partitions, it works and takes about 2 seconds:
pg_dump -h host -U user -t schema.table --schema-only -v database > file.sql
I also tried to use dbeaver's "Generate SQL > DDL > "Show partitions DDL" and it doesn't seem to work either (it hangs on 0% for several minutes), probably for the same reason.
No idea if it's a bug in pg_dump (the option is fairly new) or if something is wrong in the database or if I just need to wait for 1 hour. I feel like it should take some time but I don't see why it would hang on "saving search_path".
