本节介绍了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 ``` 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](https://www.depesz.com/2019/12/10/how-to-effectively-dump-postgresql-databases/)