十二、 PostgreSQL 逻辑备份与恢复

1. 什么情况需要通过备份恢复

  1. 用户误操作
  2. 存储介质损坏

 

2. 逻辑备份和恢复

备份

pg_dump

  • pg_dump 只能备份单个数据库，不会导出角色和表空间相关的信息，而且恢复的时候需要创建空数据库。
  • pg_dump 是一个普通的 PostgreSQL 客户端应用，可在任何可以访问该数据库的远端主机上进行备份；
  • 可以选择一个数据库或部分表进行备份，恢复过程可以跨平台迁移；
  • 可以在数据库正在使用时进行完整一致的备份，并不阻塞其它用户对数据库的访问；
  • 备份一个数据库会涉及权限，几乎总是需要一个超级用户。
# 二进制格式备份
pg_dump -F c -f mydb.dump -C -E UTF8 -h 127.0.0.1 -U postgres mydb

# 文本格式备份，比较大的库建议这种备份方式，-F p 可以不加，为默认值
pg_dump -F p -f mydb.dump -C -E UTF8 -h 127.0.0.1 -U postgres mydb

# 压缩备份
pg_dump -C -E UTF8 -h 127.0.0.1 -U postgres mydb | gzip > mydb.sql.gz
# 解压恢复
gunzip -c mydb.sql.gz | psql

# 备份指定表
pg_dump -t test_auto_vacuum -f mydb_test_auto_vacuum.dump -C -E UTF8 -h 127.0.0.1 -U postgres mydb

# 备份时排除表
pg_dump -T test_auto_vacuum -f mydb_test_auto_vacuum.dump -C -E UTF8 -h 127.0.0.1 -U postgres mydb

# pg_dump仅导出数据库结构（元数据）
pg_dump -s -f mydb.sql -E UTF8 -h 127.0.0.1 -U postgres mydb

# pg_dump仅导出表数据
pg_dump -a -f mydb.sql -C -E UTF8 -h 127.0.0.1 -U postgres mydb

# 备份test开头的表
pg_dump -F p -a -f mydb.sql -t *.test* -E UTF8 -h 127.0.0.1 -U postgres mydb

# 备份 public schema 里面的表
pg_dump -F p -a -C -f mydb.sql -n public -E UTF8 -h 127.0.0.1 -U postgres mydb

# 备份排除 public schema 里面的表
pg_dump -F p -a -f mydb.sql -C -N public -E UTF8 -h 127.0.0.1 -U postgres mydb

 

pg_restore

二进制格式的备份只能使用 pg_restore 来还原, 可以指定还原的表, 编辑 TOC 文件,定制还原的顺序, 表, 索引等。

 

# psql 恢复
psql -v  -d postgres -f mydb.sql

# pg_restore 恢复

## -d 表示先登录postgres数据库，然后执行-C 执行dump文件里面的create database mydb语句
pg_restore -v -C -d postgres mydb.dump 

## 将二进制文件生成控制文件，可以将控制文件里面的内容用 ; 注释，选择性恢复
pg_restore -l mydb.dump > toc.data
pg_restore -v -C -L toc.data -d postgres mydb.dump

 

大表备份和恢复

# 并行导出，生成文件夹testdb.p_dmp，里面有控制文件和多个压缩文件
pg_dump -Fd -f testdb.p_dmp -j8 -C -E UTF8 -h 127.0.0.1 -U postgres mydb 

# 并行导入
pg_restore -v -C -j 8 -d postgres testdb.p_dmp/

 

pg_dumpall

优点

  • 它转储全局的东西——角色和表空间，这些不能被 pg_dump 转储。
  • 单个命令，你可以获得整个集群的结果。
  • 常用来备份全局对象而非全库数据。

缺点

  • 转储很大，因为它未压缩。
  • 转储非常慢，因为它是顺序完成的，只有一个工作程序。
  • 仅恢复部分转储很难。
  • 生成 psql 脚本，pg_dumpall 只支持文本格式。
  • 它在内部调用 pg_dump。
# 导出
pg_dumpall -f mydb.dumpall -E UTF8 -h 127.0.0.1 -U postgres

# 导入，如果表中存在数据，仍然导入
psql -f mydb.dumpall

 

copy 导入导出

  1. copy 命令用于表与文件（和标准输出、标准输入）之间的相互拷贝；
  2. copy to 由表至文件，copy from 由文件至表；
  3. copy 命令始终是到数据库服务端找文件，以超级用户执行导入导出权限要求很高，适合数据库管理员操作；
  4. \copy 命令可在客户端执行导入客户端的数据文件，权限要求没那么高，适合开发人员、测试人员使用。
# test.txt 
postgres@k8s-master01:~/test$ cat test.txt 
1       a
2       b
3       c

# copy test.txt 文件到 test_1 表中
mydb=# copy test_1 from '/home/postgres/test/test.txt' ;
COPY 3

# test.txt 中的数据用 ',' 分隔
copy test_1 from '/home/postgres/test/test.txt' DELIMITER ',';

# 将 test_1 表中的数据复制到test_1.txt文件中
copy test_1 to '/home/postgres/test/test_1.txt';
COPY 3

 

pg_bulkload

pg_bulkload 是一个高性能的数据加载工具，专门为 PostgreSQL 数据库设计，用于大批量数据的快速导入。pg_bulkload 的工作原理是绕过传统的 SQL INSERT 语句，通过直接写入底层数据文件和 WAL 日志，显著提升了数据加载速度和效率。

安装

# github：https://github.com/ossc-db/pg_bulkload
make PG_CONFIG=/usr/local/postgresql/bin/pg_config
make install

mydb=# create extension pg_bulkload;
CREATE EXTENSION

使用

# 创建测试表
create table test_2 (like test);

# 生成测试文件
seq 10000000| awk '{print $0"|test"$0}' >> bulktest.txt

# 导入数据
pg_bulkload -i test/bulktest.txt -O test_2 -l test/bulktest.log -P test/bulktest-parse.log -o "TYPE=CSV" -o "DELIMITER=|" -d mydb 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        10000000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.
        
# 查看对应的日志
cat bulktest.log 

pg_bulkload 3.1.23 on 2026-09-07 05:21:15.55609+08

INPUT = /home/postgres/test/bulktest.txt
PARSE_BADFILE = /home/postgres/test/bulktest-parse.log
LOGFILE = /home/postgres/test/bulktest.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
NULL = 
OUTPUT = public.test_2
MULTI_PROCESS = NO
VERBOSE = NO
WRITER = DIRECT
DUPLICATE_BADFILE = /data/pgdata/data/pg_bulkload/20260907052115_mydb_public_test_2.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO


  0 Rows skipped.
  10000000 Rows successfully loaded.
  0 Rows not loaded due to parse errors.
  0 Rows not loaded due to duplicate errors.
  0 Rows replaced with new rows.

Run began on 2026-09-07 05:21:15.55609+08
Run ended on 2026-09-07 05:21:29.64718+08

CPU 0.46s/2.77u sec elapsed 14.09 sec

# 使用控制文件来加载数据，可以根据上面生成的控制文件，删除里面值为NULL的参数
vim test2.ctl

INPUT = /home/postgres/test/bulktest.txt
PARSE_BADFILE = /home/postgres/test/bulktest-parse.log
LOGFILE = /home/postgres/test/bulktest.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.test_2
MULTI_PROCESS = NO
VERBOSE = YES
WRITER = DIRECT
DUPLICATE_BADFILE = /data/pgdata/data/pg_bulkload/20260907052115_mydb_public_test_2.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO 

# 执行控制文件
pg_bulkload test.ctl -d mydb

 

关于写WAL日志 

pg_bulkload 默认是跳过 buffer 直接写文件，如果写的过程出现异常，需要基于 WAL 日志恢复时，没有 WAL 日志是不行的，这时我们可以强制让其写 WAL 日志，只需要加载 -o "WRITER=BUFFERED" 参数就可以了。

#查询当前lsn
select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 9/82979B28
(1 row)

# 直接用上一步的控制文件导入，WRITER=DIRECT
pg_bulkload test.ctl -d mydb 
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        10000000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.

# 在此查询lsn
select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 9/8297BD58
(1 row)

# 计算 lsn 的偏移量
select '9/8297BD58'::pg_lsn - '9/82979B28'::pg_lsn;
 ?column? 
----------
     8752  ## 产生的LSN偏移量
(1 row)


#查询当前lsn
select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 9/82980E18
(1 row)

# 将WRITER设置为BUFFERED
vim test2.ctl

INPUT = /home/postgres/test/bulktest.txt
PARSE_BADFILE = /home/postgres/test/bulktest-parse.log
LOGFILE = /home/postgres/test/bulktest.log
LIMIT = INFINITE
PARSE_ERRORS = 0
CHECK_CONSTRAINTS = NO
TYPE = CSV
SKIP = 0
DELIMITER = |
QUOTE = "\""
ESCAPE = "\""
OUTPUT = public.test_2
MULTI_PROCESS = NO
VERBOSE = YES
WRITER = BUFFERED
DUPLICATE_BADFILE = /data/pgdata/data/pg_bulkload/20260907052115_mydb_public_test_2.dup.csv
DUPLICATE_ERRORS = 0
ON_DUPLICATE_KEEP = NEW
TRUNCATE = NO 

# 使用控制文件导入
pg_bulkload test.ctl -d mydb
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        10000000 Rows successfully loaded.
        0 Rows not loaded due to parse errors.
        0 Rows not loaded due to duplicate errors.
        0 Rows replaced with new rows.
        
# 再次查询lsn
mydb=# select pg_current_wal_lsn();
 pg_current_wal_lsn 
--------------------
 9/ADA2BA90
(1 row)

# 计算lsn
select '9/ADA2BA90'::pg_lsn - '9/82980E18'::pg_lsn;
 ?column?  
-----------
 722119800  ## 产生的LSN偏移量
(1 row)

 

posted @ 2026-09-06 14:45  BinBin-HF  阅读(6)  评论(0)    收藏  举报