PostgreSQL【使用篇】02：备份和恢复【pg_dump、pg_dumpall、pg_restore、psql以及图形化工具、pg_basebackup】

做一个有趣的人Zz

已于 2025-06-03 16:12:02 修改

阅读量1.1k

点赞数 22

分类专栏： PostgreSQL 文章标签： postgresql 数据库

于 2025-06-03 16:11:48 首次发布

本文链接：https://blog.csdn.net/weixin_40612128/article/details/148391044

版权

PostgreSQL 专栏收录该内容

26 篇文章

订阅专栏

一、环境准备

1、环境信息

192.168.184.190  备份节点
192.168.184.191  恢复节点

在这里插入图片描述
安装postgresql，可参考：
CentOS 7.5【脚本自动编译】安装PostgreSQL17.5

2、环境检查

检查数据库是否启动

service postgresql status

在这里插入图片描述
如果没启动进行启动

service postgresql start
service postgresql status

在这里插入图片描述

3、创建测试数据

在备份库创建测试数据

（1）登录数据库

psql -U postgres

在这里插入图片描述

（2）创建测试数据

create database mydb1;
create database mydb2;
create database mydb3;
\c mydb1;
create schema mydb1_pgtest1;
create schema mydb1_pgtest2;
create schema mydb1_pgtest3;
\c mydb2;
create schema mydb2_pgtest1;
create schema mydb2_pgtest2;
create schema mydb2_pgtest3;
\c mydb3;
create schema mydb3_pgtest1;
create schema mydb3_pgtest2;
create schema mydb3_pgtest3;

\c mydb1;
create table mydb1_pgtest1.t1(id int primary key,name varchar(20));
create table mydb1_pgtest1.t2(id int primary key,name varchar(20));
create table mydb1_pgtest1.t3(id int primary key,name varchar(20));
INSERT INTO mydb1_pgtest1.t1 (id, name) VALUES(1, 'mydb1_pgtest1_t1');
INSERT INTO mydb1_pgtest1.t2 (id, name) VALUES(1, 'mydb1_pgtest1_t2');
INSERT INTO mydb1_pgtest1.t3 (id, name) VALUES(1, 'mydb1_pgtest1_t3');
create table mydb1_pgtest2.t2(id int primary key,name varchar(20));
INSERT INTO mydb1_pgtest2.t2 (id, name) VALUES(1, 'mydb1_pgtest2');
create table mydb1_pgtest3.t3(id int primary key,name varchar(20));
INSERT INTO mydb1_pgtest3.t3 (id, name) VALUES(1, 'mydb1_pgtest3');

\c mydb2;
create table mydb2_pgtest1.t1(id int primary key,name varchar(20));
INSERT INTO mydb2_pgtest1.t1 (id, name) VALUES(1, 'mydb2_pgtest1');
create table mydb2_pgtest2.t2(id int primary key,name varchar(20));
INSERT INTO mydb2_pgtest2.t2 (id, name) VALUES(1, 'mydb2_pgtest2');
create table mydb2_pgtest3.t3(id int primary key,name varchar(20));
INSERT INTO mydb2_pgtest3.t3 (id, name) VALUES(1, 'mydb2_pgtest3');

\c mydb3;
create table mydb3_pgtest1.t1(id int primary key,name varchar(20));
INSERT INTO mydb3_pgtest1.t1 (id, name) VALUES(1, 'mydb3_pgtest1');
create table mydb3_pgtest2.t2(id int primary key,name varchar(20));
INSERT INTO mydb3_pgtest2.t2 (id, name) VALUES(1, 'mydb3_pgtest2');
create table mydb3_pgtest3.t3(id int primary key,name varchar(20));
INSERT INTO mydb3_pgtest3.t3 (id, name) VALUES(1, 'mydb3_pgtest3');

在这里插入图片描述

4、查看数据

（1）查看数据库

查看有哪些数据库

\l+

在这里插入图片描述

（2）查看schema

查看当前库有哪些schema

\c mydb1
\dn+
\c mydb2
\dn+
\c mydb3
\dn+

在这里插入图片描述

（3）查看表

查看有哪些表

\c mydb1
\dt mydb1_pgtest1.*
\dt mydb1_pgtest2.*
\dt mydb1_pgtest3.*

\c mydb2
\dt mydb2_pgtest1.*
\dt mydb2_pgtest2.*
\dt mydb2_pgtest3.*

\c mydb3
\dt mydb3_pgtest1.*
\dt mydb3_pgtest2.*
\dt mydb3_pgtest3.*

在这里插入图片描述

（4）查看表中数据

\c mydb1
select * from mydb1_pgtest1.t1;
select * from mydb1_pgtest2.t2;
select * from mydb1_pgtest3.t3;

\c mydb2
select * from mydb2_pgtest1.t1;
select * from mydb2_pgtest2.t2;
select * from mydb2_pgtest3.t3;

\c mydb3
select * from mydb3_pgtest1.t1;
select * from mydb3_pgtest2.t2;
select * from mydb3_pgtest3.t3;

在这里插入图片描述

5、创建用户【可选】

一般会使用postgres用户进行备份和恢复

（1）创建备份用户

-- 创建备份用户并设置密码
CREATE USER backup_user WITH PASSWORD 'SecureBackupPass123!' LOGIN;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE your_database TO backup_user;

-- 授予模式使用权限
GRANT USAGE ON SCHEMA public TO backup_user;

-- 授予现有表的 SELECT 权限
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup_user;

-- 设置默认权限，使新表自动继承权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup_user;

（2）创建恢复用户

-- 创建恢复用户并设置密码
CREATE USER restore_user WITH PASSWORD 'SecureRestorePass456!' LOGIN;

-- 授予数据库连接权限
GRANT CONNECT ON DATABASE your_database TO restore_user;

-- 授予模式使用和创建对象的权限
GRANT USAGE, CREATE ON SCHEMA public TO restore_user;

-- 授予现有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO restore_user;

-- 设置默认权限，使新表自动继承权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLES TO restore_user;

6、创建备份目录

在备份节点操作

mkdir -p /data/backup/postgresql/
cd /data/backup/postgresql/

在这里插入图片描述

7、创建恢复目录

在恢复节点操作

mkdir -p /data/restore/postgresql/
cd /data/restore/postgresql/

在这里插入图片描述

8、修改配置

备份节点和恢复节点都进行修改，然后重启数据库

cd /usr/local/pgsql/data
vi pg_hba.conf

在这里插入图片描述
重启数据库

service postgresql restart
service postgresql status

在这里插入图片描述

9、设置postgres用户密码

一般postgres用户是没有密码的，但如果要远程登录的话，需要设置密码

psql -U postgres

在这里插入图片描述

ALTER USER postgres WITH PASSWORD 'admin';

在这里插入图片描述

10、开启归档

如果使用我之前的脚步进行安装的话，是默认开启归档的。
如果没有配置归档，可参考：
PostgreSQL开启归档模式

二、逻辑备份【pg_dump】和逻辑恢复【pg_restore】

1、逻辑备份【pg_dump】

（1）备份单表

（a）基本命令格式

pg_dump -U 用户名 -h 主机名 -p 端口 -d 数据库名 -t 表名 -F 格式 -f 输出文件路径

参数说明：

-t, --table=表名：指定要备份的表名（支持模式名前缀，如 public.table_name）。
-F, --format=格式：指定输出格式（常用 c 表示自定义格式，p 表示纯文本）。
-f, --file=文件名：指定备份文件输出路径。

（b）举例：备份数据库为mydb1、schema为mydb1_pgtest1、表名为t1的数据

例如备份库名：mydb1，schema：mydb1_pgtest1，表名：t1的数据。
备份纯文本格式（可读性强）：

pg_dump -U postgres -h 192.168.184.190 -p 5432 -d mydb1 -t mydb1_pgtest1.t1 -F p -f /data/backup/postgresql/mydb1_pgtest1_t1.sql

在这里插入图片描述
备份指定格式：

pg_dump -U postgres -h 192.168.184.190 -p 5432 -d mydb1 -t mydb1_pgtest1.t1 -F c -f /data/backup/postgresql/mydb1_pgtest1_t1.dump

在这里插入图片描述

（c）查看备份数据

cd /data/backup/postgresql
ll

在这里插入图片描述

（d）查看备份文件内容

cat mydb1_pgtest1_t1.sql

在这里插入图片描述

cat mydb1_pgtest1_t1.dump

在这里插入图片描述

（2）备份多个表

（a）基本命令格式

pg_dump -U 用户名 -h 主机名 -p 端口 -d 数据库名 -t 表1 -t 表2 -F 格式 -f 输出文件路径

参数说明：

-t, --table=表名：指定要备份的表名，支持多次使用（每个表名单独 -t）。
-F, --format=格式：输出格式（常用 c 自定义格式或 p 纯文本格式）。
-f, --file=文件名：指定备份文件路径。

（b）举例：备份同一模式下的多个表

pg_dump -U postgres -d mydb1 \
  -t mydb1_pgtest1.t1 -t mydb1_pgtest1.t2 \
  -F p -f /data/backup/postgresql/mydb1_pgtest1_t1_t2.sql

在这里插入图片描述
查看备份内容

cd /data/backup/postgresql
cat mydb1_pgtest1_t1_t2.sql

在这里插入图片描述

（c）举例：备份不同模式下的表

pg_dump -U postgres -d mydb1 \
  -t mydb1_pgtest1.t1 -t mydb1_pgtest2.t2 \
  -F p -f /data/backup/postgresql/mydb1_pgtest1_t1_mydb1_pgtest2_t2.sql

在这里插入图片描述
查看备份内容

cd /data/backup/postgresql
cat mydb1_pgtest1_t1_mydb1_pgtest2_t2.sql

在这里插入图片描述

（d）举例：使用通配符匹配表名

pg_dump -U postgres -d mydb1 \
  -t 'mydb1_pgtest1.t*' \
  -F p -f /data/backup/postgresql/mydb1_pgtest1_t.sql

在这里插入图片描述
查看备份内容

cd /data/backup/postgresql
cat cat mydb1_pgtest1_t.sql

在这里插入图片描述

（3）备份单库

pg_dump -U postgres -h 192.168.184.190 -p 5432 -d mydb1 -F p -f /data/backup/postgresql/mydb1.sql

在这里插入图片描述
查看备份内容

cd /data/backup/postgresql
cat mydb1.sql

在这里插入图片描述

（4）备份多库

pg_dump -U postgres -h 192.168.184.190 -p 5432 -d mydb1 -d mydb2 -d mydb3 -F p -f /data/backup/postgresql/mydb1_mydb2_mydb3.sql

在这里插入图片描述

cd /data/backup/postgresql/
cat mydb1_mydb2_mydb3.sql

在这里插入图片描述

（5）全库备份【pg_dumpall】

（a）备份数据库

pg_dumpall -U postgres -h 192.168.184.190 -p 5432 > /data/backup/postgresql/all.sql

在这里插入图片描述
查看数据

cat all.sql

在这里插入图片描述

（b）仅备份角色和表空间定义

pg_dumpall -U postgres -h 192.168.184.190 -p 5432 -g > /data/backup/postgresql/all_g.sql

在这里插入图片描述

2、逻辑恢复【pg_restore和psql】

（1）使用pg_restore恢复数据

注意，pg_restore只能进行恢复格式化的数据，例如.dump

（a）传输数据

cd /data/backup/postgresql
scp mydb1_pgtest1_t1.dump 192.168.184.191:/data/restore/postgresql/

在这里插入图片描述

（b）查看传输的数据

cd /data/restore/postgresql/
ll

在这里插入图片描述

（c）进行恢复

注意，这里需要创建数据库和schema，或者将schema指定到public，否则会出现如下错误：
在这里插入图片描述

psql -U postgres
create database mydb1;
\c mydb1;
create schema mydb1_pgtest1;

在这里插入图片描述

pg_restore --dbname=mydb1 --username=postgres --jobs=4 --verbose mydb1_pgtest1_t1.dump

在这里插入图片描述

（d）查看数据

psql -U postgres
\c mydb1
\dn
\dt mydb1_pgtest1.*
select * from mydb1_pgtest1.t1;

在这里插入图片描述

（2）使用psql恢复数据

传输数据

cd /data/backup/postgresql
scp all.sql 192.168.184.191:/data/restore/postgresql/

在这里插入图片描述
查询传输的数据

cd /data/restore/postgresql/
ll

在这里插入图片描述
进行恢复数据

--恢复一个SQL备份文件并忽略过程中可能发生的所有错误
psql -U postgres -f all.sql
恢复一个SQL备份文件，如遇错误则立即停止恢复
psql -U postgres --set ON_ERROR_STOP=on -f all.sql

3、区别

（1）pg_dump和pg_dumpall

PostgreSQL 中的 pg_dump 和 pg_dumpall 是两个用于备份的工具，但它们在用途、范围和功能上有显著区别。
在这里插入图片描述

（2）psql和pg_restore

psql 和 pg_restore 是 PostgreSQL 中两个功能不同的工具，分别用于不同场景
在这里插入图片描述

三、图形化界面导出和恢复

1、Navicat导出和导入

（1）进行导出

在这里插入图片描述

（2）查看导出的文件

在这里插入图片描述

（3）导出表结构

在这里插入图片描述

查看

（4）创建表

在这里插入图片描述

（5）进行导入

在这里插入图片描述

（6）查看数据

在这里插入图片描述

2、pgAdmin导出和恢复

（1）pgAdmin下载

网盘地址：

链接: https://pan.baidu.com/s/1S8_4YnMD3uaQtmh2xcOW6w?pwd=uy9c
提取码: uy9c

官网地址：

https://ftp.postgresql.org/pub/pgadmin/pgadmin4/v9.4/windows/

（2）设置中文

在这里插入图片描述

（3）进行连接

在这里插入图片描述

（4）进行导出

在这里插入图片描述

（5）查看备份的数据

在这里插入图片描述

（6）进行导入

创建数据库mydb1，然后进行导入
在这里插入图片描述

四、物理备份【pg_basebackup】

1、检查WAL配置

psql -U postgres

SHOW wal_level;
SHOW archive_mode;
show archive_command;

在这里插入图片描述
wal_level要为replica

ALTER SYSTEM SET wal_level = 'replica';

archive_mode要为on

ALTER SYSTEM SET archive_mode = 'on';

设置好archive_command

ALTER SYSTEM SET archive_command = 'DATE=$(date "+%%F-%%T");DIR="/usr/local/pgsql/data/arch/$DATE";(test -d $DIR || /bin/mkdir -p $DIR) && /bin/cp %p $DIR/%f';
show archive_command;

2、进行全量备份

mkdir -p /data/backup/postgresql/pg_basebackup
pg_basebackup -U postgres -Ft -Pv -z -Z5 -p 5432 -D /data/backup/postgresql/pg_basebackup/

在这里插入图片描述

3、查看备份文件

cd /data/backup/postgresql/pg_basebackup/
ll

在这里插入图片描述

4、停止数据库

service postgresql stop
service postgresql status

在这里插入图片描述

5、删除数据

rm -rf /usr/local/pgsql/data/*

在这里插入图片描述

6、解压文件到目录

（1）恢复基本数据文件

tar zxvf /data/backup/postgresql/pg_basebackup/base.tar.gz -C /usr/local/pgsql/data

在这里插入图片描述

（2）恢复wal日志

tar zxvf /data/backup/postgresql/pg_basebackup/pg_wal.tar.gz -C /usr/local/pgsql/data/arch

在这里插入图片描述

7、设置恢复方式

修改postgresql.conf文件，这里有三种方式，我选择第一种

（1）立刻恢复

cd /usr/local/pgsql/data/
vi postgresql.conf

修改如下内容：

restore_command = 'cp /usr/local/pgsql/data/arch/%f %p' 
recovery_target = 'immediate'

在这里插入图片描述
修改recovery_target_action，如果不想进入备份模式，直接数据库启动就可以用，那么就使用promote。

recovery_target_action  #指定在达到恢复目标时服务器采取的动作。  
pause  #默认值，表示恢复将被暂停 
promote  #表示恢复结束且服务器将开始接受连接 
shutdown #表示在达到恢复目标之后停止服务器。

在这里插入图片描述

（2）可以按时间线恢复到最新

cd /usr/local/pgsql/data/
vi postgresql.conf

修改如下内容：

restore_command = 'cp /usr/local/pgsql/data/arch/%f %p' 
recovery_target_timeline = 'latest'

修改recovery_target_action，如果不想进入备份模式，直接数据库启动就可以用，那么就使用promote。

recovery_target_action  #指定在达到恢复目标时服务器采取的动作。  
pause  #默认值，表示恢复将被暂停 
promote  #表示恢复结束且服务器将开始接受连接 
shutdown #表示在达到恢复目标之后停止服务器。

在这里插入图片描述

（3）按时间点恢复

cd /usr/local/pgsql/data/
vi postgresql.conf

修改如下内容：

restore_command = 'cp /usr/local/pgsql/data/arch/%f %p'  
recovery_target_time = '2025-06-03 15:56:16.007657+08'

修改recovery_target_action，如果不想进入备份模式，直接数据库启动就可以用，那么就使用promote。

recovery_target_action  #指定在达到恢复目标时服务器采取的动作。  
pause  #默认值，表示恢复将被暂停 
promote  #表示恢复结束且服务器将开始接受连接 
shutdown #表示在达到恢复目标之后停止服务器。

在这里插入图片描述

8、启动数据库

（1）创建文件

touch /usr/local/pgsql/data/recovery.signal
chown postgres:postgres /usr/local/pgsql/data/recovery.signal

在这里插入图片描述
否则启动数据库会报错。

cd /usr/local/pgsql/data
tail -100f serverlog

在这里插入图片描述

（2）启动数据库

service postgresql start
service postgresql status

在这里插入图片描述

9、查询数据

（1）查看数据库

查看有哪些数据库

\l+

在这里插入图片描述

（2）查看schema

查看当前库有哪些schema

\c mydb1
\dn+
\c mydb2
\dn+
\c mydb3
\dn+

在这里插入图片描述

（3）查看表

查看有哪些表

\c mydb1
\dt mydb1_pgtest1.*
\dt mydb1_pgtest2.*
\dt mydb1_pgtest3.*

\c mydb2
\dt mydb2_pgtest1.*
\dt mydb2_pgtest2.*
\dt mydb2_pgtest3.*

\c mydb3
\dt mydb3_pgtest1.*
\dt mydb3_pgtest2.*
\dt mydb3_pgtest3.*

在这里插入图片描述

（4）查看表中数据

\c mydb1
select * from mydb1_pgtest1.t1;
select * from mydb1_pgtest2.t2;
select * from mydb1_pgtest3.t3;

\c mydb2
select * from mydb2_pgtest1.t1;
select * from mydb2_pgtest2.t2;
select * from mydb2_pgtest3.t3;

\c mydb3
select * from mydb3_pgtest1.t1;
select * from mydb3_pgtest2.t2;
select * from mydb3_pgtest3.t3;