快速掌握 PostgreSQL 数据加载与备份

Table of Contents

• COPY 加载数据
• pg_bulkload 插件下载
• 安装 pg_bulkload
• COPY 与 pg_bulkload 加载数据对比
• pg_bulkload 使用控制文件加载数据
• pg_dump（备份） 与 pg_restore（恢复） 使用
• pg_dump 备份数据
• pg_restore 恢复数据
• 总结

COPY 加载数据

COPY 命令是PostgreSQL中最常用的加载数据方式，适合大数据量场景，能方便的在异构数据库之间进行数据交换。除了 COPY 命令外，还有一个是 \COPY 命令，这两个都是 PostgreSQL 内置的命令，如需要更高效的批量加载数据，可以使用 pg_bulkload 插件。

postgres=# \h COPY
Command:     COPY
Description: copy data between a file and a table
Syntax:
COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
    TO { 'filename' | PROGRAM 'command' | STDOUT }
    [ [ WITH ] ( option [, ...] ) ]

where option can be one of:

    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    DEFAULT 'default_string'
    HEADER [ boolean | MATCH ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

URL: https://www.postgresql.org/docs/16/sql-copy.html

下面将演示在服务端里使用 COPY 命令将数据从数据库中导出，清空表后再将导出的数据导入到数据库里，详细过程如下：

# 导出前查询数据
postgres=# select * from weather ;
 city | temp_low | temp_high | provin
------+----------+-----------+--------
 广州 |       17 |        28 | 广东
 上海 |       13 |        25 | 上海
 北京 |       10 |        22 | 北京
 南京 |       16 |        26 | 江苏
(4 rows)

# 执行导出语句，将数据导出到服务端的 /soft 目录下名为 weather.csv 文件
postgres=# COPY weather to '/soft/weather.csv' (DELIMITER ',',HEADER TRUE,ENCODING 'GBK');
COPY 4

# 清空表数据
postgres=# truncate table weather ;
TRUNCATE TABLE
postgres=# select * from weather ;
 city | temp_low | temp_high | provin
------+----------+-----------+--------
(0 rows)

# 根据导出的 /soft/weather.csv 数据文件数据重新导入到 weather 表里
postgres=# COPY weather from '/soft/weather.csv' (DELIMITER ',',HEADER TRUE,ENCODING 'GBK');
COPY 4
postgres=# select * from weather ;
 city | temp_low | temp_high | provin
------+----------+-----------+--------
 广州 |       17 |        28 | 广东
 上海 |       13 |        25 | 上海
 北京 |       10 |        22 | 北京
 南京 |       16 |        26 | 江苏
(4 rows)

使用建议：

• 关闭索引：大数据量加载时，建议先禁用索引或约束加载数据，完成后再启用，避免索引重建成本。
• 批量事务处理：将大文件拆分成小文件再循环分批进行 COPY 操作，这可以避免内存溢出。
• 临时表操作：如果数据不能直接使用，需要清洗与转换，则可以将数据先加载到临时表，再进行数据清洗、转换后插入正式表。

COPY 命令只是 PostgreSQL 基础的数据导入与导出命令，如果还需要更高效的加载数据，可使用 pg_bulkload 插件。

pg_bulkload 插件下载

pg_bulkload 的下载地址：pg_bulkload

本文使用的插件版本为：pg_bulkload-3.1.21（适用于 PostgreSQL 11, 12, 13, 14, 15, 16），pg_bulkload 的内部结构如下图：

pg_bulkload 的特点：

• 能将大量数据支持并行加载到数据库。
• 可以绕过PostgreSQL共享缓冲区将数据加载到表中。
• 具有一些ETL特性（输入数据验证和数据转换）。
• 不直接支持通过分区键自动加载到分区子表。

安装 pg_bulkload

前提已安装好 postgresql ，并服务能正常运行。如未安装 postgresql 数据库可参考以下文章：
PostgreSQL源码方式安装

上传安装包到相应目录后，解压 pg_bulkload-3.1.21.tar.gz 文件，再进入到解压后的根目录，执行 make、make install 进行编译安装，再检查安装是否成功，具体步骤如下：

[root@pc001 ~]# chown -R postgres:postgres /soft/pg_bulkload-3.1.21.tar.gz
[root@pc001 ~]# su - postgres
[postgres@pc001 ~]$ cd /soft
[postgres@pc001 soft]$ tar -zxvf pg_bulkload-3.1.21.tar.gz
[postgres@pc001 soft]$ cd pg_bulkload-3.1.21/
[postgres@pc001 pg_bulkload-3.1.21]$ make
[postgres@pc001 pg_bulkload-3.1.21]$ make install
[postgres@pc001 pg_bulkload-3.1.21]$ ldd /usr/postgres/16.1/lib/pg_bulkload.so
        linux-vdso.so.1 (0x00007ffd32148000)
        libpq.so.5 => /usr/postgres/16.1/lib/libpq.so.5 (0x00007ff6a0d67000)
        libc.so.6 => /lib64/libc.so.6 (0x00007ff6a0a00000)
        libm.so.6 => /lib64/libm.so.6 (0x00007ff6a0c82000)
        /lib64/ld-linux-x86-64.so.2 (0x00007ff6a0ddb000)

# 检查 pg_bulkload 版本
[postgres@pc001 pg_bulkload-3.1.21]$ pg_bulkload --version
pg_bulkload 3.1.21

postgres=# create extension pg_bulkload;
CREATE EXTENSION
Time: 418.763 ms
postgres=#

COPY 与 pg_bulkload 加载数据对比

成功创建 pg_bulkload 插件后，将分别使用 COPY 与 pg_bulkload 两种不同的数据加载工具，来比较两者加载数据的效率。先将数据库有中的 test_table 表数据导出成 csv 格式文件，以备后续进行数据加载。数据导出后，清空 test_table 表，再重新将 test_table.csv 的数据以 COPY 的方式加载到表中，用时为：5.959 秒。然后再次清空 test_table 表，使用 pg_bulkload 方式将相同的数据加载到 test_table 表中，并用日志文件 load_test_table.log 记录执行过程，通过查看 load_test_table.log 日志文件可以看出用时为：2.41 秒。从而可以看出 pg_bulkload 的加载速度比 COPY 快，数据量越大会越明显。

postgres=# COPY (select * from test_table) to '/soft/test_table.csv' (DELIMITER ',',HEADER FALSE,ENCODING 'GBK');
COPY 5000000

postgres=# truncate table test_table;
TRUNCATE TABLE
Time: 434.192 ms

postgres=# COPY test_table from '/soft/test_table.csv' (DELIMITER ',',HEADER FALSE,ENCODING 'GBK');
COPY 5000000
Time: 5958.823 ms (00:05.959)

postgres=# truncate table test_table;
TRUNCATE TABLE
Time: 434.192 ms

[postgres@pc001 pg_bulkload-3.1.21]$ pg_bulkload -d postgres -i /soft/test_table.csv -o table=test_table -l /soft/load_test_table.log
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        5000000 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.
[postgres@pc001 pg_bulkload-3.1.21]$ cat /soft/load_test_table.log
Run began on 2024-10-31 22:19:35.632282+08
Run ended on 2024-10-31 22:19:38.040908+08

CPU 0.31s/1.85u sec elapsed 2.41 sec
[postgres@pc001 pg_bulkload-3.1.21]$

pg_bulkload 加载返回说明：

pg_bulkload 使用控制文件加载数据

pg_bulkload 还可以使用控制文件的方式加载数据，这可以便于记录每次如何加载数据，如创建以下控制文件来加载数据到 test_table。详细加载信息可以查询 /soft/load_test_table_ctl.log 日志文件内容。控制文件中的更多参数设置可参考以下帮助文档：pg_bulkload

[postgres@pc001 ~]$ vi /soft/test_table.ctl
INPUT = /soft/test_table.csv
TABLE = test_table
LOGFILE = /soft/load_test_table_ctl.log

[postgres@pc001 ~]$ pg_bulkload /soft/test_table.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
        0 Rows skipped.
        5000000 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.

pg_dump（备份） 与 pg_restore（恢复） 使用

pg_dump 备份数据

pg_dump 是 PostgreSQL 中用来进行数据库备份的一个常用命令行工具，它可以将数据库的内容导出到文件中，以便后续进行恢复、迁移或备份。

pg_dump 有以下备份方式：

• 备份整个数据库的所有内容
• 备份指定表
• 只备份表结构
• 只备份表数据

pg_dump 详细的备份参数可以通过命令 pg_dump --help 查看，如需要备份整个集群，或备份集群中所有数据库公用的全局对象（如角色和表空间），则要使用 pg_dumpall。

💡注意：SQL 格式的备份文件不能用 pg_restore 恢复，需要用 psql 命令，SQL 脚本导出与导入使用如下：

# 导出命令：使用 pg_dump 工具，将表里的数据导出成 insert 插入语句。
[postgres@pc001 ~]$ pg_dump -U postgres -d postgres -t weather --data-only --column-inserts --file /soft/insert_weather.sql

# 导入命令：使用 psql ，将 sql 格式的插入语句批量插入到表中。
postgres=# \i /soft/insert_weather.sql

pg_restore 恢复数据

pg_restore 是 PostgreSQL 的一个用于恢复由 pg_dump 生成的自定义格式、tar 格式或目录格式的备份文件的工具，适用于大型数据库的高效恢复。

pg_restore 有以下恢复方式：

• 恢复整个数据库的所有内容
• 恢复指定的表
• 恢复指定的模式
• 恢复指定的对象（如 表结构、表数据）

pg_restore 详细的备份参数可以通过命令 pg_restore --help 查看。

总结

上述介绍的 COPY 、pg_bulkload、pg_dump、pg_restore 工具都是与数据导入或数据民出有关，但在实际过程中每个工具都有对应常用的场景，使用适合的工具能提高我们处理数据的效率。在使用 pg_dump（备份）与 pg_restore （恢复）大规模数据库时，可以使用目录格式+多线程备份与恢复，使用参数为： -F d 与-j ，其中 -F d 为 使用用目录格式，-j 指定并行度，同时可调节 maintenance_work_mem、work_mem 等参数，提升恢复效率。pg_dumpall 无法转储"大对象"，因为 pg_dump 无法将这样的对象存储到纯文本文件中。如果数据库里有这样大对象， 那么应该使用 pg_dump 的非文本输出格式之一来存储。