PostgreSQL16-数据备份与恢复-SQL转储

1、概述

• SQL转储方法的思想是创建一个由SQL命令组成的文件，当把这个文件给服务器时，服务器将利用其中的SQL命令重建与转储时状态一样的数据库
• 有pg_dump和pg_dumpall两个备份命令

2、pg_dump

创建测试数据

-- 创建数据库test_db1、test_db2
CREATE DATABASE test_db1;
CREATE DATABASE test_db2;

-- 验证数据库创建成功
\l

-- 切换至test_db1数据库
\c test_db1;

-- 创建user_info表
CREATE TABLE user_info (
    id SERIAL PRIMARY KEY,
    user_name VARCHAR(50) NOT NULL,
    age INT CHECK (age > 0),
    phone VARCHAR(11) UNIQUE,
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 向user_info插入5条测试数据
INSERT INTO user_info (user_name, age, phone) VALUES
('张三', 28, '13800138000'),
('李四', 32, '13900139000'),
('王五', 25, '13700137000'),
('赵六', 35, '13600136000'),
('孙七', 29, '13500135000');

-- 创建order_info表
CREATE TABLE order_info (
    order_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES user_info(id),  -- 关联user_info主键
    order_amount NUMERIC(10,2) NOT NULL,
    order_status VARCHAR(20) DEFAULT '已支付',
    pay_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 向order_info插入6条测试数据
INSERT INTO order_info (user_id, order_amount, order_status) VALUES
(1, 99.99, '已支付'),
(1, 199.50, '已发货'),
(2, 2999.00, '已支付'),
(3, 59.90, '待支付'),
(4, 899.99, '已收货'),
(5, 399.00, '已支付');

-- 切换到test_db2库
\c test_db2;

-- 创建dept_info表
CREATE TABLE dept_info (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE,
    dept_addr VARCHAR(100),
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 向dept_info插入4条测试数据
INSERT INTO dept_info (dept_name, dept_addr) VALUES
('技术部', '北京市海淀区科技园'),
('市场部', '上海市浦东新区金融城'),
('财务部', '广州市天河区CBD'),
('人事部', '深圳市南山区科技园');

-- 创建emp_info表
CREATE TABLE emp_info (
    emp_id SERIAL PRIMARY KEY,
    emp_name VARCHAR(50) NOT NULL,
    salary INT NOT NULL,
    dept_id INT NOT NULL REFERENCES dept_info(dept_id),  -- 关联dept_info主键
    last_update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_update_user VARCHAR(50) DEFAULT 'admin'
);

-- 向emp_info插入8条测试数据
INSERT INTO emp_info (emp_name, salary, dept_id, last_update_user) VALUES
('刘一', 12000, 1, 'admin'),
('陈二', 9500, 1, 'user01'),
('杨三', 8800, 2, 'admin'),
('黄四', 15000, 2, 'user02'),
('周五', 7500, 3, 'admin'),
('吴六', 8200, 3, 'user01'),
('郑七', 11000, 4, 'admin'),
('王八', 9000, 4, 'user02');

语法

# 推荐顺序：连接类(-U) → 数据库名(test_db1) → 对象筛选(-t) → 格式(-F) → 输出路径(-f)
pg_dump -U postgres test_db1 -t user_info -F p -f 路径

# 若有主机/端口，补充在连接类后：
pg_dump -U postgres -h 127.0.0.1 -p 5432 test_db1 -t user_info -F p -f 路径

自定义备份格式

pg_dump -Fc -h 192.168.8.19 -U 用户名 库名 > /路径/文件名.dump

• -F 自定义输出格式，后面可以跟 p c d t 来指定格式
  • p  输出一个纯文本SQL脚本文件(默认值)
  • c  输出适合于pg_restore输入的自定义格式归档文件。例如.dump格式
  • d 输出适合于pg_restore输入的目录格式归档文件
  • t  输出适合输入到pg_restore的tar格式存档

使用pg_dump备份单库/单表

-- 备份test_db1库
pg_dump -U postgres test_db1 -F p -f /tmp/test_db1_bak.sql

-- 备份test_db1库的user_info表
pg_dump -U postgres test_db1  -t user_info -F p -f /tmp/test_db1_user_bak.sql

使用pg_dump备份某个模式下的所有表

• 使用多个-n可以备份多个模式

# 压缩备份：public模式所有表，-Z指定压缩级别为6（范围1-9）
pg_dump -U postgres test_db1 -n public -F p -Z 6 -f /tmp/test_db1_public_schema_bak_compress.sql

# 远程备份：IP=192.168.1.100，端口=5432，备份public模式所有表
pg_dump -U postgres -h 192.168.1.100 -p 5432 test_db1 -n public -F p -f /tmp/test_db1_remote_public_bak.sql

# 仅排除public模式下的order_info，biz_schema模式下的order_info仍会被备份
pg_dump -U postgres test_db1 -n public -n biz_schema -T public.order_info -F p -f /tmp/only_exclude_public_order.sql

# 仅备份public模式的表结构，排除order_info表的结构
pg_dump -U postgres test_db1 -n public -T order_info -F p --schema-only -f /tmp/test_db1_public_schema_only_exclude.sql

# 仅备份public模式的表数据，排除order_info表的数据
pg_dump -U postgres test_db1 -n public -T order_info -F p --data-only -f /tmp/test_db1_public_data_only_exclude.sql

pg_dump和pg_dumpall的区别

• pg_dump用于备份单个表、schema或者database
• pg_dumpall用于导出所有数据库数据
• pg_dump可以将数据备份为SQL文本格式，也支持备份为自定义的压缩格式或TAR格式。压缩格式和TAR格式的备份文件可以实现并行恢复
• pg_dumpall仅可以将当前PG服务实例中所有database的数据导出为SQL文本,不支持其他格式导出，也可以同时导出表空间和角色的全局对象
• pg_dump一次只导出一个数据库， 并且它不会导出关于角色或表空间的信息 （因为这些是集群范围的，而不是每个数据库的

3、pg_dumpall

• 全量备份有几个数据库就要输入几次密码，修改pg_hba.conf。允许postgre用户本地登录所有数据库免密。即local all posgres trust
  

# 基础全量备份
pg_dumpall -U postgres -f /tmp/pg_all_bak_full.sql

# 全量备份并压缩为 .sql.gz 格式（压缩级别6，平衡速度和体积）
pg_dumpall -U postgres | gzip -6 > /tmp/pg_all_bak_full_compress.sql.gz

# 核心参数 -g（--globals-only）：仅备份全局对象（角色/表空间/权限）
pg_dumpall -U postgres -g -f /tmp/pg_all_bak_globals.sql

# 核心参数 -s（--schema-only）：仅备份结构，无数据
pg_dumpall -U postgres -s -f /tmp/pg_all_bak_schema_only.sql

# 核心参数 -a（--data-only）：仅备份数据，无结构
pg_dumpall -U postgres -a -f /tmp/pg_all_bak_data_only.sql

# 远程全量备份并压缩（生产推荐）
pg_dumpall -U postgres -h 192.168.1.100 -p 5432 | gzip -6 > /tmp/pg_remote_all_bak_compress.sql.gz

4、数据恢复

• PostgreSQL支持以下两种数据恢复方法：
• 1、使用psql恢复pg_dump或pg_dumpall工具生成的SQL文本格式的数据备份。
• 2、使用pg_restore工具来恢复由pg_dump工具生成的自定义压缩格式、TAR包格式或者目录格式备份。

查询占用test_db1的所有会话

• 删除test_db1库时提示“ERROR: database "test_db1" is being accessed by other users DETAIL: There is 1 other session using the database.”

SELECT
  pid AS 会话PID,
  usename AS 登录用户,
  client_addr AS 客户端IP,
  state AS 会话状态
FROM pg_stat_activity
WHERE datname = 'test_db1';

使用psql恢复数据

# 恢复单个库：test_db1（先确保库存在，若库被删除需先重建 CREATE DATABASE test_db1;）
psql -U postgres -d test_db1 -f /tmp/test_db1_bak.sql

# 恢复所有库（从pg_dumpall的备份恢复）
psql -U postgres -f /tmp/all_db_bak.sql postgres

# 恢复压缩的 test_db1 全库备份
zcat /tmp/test_db1_full_bak.sql.gz | psql -U postgres -d test_db1

# 恢复压缩的 pg_dumpall 全实例备份
zcat /tmp/pg_all_bak_full.sql.gz | psql -U postgres postgres

使用pg_restore恢复数据

pg_restore -h 192.168.9.20 -U postgres -C -d postgres -j 2  /pgbackup/mydb_back2019.dump

-j   --jobs=    执行恢复操作的进程数
-C   --create   恢复的库和SQL文件中的同名，则可省略省略建库步骤

使用pg_restore灵活备份

# 使用自定义格式备份，生成.dump备份文件
pg_dump -Fc test_db2 -f /tmp/test_db2_fc.dump

# 导出自定义格式备份文件 TOC 内容
pg_restore -l /tmp/test_db2_fc.dump > /tmp/test_db2_fc.list

# 查看TOC文件
cat /tmp/test_db2_fc.list

• TOC文件以;为注释
  第 1 列 TOC 唯一编号（pg_restore 可通过编号指定恢复 / 跳过对象）
  第 2 列 对象类型 OID（PG 内置固定值，标识对象类别）
  第 3 列 对象唯一 OID（PG 数据库内对象的唯一标识，无需关注）
  第 4 列 明文对象类型（直观标识对象是什么，核心关注）
  第 5 列 对象所属模式（此处均为默认 public 模式）
  第 6 列 对象名称（核心关注，对应库中实际对象名）
  第 7 列 对象所属用户（此处为超级用户 postgres，即创建对象的用户）