PG自定义格式逻辑备份与恢复
原创
©著作权归作者所有：来自51CTO博客作者岳麓丹枫的原创作品，请联系作者获取转载授权，否则将追究法律责任
实例
drop database if exists test;
create database test;
\c test;
create table test (id int);
insert into test select n from generate_series(1,10) n;
select * from test;
\q
逻辑备份为自定义格式
pg_dump -Fc -Z -C -c --disable-triggers --if-exists -h 127.0.0.1 -U postgres -p 5432 -f test.dmp test
操作
truncate test;
select * from test;
\q
自定义格式逻辑恢复
pg_restore -h 127.0.0.1 -U postgres -d postgres -p 5432 -C -c --if-exists test.dmp
操作过程
[postgres@node_206 ~]$psql
psql (12.3)
Type "help" for help.
postgres=# drop database if exists test;
DROP DATABASE
postgres=# create database test;
CREATE DATABASE
postgres=# \c test;
You are now connected to database "test" as user "postgres".
test=# create table test (id int);
CREATE TABLE
test=# insert into test select n from generate_series(1,10) n;
INSERT 0 10
test=# select * from test;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
test=# \q
[postgres@node_206 ~]$ls
postgres_dba test.dmp
[postgres@node_206 ~]$rm -f *dmp
[postgres@node_206 ~]$pg_dump -Fc -Z -C -c --disable-triggers --if-exists -h 127.0.0.1 -U postgres -p 5432 -f test.dmp test
[postgres@node_206 ~]$
[postgres@node_206 ~]$ls
postgres_dba test.dmp
[postgres@node_206 ~]$psql
psql (12.3)
Type "help" for help.
postgres=# truncate test;
TRUNCATE TABLE
postgres=# select * from test;
id
----
(0 rows)
postgres=# \q
[postgres@node_206 ~]$pg_restore -h 127.0.0.1 -U postgres -d postgres -p 5432 -C -c --if-exists test.dmp
[postgres@node_206 ~]$psql
psql (12.3)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# select * from test;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
