PG自定义格式逻辑备份与恢复

实例

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)


已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 创作都市 设计师:CSDN官方博客 返回首页