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