本节介绍了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
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
