PostgreSQL导入导出工具之pg_dump

IT那活儿 2022-07-15

3718

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

pg_dump是什么

pg_dump是一个用于备份PostgreSQL数据库的实用工具。

即使当前数据库正在使用，也能够生成一致性的备份。

这也就意味着转储的内容是pg_dump开始运行时刻的数据库快照，且在pg_dump运行过程中发生的更新将不会被转储。pg_dump备份期间不会阻塞其他用户访问数据库(包括读、写)，但是会阻塞那些需要排它锁的操作，比如大部分形式的ALTER TABLE。

pg_dump只能备份一个数据库。

如果要备份Cluster中数据库共有的全局对象，例如角色和表空间，需要使用pg_dumpall。

转储可以被输出到脚本或归档文件格式。

转储的脚本是包含 SQL 命令的纯文本文件，它们可以用来重构数据库到它被转储时的状态。要从这样一个脚本恢复，直接使用psql执行即可。脚本文件还可以通过修改一部分内容在其他架构的数据库上重构数据库，其和mysqldump很类似。这也是相对于其他备份方法的一个重要优势。

注意：pg_dump产生的转储文件不包含优化器用来做出查询计划决定的统计信息。当导入完成后，建议做全库的ANALYZE，这对后续优化器能根据可用的统计信息对SQL执行进行最优化操作。

另一种可选的归档文件格式必须与pg_restore配合使用来重建数据库。

它们允许pg_restore能选择恢复什么，或者甚至在恢复之前对条目重排序。归档文件格式被设计为在架构之间可移植。当使用归档文件格式之一并与pg_restore组合时，pg_dump提供了一种灵活的归档和传输机制。

pg_dump可以被用来备份整个数据库，然后pg_restore可以被用来检查归档并/或选择数据库的哪些部分要被恢复。

最灵活的输出文件格式是“自定义”格式（-Fc）和“目录”格式（-Fd）。它们允许选择和重排序所有已归档项、支持并行恢复并且默认是压缩的。“目录”格式是唯一一种支持并行转储的格式。

如果源数据库的大小很大并且数据库服务器之间的连接速度很慢，则可以将源数据库转储到文件中，然后将文件复制到远程服务器上，然后将其还原。

二

以pgtt库为例进行讲解

2.1 将源数据库转储到文件中。在之前要查看一下数据库的大小，选择合适大小的目录存放文件。

--源端查看pgtt数据库大小：

\c pgtt
select pg_database_size('pgtt');

--根据库大小将pgtt全库导出到/app/pg目录下，-b包含LOB表：

su - postgres
pg_dump -U root -p 10001 -d pgtt -b -f app/pg/pgtt_data20211022.sql

2.2 将转储文件复制到目标端服务器上。(这里目标端接受目录必须要有其他属组用户写权限)

scp pgtt_data20211022.sql postgres@xxx.xxx.xxx.xxx:/app/pg

2.3 在目标端创建新的数据库，并将转储文件还原。

--创建用户和数据库：

CREATE USER pgtt WITH  ENCRYPTED PASSWORD 'xxxxxx';
CREATE DATABASE pgtt OWNER pgtt TEMPLATE template1;
REVOKE CONNECT ON DATABASE pgtt FROM PUBLIC;
GRANT CONNECT ON DATABASE pgtt TO pgtt;

--还原转储文件（如果该库有dblink，那么在还原之前要安装好oracle_fdw插件）：

su - postgres
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_data20211022.sql

如果服务器之间的连接速度很快并且数据库的大小不大，则可以使用以下命令：

pg_dump -C -h local -U localuser sourcedb | psql -h remote -U remoteuser targetdb

如：

pg_dump -C -h $local -U root pgtt | psql -h $remote -U root pgtt

以上方法是将一个库一次性全部导出导入，这样可能会报一些用户不存在的错误。

原因是在还原转储文件的时候会将一些表的权限赋给其他用户，而该文件中是没有该用户的定义语句的，并且目标端服务器也没有该用户，所以会报错。

其实办法很简单，先仅导角色定义无需备份表空间、再全导。如果已经报用户不存在的错误了，那么就先导角色定义，再导结构（不包含数据），最后导数据（仅导数据）。

--如果仅需备份角色定义而无需备份表空间，那么可以加上--roles-only选项：

pg_dumpall -h 10.10.xxx.xxx -U root --port=10001 -f /app/pg/onlyroles20211022.sql --roles-only

--只导结构，不导数据：

pg_dump -U root -p 10001 -d pgtt -s -b -f /app/pg/pgtt_jg_20211022.sql

--只导数据，没有创建用户角色：

pg_dump -U root -p 10001 -d pgtt -a -b -f /app/pg/pgtt_data20211022.sql

--传输到目标端：

scp onlyroles20211022.sql postgres@10.10.xxx.xxx:/app/pg
scp pgtt_jg_20211022.sql postgres@10.10.xxx.xxx:/app/pg
scp pgtt_data20211022.sql postgres@10.10.xxx.xxx:/app/pg

--目标端执行：

psql -U root -p 10001 -d pgtt -f /app/pg/onlyroles20211022.sql
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_jg_20211022.sql
psql -U root -p 10001 -d pgtt -f /app/pg/pgtt_data20211022.sql

本文作者：魏斌（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

PostgreSQL导入导出工具之pg_dump

本文作者：魏 斌（上海新炬王翦团队）

本文来源：“IT那活儿”公众号

评论

本文作者：魏斌（上海新炬王翦团队）