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.filepg_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 2pg_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,3pg_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 1pg_dumpall: executing SELECT datname FROM pg_database d WHERE datallowconn ORDER BY (datname <> 'template1'), datnamepg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_dump: flagging inherited columns in subtablespg_dump: reading indexespg_dump: flagging indexes in partitioned tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading triggerspg_dump: reading rewrite rulespg_dump: reading policiespg_dump: reading publicationspg_dump: reading publication membershippg_dump: reading subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: implied data-only restorepg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_dump: finding the columns and types of table "public.t_autovacuum_db1"pg_dump: flagging inherited columns in subtablespg_dump: reading indexespg_dump: flagging indexes in partitioned tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading triggerspg_dump: reading rewrite rulespg_dump: reading policiespg_dump: reading row security enabled for table "public.t_autovacuum_db1"pg_dump: reading policies for table "public.t_autovacuum_db1"pg_dump: reading publicationspg_dump: reading publication membershippg_dump: reading publication membership for table "public.t_autovacuum_db1"pg_dump: reading subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: saving database definitionpg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_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 subtablespg_dump: reading indexespg_dump: flagging indexes in partitioned tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading triggerspg_dump: reading rewrite rulespg_dump: reading policiespg_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 publicationspg_dump: reading publication membershippg_dump: reading publication membership for table "public.t_autovacuum_db3"pg_dump: reading publication membership for table "public.t_autovacuum_db2"pg_dump: reading subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: saving database definitionpg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_dump: finding the columns and types of table "public.t_autovacuum_db3"pg_dump: flagging inherited columns in subtablespg_dump: reading indexespg_dump: flagging indexes in partitioned tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading triggerspg_dump: reading rewrite rulespg_dump: reading policiespg_dump: reading row security enabled for table "public.t_autovacuum_db3"pg_dump: reading policies for table "public.t_autovacuum_db3"pg_dump: reading publicationspg_dump: reading publication membershippg_dump: reading publication membership for table "public.t_autovacuum_db3"pg_dump: reading subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: saving database definitionpg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_dump: flagging inherited columns in subtablespg_dump: reading indexespg_dump: flagging indexes in partitioned tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading triggerspg_dump: reading rewrite rulespg_dump: reading policiespg_dump: reading publicationspg_dump: reading publication membershippg_dump: reading subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: implied data-only restorepg_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 16383pg_dump: reading extensionspg_dump: identifying extension memberspg_dump: reading schemaspg_dump: reading user-defined tablespg_dump: reading user-defined functionspg_dump: reading user-defined typespg_dump: reading procedural languagespg_dump: reading user-defined aggregate functionspg_dump: reading user-defined operatorspg_dump: reading user-defined access methodspg_dump: reading user-defined operator classespg_dump: reading user-defined operator familiespg_dump: reading user-defined text search parserspg_dump: reading user-defined text search templatespg_dump: reading user-defined text search dictionariespg_dump: reading user-defined text search configurationspg_dump: reading user-defined foreign-data wrapperspg_dump: reading user-defined foreign serverspg_dump: reading default privilegespg_dump: reading user-defined collationspg_dump: reading user-defined conversionspg_dump: reading type castspg_dump: reading transformspg_dump: reading table inheritance informationpg_dump: reading event triggerspg_dump: finding extension tablespg_dump: finding inheritance relationshipspg_dump: reading column info for interesting tablespg_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 subtablespg_dump: reading indexespg_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 tablespg_dump: reading extended statisticspg_dump: reading constraintspg_dump: reading foreign key constraints for table "public.a"pg_dump: reading foreign key constraints for table "public.b"pg_dump: reading triggerspg_dump: reading triggers for table "public.a"pg_dump: reading triggers for table "public.b"pg_dump: reading rewrite rulespg_dump: reading policiespg_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 publicationspg_dump: reading publication membershippg_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 subscriptionspg_dump: reading large objectspg_dump: reading dependency datapg_dump: saving encoding = UTF8pg_dump: saving standard_conforming_strings = onpg_dump: saving search_path = pg_dump: saving database definitionpg_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> doneFormat: preal    0m41.519suser    0m4.639ssys    0m2.161sFormat: creal    0m46.518suser    0m16.371ssys    0m0.819sFormat: treal    0m41.471suser    0m1.882ssys    0m5.433sFormat: dreal    0m44.775suser    0m15.106ssys    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-tdump-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 pg12203; 1259 16391 TABLE public b pg12205; 1259 40967 TABLE public rel pg12216; 1259 66582 TABLE public t pg12206; 1259 49634 TABLE public t1 pg12208; 1259 58321 TABLE public t2 pg12212; 1259 58360 TABLE public t_autovacuum_1 pg12217; 1259 66585 TABLE public t_autovacuum_db1 pg12213; 1259 58363 TABLE public t_big_autovacuum_1 pg12204; 1259 32768 TABLE public t_count pg12209; 1259 58324 TABLE public t_fillfactor_100 pg12211; 1259 58330 TABLE public t_fillfactor_50 pg12210; 1259 58327 TABLE public t_fillfactor_70 pg12215; 1259 66579 TABLE public t_mvcc pg12214; 1259 58366 TABLE public t_tx pg12207; 1259 49643 TABLE public tbl pg123154; 0 16385 TABLE DATA public a pg123155; 0 16391 TABLE DATA public b pg123157; 0 40967 TABLE DATA public rel pg123168; 0 66582 TABLE DATA public t pg123158; 0 49634 TABLE DATA public t1 pg123160; 0 58321 TABLE DATA public t2 pg123164; 0 58360 TABLE DATA public t_autovacuum_1 pg123169; 0 66585 TABLE DATA public t_autovacuum_db1 pg123165; 0 58363 TABLE DATA public t_big_autovacuum_1 pg123156; 0 32768 TABLE DATA public t_count pg123161; 0 58324 TABLE DATA public t_fillfactor_100 pg123163; 0 58330 TABLE DATA public t_fillfactor_50 pg123162; 0 58327 TABLE DATA public t_fillfactor_70 pg123167; 0 66579 TABLE DATA public t_mvcc pg123166; 0 58366 TABLE DATA public t_tx pg123159; 0 49643 TABLE DATA public tbl pg123021; 2606 16398 CONSTRAINT public a a_pkey pg123023; 2606 16400 CONSTRAINT public b b_pkey pg123026; 2606 40971 CONSTRAINT public rel rel_pkey pg123024; 1259 40972 INDEX public rel_bid_idx pg123027; 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 pg123167; 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.1SET 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 postgresdropdb: error: database removal failed: ERROR:  database "testdb" does not existreal    1m13.347suser    0m1.716ssys    0m2.333s[pg12@localhost ~]$ #并行模式（8个）[pg12@localhost ~]$ dropdb testdb ; time pg_restore -j 8 -C -d postgres /tmp/dump-creal    0m58.394suser    0m1.680ssys    0m0.550s[pg12@localhost ~]$

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

参考资料
How to effectively dump PostgreSQL databases