PostgreSQL pg_dumpall逻辑备份整个集群案例
创建两个数据库并分别创建表和数据：
postgres=# create database aaa;
CREATE DATABASE
postgres=# create database bbb;
CREATE DATABASE
postgres-# \c aaa
You are now connected to database "aaa" as user "postgres".
create table a(id int);
insert into a values (2);
aaa=# \c bbb
You are now connected to database "bbb" as user "postgres".
create table a(id int);
insert into a values (2);
# 全库级别备份：
# 备份：
pg_dumpall -U postgres > dumpall.sql
#登录数据库删除两个库中的两张表：
postgres=# \c aaa
You are now connected to database "aaa" as user "postgres".
aaa=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
aaa=# drop table a;
DROP TABLE
aaa=# \c bbb
You are now connected to database "bbb" as user "postgres".
bbb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
bbb=# drop table a;
DROP TABLE
# 恢复：
psql -U postgres < dumpall.sql
#验证数据：
[postgres@pca ~]$ psql
psql (13.10)
Type "help" for help.
postgres=# \c aaa
You are now connected to database "aaa" as user "postgres".
aaa=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
aaa=# select * from a;
id
----
2
(1 row)
aaa=# \c bbb
You are now connected to database "bbb" as user "postgres".
bbb=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
(1 row)
bbb=# select * from a;
id
----
2
(1 row)
