PostgreSQL 逻辑备份详解

前言

当谈到关系型数据库的备份和恢复时，逻辑备份是一种备份方法，它备份数据库的逻辑结构和数据，而不是直接备份磁盘上的物理数据。这样做有许多好处，比如备份速度更快，可以选择性地备份数据库中的某些数据，以及更容易恢复数据库到另一个物理服务器上。在关系型数据库中，PostgreSQL 是一种备受欢迎的数据库，因为它提供了许多强大的功能，包括逻辑备份。在本文中，我们将重点讨论 PostgreSQL 的逻辑备份工具 pg_dump，以及如何使用它来备份和恢复您的数据库。

1. 基础语法

pg_dump 备份命令主要由 connection-option 和 option 两部分组成：

pg_dump [connection-option...] [option...] [dbname]

1.1 connection_option

连接相关的参数：

1.2 other_option

其它参数含义：

1.3 备份文件 format

pg_dump 有 --format=c|d|t|p 参数，可以指定备份文件格式：

• p（plain）默认格式, 备份输出为可读的 text 文本. 还原时在数据库中直接执行备份文本的 SQL 即可。

• c（custom）可自定义的归档格式，同时默认开启了数据压缩，还原时可以调整备份对象的还原顺序，同时支持选择还原的对象，还原时需要使用 pg_restore。

• d（directory）目录归档格式，与 custom 格式类似, 需要使用 pg_restore 还原，目录归档格式下会创建一个目录, 然后每个表或者每个大对象对应一个备份输出文件。

• t（tar）tar 归档格式, 不支持压缩，同时限制每个表最大不能超过 8GB, 同样需要使用 pg_restore 还原。

2. 常用模版

2.1 备份单个数据库

会输出建库语句，如果不想输出建库语句，可以删掉 --create 参数：

pg_dump -U {用户名} -p {端口} -h {ip地址} --create dbname={数据库名} -f 备份路径.sql

2.2 导出单表和数据

包含建表语句，没有建库语句，恢复记得指定库名：

pg_dump -U {用户} -p {端口} -h {ip地址} dbname={数据库名} -n {schame} -t {表名} -f 备份路径.sql

2.3 仅导出单表数据

仅导出数据，不导表结构：

pg_dump -U {用户} -p {端口} -h {ip地址} --data-only dbname={数据库名} -n {schame} -t {表名} -f 备份路径.sql

2.4 某库下表结构导出

仅导出 db 的创建语句，常用于结构迁移：

pg_dump -U {用户} -p {端口} -h {ip地址} --schema-only dbname={数据库名} -f 备份路径.sql

3. 逻辑备份恢复

备份命令中如果指定 format 备份模式参数（plain 模式除外）该模式需要使用 pg_restore 来恢复。如果 SQL 脚本文件可以使用 psql 来恢复数据。

3.1 SQL 脚本恢复

熟悉 MySQL 的朋友应该都知道，MySQL 中恢复 SQL 文本，可以使用 mysql < 和 source 命令，PostgreSQL 也提供了对应命令：

# 备份 db01 数据库：
pg_dump -U postgres -p 5432 -h 127.0.0.1 dbname=db1 -f ./db1.sql

-- 先创建库
create database db1;
-- 进入该库
\c db1
-- 执行外部 SQL 文件
\i /data/pgsql12/backup/db1.sql

PostgreSQL 中的 \i 等同于 MySQL 中的 source 命令。

除此之外，还有一种方法和 mysql < 类似，使用数据库自带的客户端执行 SQL 文件：

--  -f, --file=FILENAME execute commands from file, then exit
psql -U postgres -p 5433 -h 127.0.0.1 -d db1 -f /backup/db1.sql

3.2 pg_restore 语法

文中 1.3 小节提到过 pg_dump 备份有四种格式，除了 plain 格式外，其它格式都需要使用 pg_restore 恢复备份数据。

pg_restore 恢复命令同样由 connection-option 和 option 两部分组成：

pg_restore [connection_option] [option] [filename]

3.3 pg_restore 示例

使用 format=c 的模式备份 db1 数据库：

pg_dump -U postgres -p 5432 -h 127.0.0.1 dbname=db1 --format=c -f db1_bak.sql

将备份文件恢复至 db2 数据库，先创建 db2 数据库：

# 先创建 db2
postgres=$ create database db2;
CREATE DATABASE

再使用 pg_restore 导入数据：

pg_restore -U postgres -p 5432 -h 127.0.0.1 -d db2 ./c_bak.sql

确认是否恢复：

db1=# \c db2
You are now connected to database "db2" as user "postgres".
db2=# \d
              List of relations
 Schema |       Name       | Type  |  Owner   
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres
(4 rows)

db2=# select count(*) from pgbench_accounts;
  count  
---------
 5000000
(1 row)

恢复单表数据，不包含结构：

pg_restore -U postgres -p 5432 -h 127.0.0.1 -d db1 -n public -t pgbench_accounts ./tb_bak.sql