Postgresql备份之pg_dump

[postgre@pg-1 postgresql-9.4.4]$ 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 name

输出到指定文件

-F, --format=c|t|p output file format (custom, tar, plain text)

输出格式：

c: 输出适于给 pg_restore 用的客户化归档。这是最灵活的格式，它允许对装载的数据和对象定义进行重新排列。

这个格式缺省的时候是压缩的

t: 输出适合输入到 pg_restore 里的tar归档文件。使用这个归档允许在恢复数据库时重新排序和/或把数据库对象排除在外。

同时也可能可以在恢复的时候限制对哪些数据进行恢复。

p: 输出纯文本SQL脚本文件（缺省）

-v, --verbose verbose mode

这样将令 pg_dump 输出详细的对象评注以及转储文件的启停时间和进度信息到标准输出上。

-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

--version output version information, 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

输出在创建数据库创建命令之前先清理（删除）该数据库对象的命令。

这个选项只是对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore 的时候声明选项。

-C, --create include commands to create database in dump

以一条创建该数据库本身并且与这个数据库联接等命令开头进行输出。

（如果是这种形式的脚本，那么你在运行脚本之前和哪个数据库联接就不重要了。）

这个选项只对纯文本格式有意义。对于归档格式，你可以在调用 pg_restore 的时候声明该选项。

-E, --encoding=ENCODING dump the data in encoding ENCODING

字符编码

-n, --schema=SCHEMA dump the named schema(s) only

只转存指定schema的内容，如果没有声明这个选项，所有目标数据库中的非系统模式都会被转储出来。

-N, --exclude-schema=SCHEMA do NOT dump the named schema(s)

排查不转存的schema

-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

转存指定table

-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

--inserts dump data as INSERT commands, rather than COPY

通过insert命令备份数据

--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

--no-tablespaces do not dump tablespace assignments

--role=ROLENAME do SET ROLE before dump

--use-set-session-authorization

use SET SESSION AUTHORIZATION commands instead of

ALTER OWNER commands to set ownership

Connection options:

-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)

If no database name is supplied, then the PGDATABASE environment

variable value is used.

Report bugs to <pgsql-bugs@postgresql.org>.

[postgre@pg-1 postgresql-9.4.4]$ pg_dump -U pgtest -f /opt/pgtest.dump -Fp -Eutf8

pg_dump: server version: 9.4.4; pg_dump version: 8.4.13

pg_dump: aborting because of server version mismatch

由于操作系统安装了postgresql-8.4.13-1:

pg_dump使用了/usr/bin/pg_dump导致：

解决方式：1）卸载之前系统安装的postgresql;

2) 将新安装的postgresql的PATH设置为：

PATH=/usr/local/postgresql-9.4.4/bin:$PATH:$HOME/bin

export PGDATA=/usr/local/postgresql-9.4.4/data

export PATH

测试环境：

[postgre@pg-1 postgresql-9.4.4]$ psql postgres

psql (8.4.13, server 9.4.4)

WARNING: psql version 8.4, server version 9.4.

Some psql features might not work.

Type "help" for help.

postgres=# \c pgtest pgtest

psql (8.4.13, server 9.4.4)

WARNING: psql version 8.4, server version 9.4.

Some psql features might not work.

You are now connected to database "pgtest" as user "pgtest".

pgtest=> create table test01 (id int,name varchar(20));

CREATE TABLE

pgtest=> insert into test01 values (1,'mytest01');

INSERT 0 1

pgtest=> insert into test01 values (2

INSERT 0 1

pgtest=> insert into test01 values (3

INSERT 0 1

pgtest=> select * from test01;

id | name

----+----------

1 | mytest01

2 | mytest02

3 | mytest03

(3 rows)

pgtest=> \q

[postgre@pg-1 postgresql-9.4.4]$ pg_dump -Upgtest -Fp -f pgtest.dmp pgtest

[postgre@pg-1 postgresql-9.4.4]$ cat pgtest.dmp

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'SQL_ASCII';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: pgtest; Type: TABLE; Schema: public; Owner: pgtest; Tablespace:

CREATE TABLE pgtest (

id integer,

name character varying(20)

);

ALTER TABLE pgtest OWNER TO pgtest;

-- Name: test01; Type: TABLE; Schema: public; Owner: pgtest; Tablespace:

CREATE TABLE test01 (

id integer,

name character varying(20)

);

ALTER TABLE test01 OWNER TO pgtest;

-- Data for Name: pgtest; Type: TABLE DATA; Schema: public; Owner: pgtest

COPY pgtest (id, name) FROM stdin;

-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: pgtest

COPY test01 (id, name) FROM stdin;

1 mytest01

2 mytest02

3 mytest03

-- Name: public; Type: ACL; Schema: -; Owner: postgre

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgre;

GRANT ALL ON SCHEMA public TO postgre;

GRANT ALL ON SCHEMA public TO PUBLIC;

-- PostgreSQL database dump complete

注：默认采用的是copy的方式

以insert的方式备份测试：

[postgre@pg-1 postgresql-9.4.4]$ pg_dump -Upgtest -Fp --insert -f pgtest_insert.dmp pgtest

[postgre@pg-1 postgresql-9.4.4]$ cat pgtest_insert.dmp

-- PostgreSQL database dump

SET statement_timeout = 0;

SET lock_timeout = 0;

SET client_encoding = 'SQL_ASCII';

SET standard_conforming_strings = on;

SET check_function_bodies = false;

SET client_min_messages = warning;

-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner:

CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;

-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner:

COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

-- Name: pgtest; Type: TABLE; Schema: public; Owner: pgtest; Tablespace:

CREATE TABLE pgtest (

id integer,

name character varying(20)

);

ALTER TABLE pgtest OWNER TO pgtest;

-- Name: test01; Type: TABLE; Schema: public; Owner: pgtest; Tablespace:

CREATE TABLE test01 (

id integer,

name character varying(20)

);

ALTER TABLE test01 OWNER TO pgtest;

-- Data for Name: pgtest; Type: TABLE DATA; Schema: public; Owner: pgtest

-- Data for Name: test01; Type: TABLE DATA; Schema: public; Owner: pgtest

INSERT INTO test01 VALUES (1, 'mytest01');

INSERT INTO test01 VALUES (2, 'mytest02');

INSERT INTO test01 VALUES (3, 'mytest03');

-- Name: public; Type: ACL; Schema: -; Owner: postgre

REVOKE ALL ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA public FROM postgre;

GRANT ALL ON SCHEMA public TO postgre;

GRANT ALL ON SCHEMA public TO PUBLIC;

-- PostgreSQL database dump complete

pg_dumpall

pg_dumpall会对整个数据集合实例备份，每备份一个数据库时都要进行重新的连接认证。

可通过设置pgpass文件避免进行多次密码填写

秒客网

Postgresql备份之pg_dump

相关文章