PostgreSQL DBA(146) - pgAdmin（pg_dumpall vs pg_dump）

本节介绍了PostgreSQL中逻辑备份的工具pg_dumpall和pg_dump。
pg_dumpall和pg_dump都是PG提供的逻辑备份工具，顾名思义，pg_dumpall可以一键dump所有的数据库，而pg_dump只能逐个database处理，下面来详细对比两个工具的异同。

pg_dumpall
pg_dumpall可以一键dump database cluster，dump文件的格式为plain text file，可通过psql直接读取处理。同时pg_dumpall会dump全局的数据，包括角色、表空间等。但pg_dumpall存在一些问题：
1.dump文件很大：只能存储为plain格式，由于没有压缩，存储原始的数据，dump文件的size会很大；
2.dump的性能较慢：使用pg_dumpall，由于无法使用并行只能逐个处理，因此dump的过程会比较慢；
3.部分恢复很困难：由于所有数据均存储在一个文件中，因此难以进行部分的恢复。
使用-v选项，可看到执行pg_dumpall的输出

[pg12@localhost ~]$ pg_dumpall -v -f /tmp/dumpall.file
pg_dumpall: executing SELECT pg_catalog.set_config('search_path', '', false);
pg_dumpall: executing SELECT oid, rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, rolreplication, rolbypassrls, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment, rolname = current_user AS is_current_user FROM pg_authid WHERE rolname !~ '^pg_' ORDER BY 2
pg_dumpall: executing SELECT provider, label FROM pg_catalog.pg_shseclabel WHERE classoid = 'pg_catalog.pg_authid'::pg_catalog.regclass AND objoid = '10'
pg_dumpall: executing SELECT setconfig[1] FROM pg_db_role_setting WHERE setdatabase = 0 AND setrole = (SELECT oid FROM pg_authid WHERE rolname = 'pg12')
pg_dumpall: executing SELECT ur.rolname AS roleid, um.rolname AS member, a.admin_option, ug.rolname AS grantor FROM pg_auth_members a LEFT JOIN pg_authid ur on ur.oid = a.roleid LEFT JOIN pg_authid um on um.oid = a.member LEFT JOIN pg_authid ug on ug.oid = a.grantor WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')ORDER BY 1,2,3
pg_dumpall: executing SELECT oid, spcname, pg_catalog.pg_get_userbyid(spcowner) AS spcowner, pg_catalog.pg_tablespace_location(oid), (SELECT array_agg(acl ORDER BY row_n) FROM   (SELECT acl, row_n FROM      unnest(coalesce(spcacl,acldefault('t',spcowner)))      WITH ORDINALITY AS perm(acl,row_n)    WHERE NOT EXISTS (      SELECT 1      FROM unnest(acldefault('t',spcowner))        AS init(init_acl)      WHERE acl = init_acl)) AS spcacls)  AS spcacl, (SELECT array_agg(acl ORDER BY row_n) FROM   (SELECT acl, row_n FROM      unnest(acldefault('t',spcowner))      WITH ORDINALITY AS initp(acl,row_n)    WHERE NOT EXISTS (      SELECT 1      FROM unnest(coalesce(spcacl,acldefault('t',spcowner)))        AS permp(orig_acl)      WHERE acl = orig_acl)) AS rspcacls)  AS rspcacl, array_to_string(spcoptions, ', '),pg_catalog.shobj_description(oid, 'pg_tablespace') FROM pg_catalog.pg_tablespace WHERE spcname !~ '^pg_' ORDER BY 1
pg_dumpall: executing SELECT datname FROM pg_database d WHERE datallowconn ORDER BY (datname <> 'template1'), datname
pg_dumpall: dumping database "template1"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file  -Fa ' dbname=template1'"
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 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 publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: implied data-only restore
pg_dumpall: dumping database "db1"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file --create -Fa ' dbname=db1'"
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: finding the columns and types of table "public.t_autovacuum_db1"
pg_dump: flagging inherited columns in subtables
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 security enabled for table "public.t_autovacuum_db1"
pg_dump: reading policies for table "public.t_autovacuum_db1"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "public.t_autovacuum_db1"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: creating DATABASE "db1"
pg_dump: connecting to new database "db1"
pg_dump: creating TABLE "public.t_autovacuum_db1"
pg_dump: processing data for table "public.t_autovacuum_db1"
pg_dump: dumping contents of table "public.t_autovacuum_db1"
pg_dumpall: dumping database "db2"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file --create -Fa ' dbname=db2'"
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: finding the columns and types of table "public.t_autovacuum_db3"
pg_dump: finding the columns and types of table "public.t_autovacuum_db2"
pg_dump: flagging inherited columns in subtables
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 security enabled for table "public.t_autovacuum_db3"
pg_dump: reading policies for table "public.t_autovacuum_db3"
pg_dump: reading row security enabled for table "public.t_autovacuum_db2"
pg_dump: reading policies for table "public.t_autovacuum_db2"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "public.t_autovacuum_db3"
pg_dump: reading publication membership for table "public.t_autovacuum_db2"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: creating DATABASE "db2"
pg_dump: connecting to new database "db2"
pg_dump: creating TABLE "public.t_autovacuum_db2"
pg_dump: creating TABLE "public.t_autovacuum_db3"
pg_dump: processing data for table "public.t_autovacuum_db2"
pg_dump: dumping contents of table "public.t_autovacuum_db2"
pg_dump: processing data for table "public.t_autovacuum_db3"
pg_dump: dumping contents of table "public.t_autovacuum_db3"
pg_dumpall: dumping database "db3"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file --create -Fa ' dbname=db3'"
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: finding the columns and types of table "public.t_autovacuum_db3"
pg_dump: flagging inherited columns in subtables
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 security enabled for table "public.t_autovacuum_db3"
pg_dump: reading policies for table "public.t_autovacuum_db3"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "public.t_autovacuum_db3"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: creating DATABASE "db3"
pg_dump: connecting to new database "db3"
pg_dump: creating TABLE "public.t_autovacuum_db3"
pg_dump: processing data for table "public.t_autovacuum_db3"
pg_dump: dumping contents of table "public.t_autovacuum_db3"
pg_dumpall: dumping database "postgres"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file  -Fa ' dbname=postgres'"
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 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 publications
pg_dump: reading publication membership
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: implied data-only restore
pg_dumpall: dumping database "testdb"
pg_dumpall: running ""/appdb/pg12/pg12.1/bin/pg_dump"  -v -f /tmp/dumpall.file --create -Fa ' dbname=testdb'"
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: finding the columns and types of table "public.a"
pg_dump: finding the columns and types of table "public.b"
pg_dump: finding the columns and types of table "public.t_count"
pg_dump: finding the columns and types of table "public.rel"
pg_dump: finding the columns and types of table "public.t1"
pg_dump: finding the columns and types of table "public.tbl"
pg_dump: finding the columns and types of table "public.t2"
pg_dump: finding the columns and types of table "public.t_fillfactor_100"
pg_dump: finding the columns and types of table "public.t_fillfactor_70"
pg_dump: finding the columns and types of table "public.t_fillfactor_50"
pg_dump: finding the columns and types of table "public.t_autovacuum_1"
pg_dump: finding the columns and types of table "public.t_big_autovacuum_1"
pg_dump: finding the columns and types of table "public.t_tx"
pg_dump: finding the columns and types of table "public.t_mvcc"
pg_dump: finding the columns and types of table "public.t"
pg_dump: finding the columns and types of table "public.t_autovacuum_db1"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "public.a"
pg_dump: reading indexes for table "public.b"
pg_dump: reading indexes for table "public.rel"
pg_dump: flagging indexes in partitioned tables
pg_dump: reading extended statistics
pg_dump: reading constraints
pg_dump: reading foreign key constraints for table "public.a"
pg_dump: reading foreign key constraints for table "public.b"
pg_dump: reading triggers
pg_dump: reading triggers for table "public.a"
pg_dump: reading triggers for table "public.b"
pg_dump: reading rewrite rules
pg_dump: reading policies
pg_dump: reading row security enabled for table "public.a"
pg_dump: reading policies for table "public.a"
pg_dump: reading row security enabled for table "public.b"
pg_dump: reading policies for table "public.b"
pg_dump: reading row security enabled for table "public.t_count"
pg_dump: reading policies for table "public.t_count"
pg_dump: reading row security enabled for table "public.rel"
pg_dump: reading policies for table "public.rel"
pg_dump: reading row security enabled for table "public.t1"
pg_dump: reading policies for table "public.t1"
pg_dump: reading row security enabled for table "public.tbl"
pg_dump: reading policies for table "public.tbl"
pg_dump: reading row security enabled for table "public.t2"
pg_dump: reading policies for table "public.t2"
pg_dump: reading row security enabled for table "public.t_fillfactor_100"
pg_dump: reading policies for table "public.t_fillfactor_100"
pg_dump: reading row security enabled for table "public.t_fillfactor_70"
pg_dump: reading policies for table "public.t_fillfactor_70"
pg_dump: reading row security enabled for table "public.t_fillfactor_50"
pg_dump: reading policies for table "public.t_fillfactor_50"
pg_dump: reading row security enabled for table "public.t_autovacuum_1"
pg_dump: reading policies for table "public.t_autovacuum_1"
pg_dump: reading row security enabled for table "public.t_big_autovacuum_1"
pg_dump: reading policies for table "public.t_big_autovacuum_1"
pg_dump: reading row security enabled for table "public.t_tx"
pg_dump: reading policies for table "public.t_tx"
pg_dump: reading row security enabled for table "public.t_mvcc"
pg_dump: reading policies for table "public.t_mvcc"
pg_dump: reading row security enabled for table "public.t"
pg_dump: reading policies for table "public.t"
pg_dump: reading row security enabled for table "public.t_autovacuum_db1"
pg_dump: reading policies for table "public.t_autovacuum_db1"
pg_dump: reading publications
pg_dump: reading publication membership
pg_dump: reading publication membership for table "public.a"
pg_dump: reading publication membership for table "public.b"
pg_dump: reading publication membership for table "public.t_count"
pg_dump: reading publication membership for table "public.rel"
pg_dump: reading publication membership for table "public.t1"
pg_dump: reading publication membership for table "public.tbl"
pg_dump: reading publication membership for table "public.t2"
pg_dump: reading publication membership for table "public.t_fillfactor_100"
pg_dump: reading publication membership for table "public.t_fillfactor_70"
pg_dump: reading publication membership for table "public.t_fillfactor_50"
pg_dump: reading publication membership for table "public.t_autovacuum_1"
pg_dump: reading publication membership for table "public.t_big_autovacuum_1"
pg_dump: reading publication membership for table "public.t_tx"
pg_dump: reading publication membership for table "public.t_mvcc"
pg_dump: reading publication membership for table "public.t"
pg_dump: reading publication membership for table "public.t_autovacuum_db1"
pg_dump: reading subscriptions
pg_dump: reading large objects
pg_dump: reading dependency data
pg_dump: saving encoding = UTF8
pg_dump: saving standard_conforming_strings = on
pg_dump: saving search_path = 
pg_dump: saving database definition
pg_dump: creating DATABASE "testdb"
pg_dump: connecting to new database "testdb"
pg_dump: creating TABLE "public.a"
pg_dump: creating TABLE "public.b"
pg_dump: creating TABLE "public.rel"
pg_dump: creating TABLE "public.t"
pg_dump: creating TABLE "public.t1"
pg_dump: creating TABLE "public.t2"
pg_dump: creating TABLE "public.t_autovacuum_1"
pg_dump: creating TABLE "public.t_autovacuum_db1"
pg_dump: creating TABLE "public.t_big_autovacuum_1"
pg_dump: creating TABLE "public.t_count"
pg_dump: creating TABLE "public.t_fillfactor_100"
pg_dump: creating TABLE "public.t_fillfactor_50"
pg_dump: creating TABLE "public.t_fillfactor_70"
pg_dump: creating TABLE "public.t_mvcc"
pg_dump: creating TABLE "public.t_tx"
pg_dump: creating TABLE "public.tbl"
pg_dump: processing data for table "public.a"
pg_dump: dumping contents of table "public.a"
pg_dump: processing data for table "public.b"
pg_dump: dumping contents of table "public.b"
pg_dump: processing data for table "public.rel"
pg_dump: dumping contents of table "public.rel"
pg_dump: processing data for table "public.t"
pg_dump: dumping contents of table "public.t"
pg_dump: processing data for table "public.t1"
pg_dump: dumping contents of table "public.t1"
pg_dump: processing data for table "public.t2"
pg_dump: dumping contents of table "public.t2"
pg_dump: processing data for table "public.t_autovacuum_1"
pg_dump: dumping contents of table "public.t_autovacuum_1"
pg_dump: processing data for table "public.t_autovacuum_db1"
pg_dump: dumping contents of table "public.t_autovacuum_db1"
pg_dump: processing data for table "public.t_big_autovacuum_1"
pg_dump: dumping contents of table "public.t_big_autovacuum_1"
pg_dump: processing data for table "public.t_count"
pg_dump: dumping contents of table "public.t_count"
pg_dump: processing data for table "public.t_fillfactor_100"
pg_dump: dumping contents of table "public.t_fillfactor_100"
pg_dump: processing data for table "public.t_fillfactor_50"
pg_dump: dumping contents of table "public.t_fillfactor_50"
pg_dump: processing data for table "public.t_fillfactor_70"
pg_dump: dumping contents of table "public.t_fillfactor_70"
pg_dump: processing data for table "public.t_mvcc"
pg_dump: dumping contents of table "public.t_mvcc"
pg_dump: processing data for table "public.t_tx"
pg_dump: dumping contents of table "public.t_tx"
pg_dump: processing data for table "public.tbl"
pg_dump: dumping contents of table "public.tbl"
pg_dump: creating CONSTRAINT "public.a a_pkey"
pg_dump: creating CONSTRAINT "public.b b_pkey"
pg_dump: creating CONSTRAINT "public.rel rel_pkey"
pg_dump: creating INDEX "public.rel_bid_idx"
pg_dump: creating FK CONSTRAINT "public.b b_id_fkey"
[pg12@localhost ~]$

执行恢复的时候使用psql -f指定dump文件

psql -f /tmp/dumpall.file -v ON_ERROR_STOP=1

>

pg_dump
pg_dump可指定导出为p-plain、c-custom、d-directory和t-tar格式，其中plain格式与dumpall格式一样，内容一样，执行恢复操作一样；另外三种格式使用pg_restore工具恢复。

[pg12@localhost ~]$ for format in p c t d
> do
>     echo "Format: $format"
>     time pg_dump -F $format -C -f /tmp/dump-$format testdb
> done
Format: p
real    0m41.519s
user    0m4.639s
sys    0m2.161s
Format: c
real    0m46.518s
user    0m16.371s
sys    0m0.819s
Format: t
real    0m41.471s
user    0m1.882s
sys    0m5.433s
Format: d
real    0m44.775s
user    0m15.106s
sys    0m0.816s

dump输出的文件

[pg12@localhost tmp]$ ll dump*
-rw-rw-r-- 1 pg12 pg12 109859714 Dec 16 15:53 dump-c
-rw-rw-r-- 1 pg12 pg12 788479411 Dec 16 15:52 dump-p
-rw-rw-r-- 1 pg12 pg12 788505088 Dec 16 15:54 dump-t
dump-d:
total 107196
-rw-rw-r-- 1 pg12 pg12 22047519 Dec 16 15:54 3154.dat.gz
-rw-rw-r-- 1 pg12 pg12 22047519 Dec 16 15:54 3155.dat.gz
-rw-rw-r-- 1 pg12 pg12     1888 Dec 16 15:54 3156.dat.gz
-rw-rw-r-- 1 pg12 pg12 47020645 Dec 16 15:54 3157.dat.gz
-rw-rw-r-- 1 pg12 pg12      355 Dec 16 15:54 3158.dat.gz
-rw-rw-r-- 1 pg12 pg12   345673 Dec 16 15:55 3159.dat.gz
-rw-rw-r-- 1 pg12 pg12      334 Dec 16 15:54 3160.dat.gz
-rw-rw-r-- 1 pg12 pg12  2846130 Dec 16 15:54 3161.dat.gz
-rw-rw-r-- 1 pg12 pg12  2901024 Dec 16 15:55 3162.dat.gz
-rw-rw-r-- 1 pg12 pg12  2924214 Dec 16 15:54 3163.dat.gz
-rw-rw-r-- 1 pg12 pg12    49356 Dec 16 15:54 3164.dat.gz
-rw-rw-r-- 1 pg12 pg12  7062059 Dec 16 15:54 3165.dat.gz
-rw-rw-r-- 1 pg12 pg12       31 Dec 16 15:55 3166.dat.gz
-rw-rw-r-- 1 pg12 pg12       27 Dec 16 15:55 3167.dat.gz
-rw-rw-r-- 1 pg12 pg12     2950 Dec 16 15:54 3168.dat.gz
-rw-rw-r-- 1 pg12 pg12  2466838 Dec 16 15:54 3169.dat.gz
-rw-rw-r-- 1 pg12 pg12     9152 Dec 16 15:54 toc.dat

使用c和d选项，数据大概是p和t选项的15%左右。

在恢复时，如需要执行部分恢复，可通过pg_restore的-l选项列出dump文件中的内容并输入到metadata文件中，编辑相应的metadata文件，可指定需要恢复的数据表。

[pg12@localhost ~]$ pg_restore -l /tmp/dump-c
;
; Archive created at 2019-12-16 15:52:50 CST
;     dbname: testdb
;     TOC Entries: 41
;     Compression: -1
;     Dump Version: 1.14-0
;     Format: CUSTOM
;     Integer: 4 bytes
;     Offset: 8 bytes
;     Dumped from database version: 12.1
;     Dumped by pg_dump version: 12.1
;
;
; Selected TOC Entries:
;
202; 1259 16385 TABLE public a pg12
203; 1259 16391 TABLE public b pg12
205; 1259 40967 TABLE public rel pg12
216; 1259 66582 TABLE public t pg12
206; 1259 49634 TABLE public t1 pg12
208; 1259 58321 TABLE public t2 pg12
212; 1259 58360 TABLE public t_autovacuum_1 pg12
217; 1259 66585 TABLE public t_autovacuum_db1 pg12
213; 1259 58363 TABLE public t_big_autovacuum_1 pg12
204; 1259 32768 TABLE public t_count pg12
209; 1259 58324 TABLE public t_fillfactor_100 pg12
211; 1259 58330 TABLE public t_fillfactor_50 pg12
210; 1259 58327 TABLE public t_fillfactor_70 pg12
215; 1259 66579 TABLE public t_mvcc pg12
214; 1259 58366 TABLE public t_tx pg12
207; 1259 49643 TABLE public tbl pg12
3154; 0 16385 TABLE DATA public a pg12
3155; 0 16391 TABLE DATA public b pg12
3157; 0 40967 TABLE DATA public rel pg12
3168; 0 66582 TABLE DATA public t pg12
3158; 0 49634 TABLE DATA public t1 pg12
3160; 0 58321 TABLE DATA public t2 pg12
3164; 0 58360 TABLE DATA public t_autovacuum_1 pg12
3169; 0 66585 TABLE DATA public t_autovacuum_db1 pg12
3165; 0 58363 TABLE DATA public t_big_autovacuum_1 pg12
3156; 0 32768 TABLE DATA public t_count pg12
3161; 0 58324 TABLE DATA public t_fillfactor_100 pg12
3163; 0 58330 TABLE DATA public t_fillfactor_50 pg12
3162; 0 58327 TABLE DATA public t_fillfactor_70 pg12
3167; 0 66579 TABLE DATA public t_mvcc pg12
3166; 0 58366 TABLE DATA public t_tx pg12
3159; 0 49643 TABLE DATA public tbl pg12
3021; 2606 16398 CONSTRAINT public a a_pkey pg12
3023; 2606 16400 CONSTRAINT public b b_pkey pg12
3026; 2606 40971 CONSTRAINT public rel rel_pkey pg12
3024; 1259 40972 INDEX public rel_bid_idx pg12
3027; 2606 16401 FK CONSTRAINT public b b_id_fkey pg12
[pg12@localhost ~]$ 
[pg12@localhost ~]$ pg_restore -l /tmp/dump-c | grep t_mvcc > /tmp/part.list
[pg12@localhost ~]$ cat /tmp/part.list 
215; 1259 66579 TABLE public t_mvcc pg12
3167; 0 66579 TABLE DATA public t_mvcc pg12
[pg12@localhost ~]$ 
[pg12@localhost ~]$ pg_restore -L /tmp/part.list -f /tmp/partial.restore /tmp/dump-c 
[pg12@localhost ~]$ cat /tmp/partial.restore 
--
-- PostgreSQL database dump
--
-- Dumped from database version 12.1
-- Dumped by pg_dump version 12.1
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: t_mvcc; Type: TABLE; Schema: public; Owner: pg12
--
CREATE TABLE public.t_mvcc (
    id integer
);
ALTER TABLE public.t_mvcc OWNER TO pg12;
--
-- Data for Name: t_mvcc; Type: TABLE DATA; Schema: public; Owner: pg12
--
COPY public.t_mvcc (id) FROM stdin;
1
\.
--
-- PostgreSQL database dump complete
--
[pg12@localhost ~]$

下面体验下pd_restore的并行恢复

#串行模式
[pg12@localhost ~]$ dropdb testdb; time psql -qAtX -v ON_ERROR_STOP=1 -f /tmp/dump-p -d postgres
dropdb: error: database removal failed: ERROR:  database "testdb" does not exist
real    1m13.347s
user    0m1.716s
sys    0m2.333s
[pg12@localhost ~]$ 
#并行模式（8个）
[pg12@localhost ~]$ dropdb testdb ; time pg_restore -j 8 -C -d postgres /tmp/dump-c
real    0m58.394s
user    0m1.680s
sys    0m0.550s
[pg12@localhost ~]$

并行模式有20%的性能提升。
其实在dump的时候亦可指定并行，但需要与-F d选项配合使用。

参考资料
How to effectively dump PostgreSQL databases