PostgreSQL 并行逻辑备份与一致性讲解 - PostgreSQL 9.3 parallel pg_dump
背景
PostgreSQL pg_dump并行备份的功能已经提交到最新的代码中, 9.3中应该会包含此功能. 导出速度提升非常明显.
前段时间写过一篇并行导出的BLOG, 与pg_dump的并行导出一样都用到了9.2开始引入的export snapshot功能.
感兴趣的朋友可以看如下文章 :
http://blog.163.com/digoal@126/blog/static/163877040201326829943/
接下来将测试一下pg_dump的并行导出功能.
1. 下载
su - pgdev
pgdev@db-172-16-3-150-> wget --no-check-certificate https://github.com/postgres/postgres/archive/master.zip -O postgresql9.3.zip
2. 安装
pgdev@db-172-16-3-150-> unzip postgresql9.3.zip
pgdev@db-172-16-3-150-> less .bash_profile
export PS1="$USER@`/bin/hostname -s`-> "
export PGPORT=9300
export PGUSER=postgres
export PGDATA=/data06/pgdev/pg_root
export LANG=en_US.utf8
export PGHOME=/home/pgdev/pgsql9.3
export PGHOST=127.0.0.1
export PGDATABASE=digoal
export LD_LIBRARY_PATH=/opt/uuid-1.6.2/lib:$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib
export DATE=`date +"%Y%m%d%H%M"`
export PATH=$PGHOME/bin:$PATH:.
export MANPATH=$PGHOME/share/man:$MANPATH
alias rm='rm -i'
alias ll='ls -lh'
su - root
[root@db-172-16-3-150 ~]# . /home/pgdev/.bash_profile
root@db-172-16-3-150-> cd postgres-master/
root@db-172-16-3-150-> ./configure --prefix=/home/pgdev/pgsql9.3 --with-pgport=9300 --with-perl --with-python --with-tcl --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --enable-cassert --with-wal-blocksize=16 --enable-debug && gmake
root@db-172-16-3-150-> gmake install
root@db-172-16-3-150-> cd contrib/
root@db-172-16-3-150-> gmake install
root@db-172-16-3-150-> su - pgdev
pgdev@db-172-16-3-150-> initdb -D $PGDATA --locale=C -E UTF8 -U postgres
pgdev@db-172-16-3-150-> pg_ctl start
server starting
postgres=# create table t1(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t2(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t3(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t4(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t5(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t6(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t7(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# create table t8(id int, info text, crt_time timestamp);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,2000000),md5(random()::text),clock_timestamp();
postgres=# insert into t2 select generate_series(1,1500000),md5(random()::text),clock_timestamp();
postgres=# insert into t3 select generate_series(1,1000000),md5(random()::text),clock_timestamp();
postgres=# insert into t4 select generate_series(1,3000000),md5(random()::text),clock_timestamp();
postgres=# insert into t5 select generate_series(1,4000000),md5(random()::text),clock_timestamp();
postgres=# insert into t6 select generate_series(1,1000000),md5(random()::text),clock_timestamp();
postgres=# insert into t7 select generate_series(1,1000000),md5(random()::text),clock_timestamp();
postgres=# insert into t8 select generate_series(1,5000000),md5(random()::text),clock_timestamp();
3. 并行导出测试 :
并行度为8, 导出耗时36秒 :
pgdev@db-172-16-3-150-> date;pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h $PGDATA -p 9300 -U postgres postgres;date
Mon Mar 25 10:51:18 CST 2013
Mon Mar 25 10:51:54 CST 2013
pg_dump fork了8个进程来处理dump.
pgdev@db-172-16-3-150-> ps -ewf|grep pg_dump
pgdev 17136 16544 1 10:56 pts/1 00:00:00 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17138 17136 68 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17140 17136 66 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17141 17136 67 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17142 17136 66 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17144 17136 68 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17145 17136 60 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17146 17136 67 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
pgdev 17147 17136 56 10:56 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -j 8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
4. 普通导出, 导出耗时129秒 :
pgdev@db-172-16-3-150-> date;pg_dump -f ./postgres.dmp -F d -E UTF8 -h $PGDATA -p 9300 -U postgres postgres;date
Mon Mar 25 10:52:32 CST 2013
Mon Mar 25 10:54:41 CST 2013
pgdev@db-172-16-3-150-> ps -ewf|grep pg_dump
pgdev 17173 16544 89 10:58 pts/1 00:00:01 pg_dump -f ./postgres.dmp -F d -E UTF8 -h /data06/pgdev/pg_root -p 9300 -U postgres postgres
5. 并行导出对9.1以及更老的版本也适用, 但是请注意以下.
注意
1. 并行导出利用了PostgreSQL引入的事务状态导出特性. 对于不支持并行导出的数据库版本, 9.2以前的版本. 如果使用并行导出, 可能导出不一致的数据, 那么为了在9.1以及以前的版本并行导出一致的数据, 办法是导出时禁止被导出表的一切DML操作, 并且加上pg_dump 的 –no-synchronized-snapshots 选项.
导出事务状态的文章可参考如下 :
http://blog.163.com/digoal@126/blog/static/1638770402012416105232835/
2. 目前pg_dump的并行导出需要注意, pg_dump子进程获取锁不是一步到位的, 而是随着备份进行去单个获取的(并且使用了nowait), 所以如果在导出过程中, 子进程获取锁可能会遇到用户操作的冲突. 获取失败, 导致整个备份失败. 如下.
SESSION A :
pgdev@db-172-16-3-150-> date;pg_dump -f ./postgres.dmp -F d -E UTF8 -j 2 -h $PGDATA -p 9300 -U postgres postgres;date
Mon Mar 25 11:28:46 CST 2013
SESSION B :
postgres=# truncate table t7;
waiting...
SESSION C :
postgres=# select pid,query from pg_stat_activity;
pid | query
-------+---------------------------------------------------------------------------------------------------------------------
---------------------
17326 | truncate table t7;
18585 | SELECT attname, attacl FROM pg_catalog.pg_attribute WHERE attrelid = '16449' AND NOT attisdropped AND attacl IS NOT
NULL ORDER BY attnum
18588 | COPY public.t8 (id, info, crt_time) TO stdout;
18589 | COPY public.t5 (id, info, crt_time) TO stdout;
17583 | select pid,query from pg_stat_activity;
(5 rows)
并行备份的主pg_dump进程需要获取所有表包括系统表的AccessShareLock(也就是说，备份数据前就已经加锁), 然后fork的pg_dump进程则依次根据备份的需要获取相应对象的AccessShareLock.
从以下查询可以得知两个pg_dump的子进程获得了t8和t5的AccessShareLock.
postgres=# select pid,database,relation,locktype,mode,granted,relname from pg_locks t1,pg_class t2 where t1.relation=t2.oid and pid in (18585,18588,18589,17326) order by pid,relation;
pid | database | relation | locktype | mode | granted | relname
-------+----------+----------+----------+-----------------+---------+-----------------------------------------
17326 | 12816 | 16420 | relation | AccessExclusiveLock | f | t7
18585 | 12816 | 112 | relation | AccessShareLock | t | pg_foreign_data_wrapper_oid_index
18585 | 12816 | 113 | relation | AccessShareLock | t | pg_foreign_server_oid_index
18585 | 12816 | 548 | relation | AccessShareLock | t | pg_foreign_data_wrapper_name_index
18585 | 12816 | 549 | relation | AccessShareLock | t | pg_foreign_server_name_index
18585 | 12816 | 826 | relation | AccessShareLock | t | pg_default_acl
18585 | 12816 | 827 | relation | AccessShareLock | t | pg_default_acl_role_nsp_obj_index
18585 | 12816 | 828 | relation | AccessShareLock | t | pg_default_acl_oid_index
18585 | 0 | 1213 | relation | AccessShareLock | t | pg_tablespace
18585 | 12816 | 1247 | relation | AccessShareLock | t | pg_type
18585 | 12816 | 1249 | relation | AccessShareLock | t | pg_attribute
18585 | 12816 | 1255 | relation | AccessShareLock | t | pg_proc
18585 | 12816 | 1259 | relation | AccessShareLock | t | pg_class
18585 | 0 | 1260 | relation | AccessShareLock | t | pg_authid
18585 | 0 | 1262 | relation | AccessShareLock | t | pg_database
18585 | 12816 | 1417 | relation | AccessShareLock | t | pg_foreign_server
18585 | 12816 | 2187 | relation | AccessShareLock | t | pg_inherits_parent_index
18585 | 12816 | 2328 | relation | AccessShareLock | t | pg_foreign_data_wrapper
18585 | 0 | 2396 | relation | AccessShareLock | t | pg_shdescription
18585 | 0 | 2397 | relation | AccessShareLock | t | pg_shdescription_o_c_index
18585 | 12816 | 2605 | relation | AccessShareLock | t | pg_cast
18585 | 12816 | 2607 | relation | AccessShareLock | t | pg_conversion
18585 | 12816 | 2608 | relation | AccessShareLock | t | pg_depend
18585 | 12816 | 2609 | relation | AccessShareLock | t | pg_description
18585 | 12816 | 2611 | relation | AccessShareLock | t | pg_inherits
18585 | 12816 | 2612 | relation | AccessShareLock | t | pg_language
18585 | 12816 | 2615 | relation | AccessShareLock | t | pg_namespace
18585 | 12816 | 2616 | relation | AccessShareLock | t | pg_opclass
18585 | 12816 | 2617 | relation | AccessShareLock | t | pg_operator
18585 | 12816 | 2618 | relation | AccessShareLock | t | pg_rewrite
18585 | 12816 | 2658 | relation | AccessShareLock | t | pg_attribute_relid_attnam_index
18585 | 12816 | 2659 | relation | AccessShareLock | t | pg_attribute_relid_attnum_index
18585 | 12816 | 2660 | relation | AccessShareLock | t | pg_cast_oid_index
18585 | 12816 | 2661 | relation | AccessShareLock | t | pg_cast_source_target_index
18585 | 12816 | 2662 | relation | AccessShareLock | t | pg_class_oid_index
18585 | 12816 | 2663 | relation | AccessShareLock | t | pg_class_relname_nsp_index
18585 | 12816 | 2668 | relation | AccessShareLock | t | pg_conversion_default_index
18585 | 12816 | 2669 | relation | AccessShareLock | t | pg_conversion_name_nsp_index
18585 | 12816 | 2670 | relation | AccessShareLock | t | pg_conversion_oid_index
18585 | 0 | 2671 | relation | AccessShareLock | t | pg_database_datname_index
18585 | 0 | 2672 | relation | AccessShareLock | t | pg_database_oid_index
18585 | 12816 | 2673 | relation | AccessShareLock | t | pg_depend_depender_index
18585 | 12816 | 2674 | relation | AccessShareLock | t | pg_depend_reference_index
18585 | 12816 | 2675 | relation | AccessShareLock | t | pg_description_o_c_o_index
18585 | 0 | 2676 | relation | AccessShareLock | t | pg_authid_rolname_index
18585 | 0 | 2677 | relation | AccessShareLock | t | pg_authid_oid_index
18585 | 12816 | 2680 | relation | AccessShareLock | t | pg_inherits_relid_seqno_index
18585 | 12816 | 2681 | relation | AccessShareLock | t | pg_language_name_index
18585 | 12816 | 2682 | relation | AccessShareLock | t | pg_language_oid_index
18585 | 12816 | 2684 | relation | AccessShareLock | t | pg_namespace_nspname_index
18585 | 12816 | 2685 | relation | AccessShareLock | t | pg_namespace_oid_index
18585 | 12816 | 2686 | relation | AccessShareLock | t | pg_opclass_am_name_nsp_index
18585 | 12816 | 2687 | relation | AccessShareLock | t | pg_opclass_oid_index
18585 | 12816 | 2688 | relation | AccessShareLock | t | pg_operator_oid_index
18585 | 12816 | 2689 | relation | AccessShareLock | t | pg_operator_oprname_l_r_n_index
18585 | 12816 | 2690 | relation | AccessShareLock | t | pg_proc_oid_index
18585 | 12816 | 2691 | relation | AccessShareLock | t | pg_proc_proname_args_nsp_index
18585 | 12816 | 2692 | relation | AccessShareLock | t | pg_rewrite_oid_index
18585 | 12816 | 2693 | relation | AccessShareLock | t | pg_rewrite_rel_rulename_index
18585 | 0 | 2697 | relation | AccessShareLock | t | pg_tablespace_oid_index
18585 | 0 | 2698 | relation | AccessShareLock | t | pg_tablespace_spcname_index
18585 | 12816 | 2703 | relation | AccessShareLock | t | pg_type_oid_index
18585 | 12816 | 2704 | relation | AccessShareLock | t | pg_type_typname_nsp_index
18585 | 12816 | 2753 | relation | AccessShareLock | t | pg_opfamily
18585 | 12816 | 2754 | relation | AccessShareLock | t | pg_opfamily_am_name_nsp_index
18585 | 12816 | 2755 | relation | AccessShareLock | t | pg_opfamily_oid_index
18585 | 0 | 2964 | relation | AccessShareLock | t | pg_db_role_setting
18585 | 0 | 2965 | relation | AccessShareLock | t | pg_db_role_setting_databaseid_rol_index
18585 | 12816 | 2995 | relation | AccessShareLock | t | pg_largeobject_metadata
18585 | 12816 | 2996 | relation | AccessShareLock | t | pg_largeobject_metadata_oid_index
18585 | 12816 | 3079 | relation | AccessShareLock | t | pg_extension
18585 | 12816 | 3080 | relation | AccessShareLock | t | pg_extension_oid_index
18585 | 12816 | 3081 | relation | AccessShareLock | t | pg_extension_name_index
18585 | 12816 | 3085 | relation | AccessShareLock | t | pg_collation_oid_index
18585 | 12816 | 3164 | relation | AccessShareLock | t | pg_collation_name_enc_nsp_index
18585 | 12816 | 3456 | relation | AccessShareLock | t | pg_collation
18585 | 12816 | 3466 | relation | AccessShareLock | t | pg_event_trigger
18585 | 12816 | 3467 | relation | AccessShareLock | t | pg_event_trigger_evtname_index
18585 | 12816 | 3468 | relation | AccessShareLock | t | pg_event_trigger_oid_index
18585 | 0 | 3592 | relation | AccessShareLock | t | pg_shseclabel
18585 | 0 | 3593 | relation | AccessShareLock | t | pg_shseclabel_object_index
18585 | 12816 | 3596 | relation | AccessShareLock | t | pg_seclabel
18585 | 12816 | 3597 | relation | AccessShareLock | t | pg_seclabel_object_index
18585 | 12816 | 3600 | relation | AccessShareLock | t | pg_ts_dict
18585 | 12816 | 3601 | relation | AccessShareLock | t | pg_ts_parser
18585 | 12816 | 3602 | relation | AccessShareLock | t | pg_ts_config
18585 | 12816 | 3604 | relation | AccessShareLock | t | pg_ts_dict_dictname_index
18585 | 12816 | 3605 | relation | AccessShareLock | t | pg_ts_dict_oid_index
18585 | 12816 | 3606 | relation | AccessShareLock | t | pg_ts_parser_prsname_index
18585 | 12816 | 3607 | relation | AccessShareLock | t | pg_ts_parser_oid_index
18585 | 12816 | 3608 | relation | AccessShareLock | t | pg_ts_config_cfgname_index
18585 | 12816 | 3712 | relation | AccessShareLock | t | pg_ts_config_oid_index
18585 | 12816 | 3764 | relation | AccessShareLock | t | pg_ts_template
18585 | 12816 | 3766 | relation | AccessShareLock | t | pg_ts_template_tmplname_index
18585 | 12816 | 3767 | relation | AccessShareLock | t | pg_ts_template_oid_index
18585 | 12816 | 11054 | relation | AccessShareLock | t | pg_roles
18585 | 12816 | 16384 | relation | AccessShareLock | t | t1
18585 | 12816 | 16390 | relation | AccessShareLock | t | t2
18585 | 12816 | 16396 | relation | AccessShareLock | t | t3
18585 | 12816 | 16402 | relation | AccessShareLock | t | t4
18585 | 12816 | 16408 | relation | AccessShareLock | t | t5
18585 | 12816 | 16414 | relation | AccessShareLock | t | t6
18585 | 12816 | 16420 | relation | AccessShareLock | t | t7
18585 | 12816 | 16426 | relation | AccessShareLock | t | t8
18585 | 12816 | 16449 | relation | AccessShareLock | t | t9
18588 | 12816 | 1259 | relation | AccessShareLock | t | pg_class
18588 | 12816 | 2615 | relation | AccessShareLock | t | pg_namespace
18588 | 12816 | 2662 | relation | AccessShareLock | t | pg_class_oid_index
18588 | 12816 | 2663 | relation | AccessShareLock | t | pg_class_relname_nsp_index
18588 | 12816 | 2684 | relation | AccessShareLock | t | pg_namespace_nspname_index
18588 | 12816 | 2685 | relation | AccessShareLock | t | pg_namespace_oid_index
18588 | 12816 | 16426 | relation | AccessShareLock | t | t8
18589 | 12816 | 1259 | relation | AccessShareLock | t | pg_class
18589 | 12816 | 2615 | relation | AccessShareLock | t | pg_namespace
18589 | 12816 | 2662 | relation | AccessShareLock | t | pg_class_oid_index
18589 | 12816 | 2663 | relation | AccessShareLock | t | pg_class_relname_nsp_index
18589 | 12816 | 2684 | relation | AccessShareLock | t | pg_namespace_nspname_index
18589 | 12816 | 2685 | relation | AccessShareLock | t | pg_namespace_oid_index
18589 | 12816 | 16408 | relation | AccessShareLock | t | t5
(118 rows)
随着备份的进行, 继续查询…. pg_dump的子进程需要继续获取t1,t3,t2,t4的AccessShareLock锁.
18588 | 12816 | 1259 | relation | AccessShareLock | t | pg_class
18588 | 12816 | 2615 | relation | AccessShareLock | t | pg_namespace
18588 | 12816 | 2662 | relation | AccessShareLock | t | pg_class_oid_index
18588 | 12816 | 2663 | relation | AccessShareLock | t | pg_class_relname_nsp_index
18588 | 12816 | 2684 | relation | AccessShareLock | t | pg_namespace_nspname_index
18588 | 12816 | 2685 | relation | AccessShareLock | t | pg_namespace_oid_index
18588 | 12816 | 16384 | relation | AccessShareLock | t | t1
18588 | 12816 | 16396 | relation | AccessShareLock | t | t3
18588 | 12816 | 16426 | relation | AccessShareLock | t | t8
18589 | 12816 | 1259 | relation | AccessShareLock | t | pg_class
18589 | 12816 | 2615 | relation | AccessShareLock | t | pg_namespace
18589 | 12816 | 2662 | relation | AccessShareLock | t | pg_class_oid_index
18589 | 12816 | 2663 | relation | AccessShareLock | t | pg_class_relname_nsp_index
18589 | 12816 | 2684 | relation | AccessShareLock | t | pg_namespace_nspname_index
18589 | 12816 | 2685 | relation | AccessShareLock | t | pg_namespace_oid_index
18589 | 12816 | 16390 | relation | AccessShareLock | t | t2
18589 | 12816 | 16402 | relation | AccessShareLock | t | t4
18589 | 12816 | 16408 | relation | AccessShareLock | t | t5
SESSION A :
当pg_dump的子进程获取t7的AccessShareLock锁时会失败. 因为SESSION B在等待与之冲突的Exclusive Lock. 而pg_dump使用的是nowait进行锁请求, 当pg_dump获取锁失败后马上退出, pg_dump主进程虽然已经获取了t7的AccessShareLock, 但是为了保证整个备份的一致性, 也会退出.
pg_dump: [parallel archiver] could not obtain lock on relation "public.t7". This usually means that someone requested an ACCESS EXCLUSIVE lock on the table after the pg_dump parent process has gotten the initial ACCESS SHARE lock on the table.
Mon Mar 25 11:29:43 CST 2013
SESSION B :
TRUNCATE TABLE
complete
参考
1. https://github.com/postgres/postgres/commit/9e257a181cc1dc5e19eb5d770ce09cc98f470f5f
2. http://www.postgresql.org/message-id/CACw0+11NeCcdv2dp4OsEYRoG8rEhKuVui7A8HwbOkBWCSygPXQ@mail.gmail.com
3. http://www.postgresql.org/docs/devel/static/app-pgdump.html
4. http://blog.163.com/digoal@126/blog/static/163877040201222112937900/
5. http://blog.163.com/digoal@126/blog/static/163877040201326829943/
6. http://blog.163.com/digoal@126/blog/static/1638770402012416105232835/
7. pg_dump –help
pg_dump dumps a database as a text file or to other formats.
Usage:
pg_dump [OPTION]... [DBNAME]
General options:
-f, --file=FILENAME output file or directory name
-F, --format=c|d|t|p output file format (custom, directory, tar,
plain text (default))
-j, --jobs=NUM use this many parallel jobs to dump
-v, --verbose verbose mode
-V, --version output version information, then exit
-Z, --compress=0-9 compression level for compressed formats
--lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock
-?, --help show this help, then exit
Options controlling the output content:
-a, --data-only dump only the data, not the schema
-b, --blobs include large objects in dump
-c, --clean clean (drop) database objects before recreating
-C, --create include commands to create database in dump
-E, --encoding=ENCODING dump the data in encoding ENCODING
-n, --schema=SCHEMA dump the named schema(s) only
-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)
-o, --oids include OIDs in dump
-O, --no-owner skip restoration of object ownership in
plain-text format
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME superuser user name to use in plain-text format
-t, --table=TABLE dump the named table(s) only
-T, --exclude-table=TABLE do NOT dump the named table(s)
-x, --no-privileges do not dump privileges (grant/revoke)
--binary-upgrade for use by upgrade utilities only
--column-inserts dump data as INSERT commands with column names
--disable-dollar-quoting disable dollar quoting, use SQL standard quoting
--disable-triggers disable triggers during data-only restore
--exclude-table-data=TABLE do NOT dump data for the named table(s)
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-synchronized-snapshots parallel processes should not use synchronized snapshots
--no-tablespaces do not dump tablespace assignments
--no-unlogged-table-data do not dump unlogged table data
--quote-all-identifiers quote all identifiers, even if not key words
--section=SECTION dump named section (pre-data, data, or post-data)
--serializable-deferrable wait until the dump can run without anomalies
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-d, --dbname=DBNAME database to dump
-h, --host=HOSTNAME database server host or socket directory
-p, --port=PORT database server port number
-U, --username=NAME connect as specified database user
-w, --no-password never prompt for password
-W, --password force password prompt (should happen automatically)
--role=ROLENAME do SET ROLE before dump
If no database name is supplied, then the PGDATABASE environment
variable value is used.
Report bugs to <pgsql-bugs@postgresql.org>.
8.
-j njobs
--jobs=njobs
Run the dump in parallel by dumping njobs tables simultaneously. This option reduces the time of the dump but it also increases the load on the database server. You can only use this option with the directory output format because this is the only output format where multiple processes can write their data at the same time.
pg_dump will open njobs + 1 connections to the database, so make sure your max_connections setting is high enough to accommodate all connections.
Requesting exclusive locks on database objects while running a parallel dump could cause the dump to fail. The reason is that the pg_dump master process requests shared locks on the objects that the worker processes are going to dump later in order to make sure that nobody deletes them and makes them go away while the dump is running. If another client then requests an exclusive lock on a table, that lock will not be granted but will be queued waiting for the shared lock of the master process to be released.. Consequently any other access to the table will not be granted either and will queue after the exclusive lock request. This includes the worker process trying to dump the table. Without any precautions this would be a classic deadlock situation. To detect this conflict, the pg_dump worker process requests another shared lock using the NOWAIT option. If the worker process is not granted this shared lock, somebody else must have requested an exclusive lock in the meantime and there is no way to continue with the dump, so pg_dump has no choice but to abort the dump.
For a consistent backup, the database server needs to support synchronized snapshots, a feature that was introduced in PostgreSQL 9.2. With this feature, database clients can ensure they see the same dataset even though they use different connections. pg_dump -j uses multiple database connections; it connects to the database once with the master process and once again for each worker job. Without the sychronized snapshot feature, the different worker jobs wouldn't be guaranteed to see the same data in each connection, which could lead to an inconsistent backup.
If you want to run a parallel dump of a pre-9.2 server, you need to make sure that the database content doesn't change from between the time the master connects to the database until the last worker job has connected to the database. The easiest way to do this is to halt any data modifying processes (DDL and DML) accessing the database before starting the backup. You also need to specify the --no-synchronized-snapshots parameter when running pg_dump -j against a pre-9.2 PostgreSQL server.