当前版本： PG 9.5.3
1. pg_dump 官方帮助
[postgres@dang-db bin]$./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
--enable-row-security enable row security (dump only content user has
access to)
--exclude-table-data=TABLE do NOT dump data for the named table(s)
--if-exists use IF EXISTS when dropping objects
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--no-synchronized-snapshots do not use synchronized snapshots in parallel jobs
--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
--snapshot=SNAPSHOT use given snapshot for the dump
--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>.
2. 具体的例子
2.1 导出整个db到SQL文件中
$PGHOME/bin/pg_dump testdb > testdb.sql
通过 --verbose参数可以查看导出的过程：
$PGHOME/bin/pg_dump testdb --verbose > testdb.sql
[postgres@dang-db dump]$$PGHOME/bin/pg_dump testdb --verbose > testdb.sql
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 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 "user01.test"
pg_dump: finding the columns and types of table "user02.test01"
pg_dump: finding the columns and types of table "public.test"
pg_dump: finding the columns and types of table "user01.test02"
pg_dump: flagging inherited columns in subtables
pg_dump: reading indexes
pg_dump: reading indexes for table "user01.test"
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 "user01.test"
pg_dump: reading policies for table "user01.test"
pg_dump: reading row security enabled for table "user01.seq_test"
pg_dump: reading policies for table "user01.seq_test"
pg_dump: reading row security enabled for table "user02.test01"
pg_dump: reading policies for table "user02.test01"
pg_dump: reading row security enabled for table "public.test"
pg_dump: reading policies for table "public.test"
pg_dump: reading row security enabled for table "user01.test02"
pg_dump: reading policies for table "user01.test02"
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 database definition
pg_dump: creating SCHEMA "public"
pg_dump: creating COMMENT "SCHEMA public"
pg_dump: creating SCHEMA "user01"
pg_dump: creating SCHEMA "user02"
pg_dump: creating EXTENSION "plpgsql"
pg_dump: creating COMMENT "EXTENSION plpgsql"
pg_dump: creating FUNCTION "user01.add(integer, numeric)"
pg_dump: creating TABLE "public.test"
pg_dump: creating SEQUENCE "user01.seq_test"
pg_dump: creating TABLE "user01.test"
pg_dump: creating COMMENT "user01.TABLE test"
pg_dump: creating TABLE "user01.test02"
pg_dump: creating TABLE "user02.test01"
pg_dump: processing data for table "public.test"
pg_dump: dumping contents of table "public.test"
pg_dump: executing SEQUENCE SET seq_test
pg_dump: processing data for table "user01.test"
pg_dump: dumping contents of table "user01.test"
pg_dump: processing data for table "user01.test02"
pg_dump: dumping contents of table "user01.test02"
pg_dump: processing data for table "user02.test01"
pg_dump: dumping contents of table "user02.test01"
pg_dump: creating CONSTRAINT "user01.test_pkey"
pg_dump: setting owner and privileges for DATABASE "testdb"
pg_dump: setting owner and privileges for SCHEMA "public"
pg_dump: setting owner and privileges for COMMENT "SCHEMA public"
pg_dump: setting owner and privileges for ACL "public"
pg_dump: setting owner and privileges for SCHEMA "user01"
pg_dump: setting owner and privileges for SCHEMA "user02"
pg_dump: setting owner and privileges for EXTENSION "plpgsql"
pg_dump: setting owner and privileges for COMMENT "EXTENSION plpgsql"
pg_dump: setting owner and privileges for FUNCTION "user01.add(integer, numeric)"
pg_dump: setting owner and privileges for TABLE "public.test"
pg_dump: setting owner and privileges for SEQUENCE "user01.seq_test"
pg_dump: setting owner and privileges for TABLE "user01.test"
pg_dump: setting owner and privileges for COMMENT "user01.TABLE test"
pg_dump: setting owner and privileges for TABLE "user01.test02"
pg_dump: setting owner and privileges for TABLE "user02.test01"
pg_dump: setting owner and privileges for TABLE DATA "public.test"
pg_dump: setting owner and privileges for SEQUENCE SET "user01.seq_test"
pg_dump: setting owner and privileges for TABLE DATA "user01.test"
pg_dump: setting owner and privileges for TABLE DATA "user01.test02"
pg_dump: setting owner and privileges for TABLE DATA "user02.test01"
pg_dump: setting owner and privileges for CONSTRAINT "user01.test_pkey"
2.2 导出某个db为PG识别的dump格式
$PGHOME/bin/pg_dump -Fc testdb --verbose > testdb.dump
[postgres@dang-db dump]$$PGHOME/bin/pg_dump -Fc testdb --verbose > testdb.dump
pg_dump: reading extensions
。。。
pg_dump: dumping contents of table "user01.test"
参数解释：
-F 决定导出的文件是哪种格式，默认为文本格式，可选项为：
c pg识别的自定义格式
t 导出的对象和数据放在一个tar包里，pg_restore 可以识别
d 导出数据到一个指定的文件夹中,pg_restore 可以识别
p 导出数据到文本中，也是默认的是格式
2.3 导出某个db到文件夹中
$PGHOME/bin/pg_dump -Fd testdb -f /postgres/pgsql/dump/pgdump
注意事项：
导出的文件夹必须为空，否则报错：
[postgres@dang-db pgdump]$$PGHOME/bin/pg_dump -Fd testdb -f /postgres/pgsql/dump/pgdump
pg_dump: [directory archiver] could not create directory "/postgres/pgsql/dump/pgdump": File exists
可以清空或者直接删除该文件夹。
[postgres@dang-db dump]$ll | grep pgdump
[postgres@dang-db dump]$$PGHOME/bin/pg_dump -Fd testdb -f /postgres/pgsql/dump/pgdump
[postgres@dang-db dump]$ll | grep pgdump
drwx------ 2 postgres postgres 4096 Aug 11 17:58 pgdump
[postgres@dang-db dump]$ll pgdump/
total 2300
-rw-rw-r-- 1 postgres postgres 2350816 Aug 11 17:58 2964.dat.gz
-rw-rw-r-- 1 postgres postgres 2870 Aug 11 17:58 toc.dat
[postgres@dang-db dump]$
[postgres@dang-db dump]$
[postgres@dang-db dump]$rm -rf pgdump/*
[postgres@dang-db dump]$ll pgdump/
total 0
[postgres@dang-db dump]$$PGHOME/bin/pg_dump -Fd testdb -f /postgres/pgsql/dump/pgdump
[postgres@dang-db dump]$ll pgdump/
total 2300
-rw-rw-r-- 1 postgres postgres 2350816 Aug 11 17:59 2964.dat.gz
-rw-rw-r-- 1 postgres postgres 2870 Aug 11 17:59 toc.dat
在使用-Fd的时候可以使用参数 -j 并行导出，加快导出的速度，但同时也会增加db的负载，需要结合当时的负载来决定并行度。另外并行会增加db的连接数，需要保证连接够用。
$PGHOME/bin/pg_dump -Fd testdb -f /postgres/pgsql/dump/pgdump -j 5
只有在-Fd模式才支持并行:
[postgres@dang-db dump]$$PGHOME/bin/pg_dump -Fc testdb -f testdb.dump -j 5
pg_dump: parallel backup only supported by the directory format
2.4 导出单个或多个schema中的数据
$PGHOME/bin/pg_dump -n user01 testdb -f test_dump.sql --verbose
可以通过通配符的方式导出一类schema的对象，假设当前testdb有两个schema：user01,user02，可以用如下方式同时导出两个schema的数据：
$PGHOME/bin/pg_dump -n 'user*' testdb -f test_dump.sql --verbose
利用 -N 参数可以排除某些schema的导出，假设当前testdb有两个schema：user01,user02，可以用如下方式排除user01的数据：
$PGHOME/bin/pg_dump -N user01 testdb -f test_dump.sql --verbose
同样，可以利用通配符排除某些schema,假设当前testdb有两个schema：user01,user02，可以用如下方式同时排除两个userXX的数据：
$PGHOME/bin/pg_dump -N 'user*' testdb -f test_dump.sql --verbose
-n -N 可以组合使用排除某些schema，假设当前testdb有10个schema：user01..user10，可以用如下方式同时排除user02的数据：
$PGHOME/bin/pg_dump -n 'user*' -N user02 testdb -f test_dump.sql --verbose
2.5 导出单个或多个对象的数据
利用-t 参数可以单独导出某张表的数据,在不指定 -n schema的时候，默认导出public下的数据
$PGHOME/bin/pg_dump -t test testdb -f test_dump.sql --verbose
需要导出某个schema下的某张表，可以用如下方式：
$PGHOME/bin/pg_dump -t user01.test testdb -f test_dump.sql --verbose
同样，可以利用通配符导出某类表：
$PGHOME/bin/pg_dump -t 'user01.test*' testdb -f test_dump.sql --verbose
导出多个schema下的同类表：
$PGHOME/bin/pg_dump -t 'user*.test*' testdb -f test_dump.sql --verbose
OR
$PGHOME/bin/pg_dump -t 'user01.test*' -t 'user02.test*' testdb -f test_dump.sql --verbose
导出除了单个或多个表之外的所有表：
$PGHOME/bin/pg_dump -T 'user01.test*' testdb -f test_dump.sql --verbose
-t -T 可以组合使用排除部分表:
$PGHOME/bin/pg_dump -t 'user*.test*' -T 'user01.test02' testdb -f test_dump.sql --verbose
结合参数 --exclude-table-data的使用，可以在导出数据的时候，仅导出部分表的定义,其他表则导出定义和数据
$PGHOME/bin/pg_dump -t 'user*.test*' --exclude-table-data='user02.test*' testdb -f test_dump.sql --verbose
2.6 导出对象定义
-s 参数可以仅仅导出对象的定义
导出db中所有对象的定义：
$PGHOME/bin/pg_dump -s testdb -f test_dump.sql --verbose
导出schema中所有对象的定义：
$PGHOME/bin/pg_dump -s -n user01 testdb -f test_dump.sql --verbose
导出schema中单个或多个对象的定义：
$PGHOME/bin/pg_dump -s -t 'user01.test*' testdb -f test_dump.sql --verbose
-O 参数可以在导出的时候，不将对象指向某个user，只针对文本类型的导出文件：
$PGHOME/bin/pg_dump -s -t 'user01.test*' -O testdb -f test_dump.sql --verbose
2.7 导出数据不包括对象定义
-a 参数仅仅表的数据，而没有表的定义：
$PGHOME/bin/pg_dump -a -t user01.test testdb -f test_dump.sql --verbose
3.其他的一些参数
3.1 -b 导出blob对象到dump文件中
3.2 -c 在导出文件中生成 drop object 的命令
$PGHOME/bin/pg_dump -s -c -t user01.test testdb -f test_dump.sql --verbose
[postgres@dang-db dump]$cat test_dump.sql | grep DROP
ALTER TABLE ONLY user01.test DROP CONSTRAINT test_pkey;
DROP TABLE user01.test;
配合 --if-exists 可以在restore至干净数据库中的时候不产生错误：
$PGHOME/bin/pg_dump -s -c --if-exists -t user01.test testdb -f test_dump.sql --verbose
[postgres@dang-db dump]$cat test_dump.sql | grep DROP
ALTER TABLE IF EXISTS ONLY user01.test DROP CONSTRAINT IF EXISTS test_pkey;
DROP TABLE IF EXISTS user01.test;
PS:该选项仅适用于文本类型的备份文件
3.3 -C 在导出文件中生成 create database 的命令
$PGHOME/bin/pg_dump -s -C testdb -f test_dump.sql --verbose
[postgres@dang-db dump]$cat test_dump.sql | grep "CREATE DATABASE"
CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = tbs_test;
[postgres@dang-db dump]$
配合 -c 可以生成先drop再create database的语句：
$PGHOME/bin/pg_dump -s -c -C testdb -f test_dump.sql --verbose
[postgres@dang-db dump]$cat test_dump.sql | grep DATABASE
DROP DATABASE testdb;
-- Name: testdb; Type: DATABASE; Schema: -; Owner: user01
CREATE DATABASE testdb WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TABLESPACE = tbs_test;
ALTER DATABASE testdb OWNER TO user01;
PS:该选项仅适用于文本类型的备份文件
3.4 -E 以指定的字符集生成dump文件
3.5 --insert 导出数据的时候生成insert into的脚本
$PGHOME/bin/pg_dump -t user01.test --insert testdb -f test_dump.sql --verbose
此种方式的insert into 为：INSERT INTO test VALUES (1, 'test');
通过 --column-insert 可以生成带列名的insert into：INSERT INTO test (id, name) VALUES (1, 'test'); :
$PGHOME/bin/pg_dump -t user01.test --column-insert testdb -f test_dump.sql --verbose
4.一些限制
4.1 导出对象可能无法构建完整的系统
在使用 -t 或者 -n 导出部分表或者部分schema的时候，不会将与之依赖的对象一同导出，因此在构建现有系统的开发测试环境的时候，需要结合业务系统来梳理出所有相关
5.待加强的功能
5.1 导出一类对象
目前无法通过参数控制来导出一类对象，比如单独导出表的定义，函数的定义等。
5.2 从参数文件中读取导出参数
在通过 -t 或者 -n 导出部分表或者schema的时候，如果这些表或者schema比较多且无法通过通配符匹配的时候，希望是将其写在参数文件中，pg_dump来读取，提高了pg_dump命令的可读性和便利性。
6. pg_dumpall
pg_dumpall 可以用来备份整个cluster中的全部database、备份global对象等。正因此如此，需要使用superuser来执行pg_dumpall以便生产完整的备份文件。
pg_dumpall 只能生成文本类型的备份文件，备份过程不支持压缩和并行，因此不建议使用pg_dumpall来备份数据。
pg_dumpall 备份全部database的过程通过循环调用 pg_dump 来实现，因此，pg_dumpall 更适合用来备份一些全局的role、tablespace、database定义等。
6.1 pg_dumpall 官方帮助
[postgres@dang-db dump]$$PGHOME/bin/pg_dumpall --help
pg_dumpall extracts a PostgreSQL database cluster into an SQL script file.
Usage:
pg_dumpall [OPTION]...
General options:
-f, --file=FILENAME output file name
-V, --version output version information, then exit
--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
-c, --clean clean (drop) databases before recreating
-g, --globals-only dump only global objects, no databases
-o, --oids include OIDs in dump
-O, --no-owner skip restoration of object ownership
-r, --roles-only dump only roles, no databases or tablespaces
-s, --schema-only dump only the schema, no data
-S, --superuser=NAME superuser user name to use in the dump
-t, --tablespaces-only dump only tablespaces, no databases or roles
-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
--if-exists use IF EXISTS when dropping objects
--inserts dump data as INSERT commands, rather than COPY
--no-security-labels do not dump security label assignments
--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
--use-set-session-authorization
use SET SESSION AUTHORIZATION commands instead of
ALTER OWNER commands to set ownership
Connection options:
-d, --dbname=CONNSTR connect using connection string
-h, --host=HOSTNAME database server host or socket directory
-l, --database=DBNAME alternative default database
-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 -f/--file is not used, then the SQL script will be written to the standard
output.
Report bugs to <pgsql-bugs@postgresql.org>.
6.2 -g 备份全局对象
$PGHOME/bin/pg_dumpall -g -f test_dump.sql --verbose
包括role和tablespace，不包括database。
6.3 -r 只备份role
$PGHOME/bin/pg_dumpall -r -f test_dump.sql --verbose
6.4 -t 只备份tablespace
$PGHOME/bin/pg_dumpall -t -f test_dump.sql --verbose
6.5 -s 导出cluster中的全部对象定义
$PGHOME/bin/pg_dumpall -s -f test_dump.sql --verbose
包括global对象以及每个database中的对象。
338
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
