pg_dump/pg_dumpall使用

当前版本: 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中的对象。

转载于:https://my.oschina.net/yafeishi/blog/742306

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值