pg_dump使用最佳实践

简介

pg_dump是PostgreSQL原生的备份工具。pg_dump生成的备份文件可以是一个SQL脚本文件，也可以是一个归档文件。详细信息请查看pg_dump官方说明。

• SQL脚本文件：纯文本格式的文件，其中包含将数据库重建到备份时状态的SQL命令。
• 归档格式的备份文件：必须与pg_restore一起使用来重建数据库，这种格式允许pg_restore选择恢复哪些数据。

注意事项

pg_dump适合单个库、schema级、表级导出，只会导出表及数据、函数等，数据库和用户需要提前在要恢复的库创建。

• --format=custom：备份为二进制格式，二进制格式的备份只能使用pg_restore来还原，并且可以指定还原的表。
• --format=plain：备份为文本，文本格式的备份还原，直接使用用户连接到对应的数据库执行备份文本即可。

限制条件

在使用pg_dump和pg_restore进行备份和恢复时，确保源数据库和目标库的版本一致，以避免出现兼容性问题，如果版本不一致可能会导致数据丢失或无法正确还原数据。

准备测试数据

# 创建数据库
create database dump_database;

# 登录dump_database数据库
\c dump_database

# 创建表1并插入数据
create table dump_table(id int primary key, content char(50));
insert into dump_table values(1,'aa');
insert into dump_table values(2,'bb');

# 创建表2并插入数据
create table dump_table2(id int primary key, content char(50));
insert into dump_table2 values(1,'aaaa');
insert into dump_table2 values(2,'bbbb');

使用pg_dump将数据库导出至SQL文件

语法

pg_dump ​​--username=<DB_USER> --host=<DB_IPADDRESS>​ --port=<DB_PORT> --format=plain --file=<BACKUP_FILE><DB_NAME>

• DB_USER为数据库用户。
• DB_IPADDRESS为数据库地址。
• DB_PORT为数据库端口。
• BACKUP_FILE为要导出的文件名称。
• DB_NAME为要导出的数据库名称。
• --format为导出的文件格式，plain为输出纯文本SQL脚本文件（默认）。其他选项详见pg_dump官方说明。

示例

• 导出数据库至SQL文件（INSERT语句）。

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --inserts --file=backup.sql dump_database
    Password for user root:

• 导出数据库中所有表结构至SQL文件。

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --schema-only --file=backup.sql dump_database
    Password for user root:

• 导出数据库中所有表数据至SQL文件。

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --data-only --file=backup.sql dump_database
    Password for user root:

命令执行完会生成“backup.sql”文件，如下：

[rds@localhost ~]$ ll backup.sql
-rw-r----- 1 rds rds 5657 May 24 09:21 backup.sql

使用pg_dump将数据库中的表导出至SQL文件

语法

pg_dump --username=<DB_USER> --host=<DB_ADDRESS> --port=<DB_PORT> --format=plain --file=<BACKUP_FILE> <DB_NAME> --table=<TABLE_NAME>

• DB_USER为数据库用户。
• DB_ADDRESS为数据库地址。
• DB_PORT为数据库端口。
• BACKUP_FILE为要导出的文件名称。
• DB_NAME为要迁移的数据库名称。
• TABLE_NAME为要迁移的数据库中指定表名称。
• --format为导出的文件格式，plain为输出纯文本SQL脚本文件（默认）。其他选项详见pg_dump官方说明。

示例

• 导出数据库中指定的单表至SQL文件。

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=dump_table
    Password for user root

• 导出数据库中指定的多表至SQL文件。

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=dump_table --table=dump_table2
    Password for user root:

• 导出数据库中以ts_开头的所有表至SQL文件。

    $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database --table=ts_*
    Password for user root:

• 导出数据库中除ts_开头之外的所有表至SQL文件。

   $ pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --file=backup.sql dump_database -T=ts_*
    Password for user root:

命令执行完会生成“backup.sql”文件，如下：

[rds@localhost ~]$ ll backup.sql
-rw-r----- 1 rds rds 5657 May 24 09:21 backup.sql

使用pg_dump导出特定schema下的数据

语法

pg_dump ​​--username=<DB_USER> --host=<DB_IPADDRESS>​ --port=<DB_PORT> --format=plain --schema=<SCHEMA> <DB_NAME> --table=<BACKUP_FILE>

• DB_USER为数据库用户。
• DB_IPADDRESS为数据库地址。
• DB_PORT为数据库端口。
• BACKUP_FILE为要导出的文件名称。
• DB_NAME为要导出的数据库名称。
• SCHEMA为要导出的schema名称。
• --format为导出的文件格式，plain为输出纯文本SQL脚本文件（默认）。其他选项详见pg_dump官方说明。

示例

• 导出指定库中public schema的所有数据。

  pg_dump --username=root --host=192.168.61.143 --port=5432 --format=plain --schema=public  dump_database  > backup.sql

• 导出指定库中除public schema以外的所有数据，结果以自定义压缩格式导出。

  pg_dump --username=root --host=192.168.61.143 --port=5432 --format=custom -b -v -N public dump_database > all_sch_except_pub.backup

还原数据

使用纯文本SQL脚本文件导出的数据，还原时直接使用psql命令即可，比如：

# 还原特定数据库
psql --username=root --host=192.168.61.143 --port=5432  backup_database  < backup.sql

# 还原特定表
psql --username=root --host=192.168.61.143 --port=5432  backup_database --table=dump_table  < backup.sql

# 还原特定schema
psql --username=root --host=192.168.61.143 --port=5432  backup_database --schema=public  < backup.sql

在恢复之前在目标库中创建数据库backup_database。

使用其他格式导出的数据进行还原时，需要使用pg_restore。pg_restore用于恢复由pg_dump转储的任何非纯文本格式中的PostgreSQL数据库。

pg_restore --username=root --host=192.168.61.143 --port=5432 --dbname=backup_database --format=custom all_sch_except_pub.backup --verbose

常见问题

1. 如果在使用pg_dump导出时，报错用户权限不足。

   解决方法：

   检查是否使用的root用户导出，如果不是root用户则会报权限不足；如果使用root用户导出，仍然提示没有权限，请检查数据库版本，root用户执行pg_dump命令需要内核版本为支持root提权的版本，支持root提权版本情况见root用户权限说明。

2. 将备份的文件导入到RDS for PostgreSQL目标数据库时发现control_extension等几个函数报错。

   解决方法：

   由于目标库中自带这些函数，因此该报错可以忽略。