当前版本： PG 9.5.3
1.pg_restore 官方帮助
[postgres@dang-db dump]$$PGHOME/bin/pg_restore --help
pg_restore restores a PostgreSQL database from an archive created by pg_dump.
Usage:
pg_restore [OPTION]... [FILE]
General options:
-d, --dbname=NAME connect to database name
-f, --file=FILENAME output file name
-F, --format=c|d|t backup file format (should be automatic)
-l, --list print summarized TOC of the archive
-v, --verbose verbose mode
-V, --version output version information, then exit
-?, --help show this help, then exit
Options controlling the restore:
-a, --data-only restore only the data, no schema
-c, --clean clean (drop) database objects before recreating
-C, --create create the target database
-e, --exit-on-error exit on error, default is to continue
-I, --index=NAME restore named index
-j, --jobs=NUM use this many parallel jobs to restore
-L, --use-list=FILENAME use table of contents from this file for
selecting/ordering output
-n, --schema=NAME restore only objects in this schema
-O, --no-owner skip restoration of object ownership
-P, --function=NAME(args) restore named function
-s, --schema-only restore only the schema, no data
-S, --superuser=NAME superuser user name to use for disabling triggers
-t, --table=NAME restore named table
-T, --trigger=NAME restore named trigger
-x, --no-privileges skip restoration of access privileges (grant/revoke)
-1, --single-transaction restore as a single transaction
--disable-triggers disable triggers during data-only restore
--enable-row-security enable row security
--if-exists use IF EXISTS when dropping objects
--no-data-for-failed-tables do not restore data of tables that could not be
created
--no-security-labels do not restore security labels
--no-tablespaces do not restore tablespace assignments
--section=SECTION restore named section (pre-data, data, or post-data)
--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)
--role=ROLENAME do SET ROLE before restore
The options -I, -n, -P, -t, -T, and --section can be combined and specified
multiple times to select multiple objects.
If no input file name is supplied, then standard input is used.
Report bugs to <pgsql-bugs@postgresql.org>.
2. 具体的一些例子
2.1 恢复整个database
恢复到原来的dbname：
$PGHOME/bin/pg_dump -Fc testdb -f testdb.dump
[postgres@dang-db dump]$$PGHOME/bin/psql -c 'drop database testdb;'
DROP DATABASE
[postgres@dang-db dump]$$PGHOME/bin/psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -C -d postgres testdb.dump &
[1] 30022
[postgres@dang-db dump]$
[1]+ Done $PGHOME/bin/pg_restore -C -d postgres testdb.dump
[postgres@dang-db dump]$$PGHOME/bin/psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | user01 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)
[postgres@dang-db dump]$$PGHOME/bin/psql -d testdb -U user01 -c 'select count(*) from test;'
count
---------
1000000
(1 row)
$PGHOME/bin/pg_restore -C -d testdb01 testdb.dump
恢复到新的dbname中：
[postgres@dang-db dump]$$PGHOME/bin/createdb -T template0 testdb01;
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -d testdb01 testdb.dump &
[1] 30125
[postgres@dang-db dump]$
[postgres@dang-db dump]$
[1]+ Done $PGHOME/bin/pg_restore -d testdb01 testdb.dump
[postgres@dang-db dump]$$PGHOME/bin/psql -l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
testdb | user01 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb01 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
testdb02 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(6 rows)
[postgres@dang-db dump]$$PGHOME/bin/psql -d testdb01 -U user01 -c 'select count(*) from test;'
count
---------
1000000
(1 row)
同时在恢复的时候，可以添加 -j参数来利用并行提高恢复的速度。
$PGHOME/bin/pg_restore -d testdb01 -j 5testdb.dump
2.2 列出备份文件包含的内容
在拿到一个备份文件的时候，如果不知备份的来源，可以通过pg_restore提供的 -l 参数来查看备份文件中都包含哪些内容
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -l testdb.dump
;
; Archive created at 2016-08-12 16:48:36 CST
; dbname: testdb
; TOC Entries: 23
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.5.3
; Dumped by pg_dump version: 9.5.3
;
;
; Selected TOC Entries:
;
2991; 1262 16416 DATABASE - testdb user01
6; 2615 2200 SCHEMA - public postgres
2992; 0 0 COMMENT - SCHEMA public postgres
2993; 0 0 ACL - public postgres
8; 2615 16417 SCHEMA - user01 user01
9; 2615 24579 SCHEMA - user02 user01
1; 3079 13223 EXTENSION - plpgsql
2994; 0 0 COMMENT - EXTENSION plpgsql
188; 1255 24576 FUNCTION user01 add(integer, numeric) user01
186; 1259 24586 TABLE public test postgres
184; 1259 24577 SEQUENCE user01 seq_test user01
183; 1259 16432 TABLE user01 test user01
2995; 0 0 COMMENT user01 TABLE test user01
187; 1259 24592 TABLE user01 test02 user01
185; 1259 24580 TABLE user02 test01 user01
2985; 0 24586 TABLE DATA public test postgres
2996; 0 0 SEQUENCE SET user01 seq_test user01
2982; 0 16432 TABLE DATA user01 test user01
2986; 0 24592 TABLE DATA user01 test02 user01
2984; 0 24580 TABLE DATA user02 test01 user01
2867; 2606 16439 CONSTRAINT user01 test_pkey user01
在上面输出信息的基础上，可以结合 -n 或者 -t 来进一步缩小范围
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -l -n user01 testdb.dump
;
; Archive created at 2016-08-12 16:48:36 CST
; dbname: testdb
; TOC Entries: 23
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.5.3
; Dumped by pg_dump version: 9.5.3
;
;
; Selected TOC Entries:
;
188; 1255 24576 FUNCTION user01 add(integer, numeric) user01
184; 1259 24577 SEQUENCE user01 seq_test user01
183; 1259 16432 TABLE user01 test user01
2995; 0 0 COMMENT user01 TABLE test user01
187; 1259 24592 TABLE user01 test02 user01
2996; 0 0 SEQUENCE SET user01 seq_test user01
2982; 0 16432 TABLE DATA user01 test user01
2986; 0 24592 TABLE DATA user01 test02 user01
2867; 2606 16439 CONSTRAINT user01 test_pkey user01
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -l -n user01 -t 'test' testdb.dump
;
; Archive created at 2016-08-12 16:48:36 CST
; dbname: testdb
; TOC Entries: 23
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.5.3
; Dumped by pg_dump version: 9.5.3
;
;
; Selected TOC Entries:
;
183; 1259 16432 TABLE user01 test user01
2982; 0 16432 TABLE DATA user01 test user01
与pg_dump 不同的是，pg_restore的 -t -n 不能使用通配符，在restore的时候需要注意。
同时，restore提供了一个参数 -L 来从配置文件中读取需要恢复的对象或数据，而配置文件则可以通过 -l 来生成。
生成schema user01的配置文件：
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -l -n user01 testdb.dump > user01.lst
[postgres@dang-db dump]$
[postgres@dang-db dump]$more user01.lst
;
; Archive created at 2016-08-12 16:48:36 CST
; dbname: testdb
; TOC Entries: 23
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 9.5.3
; Dumped by pg_dump version: 9.5.3
;
;
; Selected TOC Entries:
;
188; 1255 24576 FUNCTION user01 add(integer, numeric) user01
184; 1259 24577 SEQUENCE user01 seq_test user01
183; 1259 16432 TABLE user01 test user01
2995; 0 0 COMMENT user01 TABLE test user01
187; 1259 24592 TABLE user01 test02 user01
2996; 0 0 SEQUENCE SET user01 seq_test user01
2982; 0 16432 TABLE DATA user01 test user01
2986; 0 24592 TABLE DATA user01 test02 user01
2867; 2606 16439 CONSTRAINT user01 test_pkey user01
删除 schema user01后新建schema
postgres@testdb:5532 # drop schema user01 cascade;
NOTICE: drop cascades to 4 other objects
DETAIL: drop cascades to function user01.add(integer,numeric)
drop cascades to sequence user01.seq_test
drop cascades to table user01.test02
drop cascades to table user01.t01
DROP SCHEMA
postgres@testdb:5532 # \dn
List of schemas
Name | Owner
--------+----------
public | postgres
user02 | user01
(2 rows)
postgres@testdb:5532 # create schema user01 authorization user01;
CREATE SCHEMA
利用配置文件恢复 schema user01中的对象
$PGHOME/bin/pg_restore -L user01.lst testdb.dump &
[postgres@dang-db dump]$$PGHOME/bin/psql -d testdb -U user01 -c 'select count(*) from test;'
count
---------
1000000
(1 row)
2.3 恢复表和索引
恢复表定义
user01@testdb:5532 > \d+ test
Table "user01.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
name | text | | extended | |
Indexes:
"test_pkey" PRIMARY KEY, btree (id)
user01@testdb:5532 > drop table test;
DROP TABLE
user01@testdb:5532 > \d+ user01.test
Did not find any relation named "user01.test".
$PGHOME/bin/pg_restore -s -t test -n user01 testdb.dump -d testdb
user01@testdb:5532 > \d+ test
Table "user01.test"
Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
name | text | | extended | |
user01@testdb:5532 > select count(*) from test;
count
-------
0
(1 row)
恢复表数据
$PGHOME/bin/pg_restore -a -t test -n user01 testdb.dump -d testdb
user01@testdb:5532 > select count(*) from test;
count
---------
1000000
(1 row)
表中有数据的时候需要注意
如果在导入的时候，表已经存在，表中的数据默认情况下还会继续导入，对于表上没有主键或者唯一索引的情况，数据就会重复导入，会造成业务上的数据重复。针对这种情况，pg_restore提供了no-data-for-failed-tables选项来防止重复数据的进入，即在表创建失败的时候，表中的数据不进行导入。建议除非在--clean的情况下(即创建对象前先删除)，不使用该选项，否则在进行数据导入的时候均启用。
不启用时候进行数据导入，原test表中数据记录1000000条。
[postgres@dang-db dump]$$PGHOME/bin/psql -U user01 -d testdb -c 'select count(*) from test;';
$PGHOME/bin/pg_restore -a -t test -n user01 testdb.dump -d testdb
$PGHOME/bin/psql -U user01 -d testdb -c 'select count(*) from test;'; count
---------
1000000
(1 row)
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -t test -n user01 testdb.dump -d testdb
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 187; 1259 24713 TABLE test user01
pg_restore: [archiver (db)] could not execute query: ERROR: relation "test" already exists
Command was: CREATE TABLE test (
id integer NOT NULL,
name text
);
WARNING: errors ignored on restore: 1
[postgres@dang-db dump]$$PGHOME/bin/psql -U user01 -d testdb -c 'select count(*) from test;';
count
---------
2000000
(1 row)
添加 --no-data-for-failed-tables 继续导入：
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -t test -n user01 testdb.dump -d testdb --no-data-for-failed-tables
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 190; 1259 24741 TABLE test user01
pg_restore: [archiver (db)] could not execute query: ERROR: relation "test" already exists
Command was: CREATE TABLE test (
id integer NOT NULL,
name text
);
WARNING: errors ignored on restore: 1
[postgres@dang-db dump]$$PGHOME/bin/psql -U user01 -d testdb -c 'select count(*) from test;';
count
---------
2000000
(1 row)
[postgres@dang-db dump]$
恢复索引
-I 选项可以单独恢复索引
user01@testdb:5532 > \d test02
Table "user01.test02"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"idx_test02_id" btree (id)
"idx_test02_id1" btree (id) WHERE id < 10
user01@testdb:5532 > drop index idx_test02_id;
DROP INDEX
user01@testdb:5532 > \d test02
Table "user01.test02"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"idx_test02_id1" btree (id) WHERE id < 10
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -I idx_test02_id -n user01 testdb.dump -d testdb --verbose
pg_restore: connecting to database for restore
pg_restore: creating INDEX "user01.idx_test02_id"
pg_restore: setting owner and privileges for INDEX "user01.idx_test02_id"
user01@testdb:5532 > \d test02
Table "user01.test02"
Column | Type | Modifiers
--------+---------+-----------
id | integer |
name | text |
Indexes:
"idx_test02_id" btree (id)
"idx_test02_id1" btree (id) WHERE id < 10
2.4 恢复其他对象的定义
恢复触发器
user01@testdb:5532 > select tgname from pg_trigger;
tgname
---------------------
delete_test_trigger
(1 row)
user01@testdb:5532 > drop trigger delete_test_trigger on test;
DROP TRIGGER
$PGHOME/bin/pg_restore -T delete_test_trigger -n user01 testdb.dump -d testdb --verbose
user01@testdb:5532 > select tgname from pg_trigger;
tgname
---------------------
delete_test_trigger
(1 row)
恢复函数
- 删除现有函数
user01@testdb:5532 > \sf test_delete_trigger
CREATE OR REPLACE FUNCTION user01.test_delete_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
insert into test_delete values (old.id,old.name);
return old;
end;
$function$
user01@testdb:5532 > drop function test_delete_trigger();
DROP FUNCTION
user01@testdb:5532 > \sf test_delete_trigger
ERROR: function "test_delete_trigger" does not exist
- -P 参数恢复函数
[postgres@dang-db dump]$$PGHOME/bin/pg_restore -P 'test_delete_trigger()' -n user01 testdb.dump -d testdb --verbose
pg_restore: connecting to database for restore
pg_restore: creating FUNCTION "user01.test_delete_trigger()"
pg_restore: setting owner and privileges for FUNCTION "user01.test_delete_trigger()"
user01@testdb:5532 > \sf test_delete_trigger
CREATE OR REPLACE FUNCTION user01.test_delete_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
begin
insert into test_delete values (old.id,old.name);
return old;
end;
$function$
2.5 其他一些参数选项
--no-tablespaces 在恢复的时候不指定表空间，所有的对象默认恢复到所在数据库的默认表空间。对于数据的迁移和新环境的搭建很有帮助。
--if-exists 配合 --clean参数一起使用，在删除对象的时候加上 if exists。
-f filename 可以利用该参数将备份文件内容恢复到文本文件中。
3.待加强的功能
同样，希望可以在恢复的时候可以根据对象类型恢复同类全部对象。
可以提供将参数选项写入配置文件，然后从配置文件中读取的功能。
可以通过扩展的dblink实现不落地迁移数据。
扫一扫
6784
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
