PostgreSQL pg_dump逻辑备份案例
单库-备份及恢复
创建数据库以及表:
postgres=# create database test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
\c test;
create table a(id int);
insert into a values (2);
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
test=# select * from a;
id
----
2
(1 row)
# 备份：
pg_dump -U postgres test >test.sql
#删除test库中的表：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
test=# drop table a;
DROP TABLE
test=# \d
Did not find any relations.
# 恢复：
psql
create database test;
psql -U postgres test < test.sql
#查看表中否已恢复：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
test=# select * from a;
id
----
2
(1 row)
# 指定格式进行备份：
# 备份：
pg_dump -Fc -U postgres test >test.dump
# 恢复：
恢复前要删除所有的表
psql
create database test;
pg_restore -d test test.dump
单模式-备份及恢复
# 备份：
pg_dump -U postgres -t 'public.t*' test >test.sql #'public.t*' 表示匹配在public模式下所有以表t开头的表名 test 是库名
# 恢复：
psql -U postgres test < test.sql
单个表-备份及恢复
# 例如备份test库下的a表：
# 备份：
pg_dump -t a test >dump.sql
# 恢复：
create database test;
psql -U postgres test < dump.sql
