PostgreSQL导入导出工具之pg_dump

IT那活儿发布于2023-01-11 13:19 / 2357人阅读

点击上方“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那活儿”公众号

云服务器 GPU云服务器 mysql数据库导入导出工具导出导入导入导出导入?导出

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129397.html

Postgresql 备份与恢复

摘要：指定要用于查找的口令文件的名称。前四个字段可以是确定的字面值，也可以使用通配符匹配所有。利用环境变量引用的文件权限也要满足这个要求，否则同样会被忽略。在上，该文件被假定存储在一个安全的目录中，因此不会进行特别的权限检查。 pg_dump pg_dump 把一个数据库转储为纯文本文件或者是其它格式. 用法: pg_dump [选项]... [数据库名字] 一般选项: -f, --fi...

阿罗 2019-07-25 12:34 评论0 收藏0
用 PostgreSQL 的 COPY 导入导出 CSV

摘要：无意中看到了一篇讲数据批量导入的文章，才注意到的命令。的直接可以干这个事情，而且导出速度是非常快的。总结还有一些其他配置，比如把输入输出源指定成和命令，或者指定的等等。合理使用能大大提高效率。 TL;DR 无意中看到了一篇讲数据批量导入的文章，才注意到 PostgreSQL 的 COPY 命令。简而言之，它用来在文件和数据库之间复制数据，效率非常高，并且支持 CSV 。导出 CS...

ethernet 2019-07-25 12:34 评论0 收藏0
用 PostgreSQL 的 COPY 导入导出 CSV

摘要：无意中看到了一篇讲数据批量导入的文章，才注意到的命令。的直接可以干这个事情，而且导出速度是非常快的。总结还有一些其他配置，比如把输入输出源指定成和命令，或者指定的等等。合理使用能大大提高效率。 TL;DR 无意中看到了一篇讲数据批量导入的文章，才注意到 PostgreSQL 的 COPY 命令。简而言之，它用来在文件和数据库之间复制数据，效率非常高，并且支持 CSV 。导出 CS...

FleyX 2019-06-25 18:24 评论0 收藏0
Rails 3 升级 Rails 4 中遇到的问题及解决方法

摘要：的问题当出现不能的时候，我也尝试过这种提示里面的命令，可是还是不能解决这个问题。解决方法一般自己机器上面的都是测试数据，所以可以直接删除掉旧的数据库文件。解决方法然后就可以该干嘛干嘛了。有些出现的问题其实是不懂正确的流程，都是在试错，可是还是学到了很多东西，写下了，希望对我和大家都有帮助。 Homebrew 的问题当我去运行brew update的时候出现错误untracked...

史占广 2019-07-25 12:31 评论0 收藏0
Oracle SQL Developer 个人使用记录

摘要：打开多个窗口一个数据库连接打开多个窗口用于查看数据表及数据进入工具首选项数据库对象查看器勾选自动冻结对象查看器窗口即可。显示行号进入工具首选项代码编辑器行装订线勾选显示行数即可。 Oracle SQL Developer 个人使用记录以下简称 SQL Developer 对我Java开发来说，这个工具已经足够使用了，虽然还有很多缺点，但够用就行，相对于我来说的优点：整体UI还算舒...

leanxi 2019-06-25 18:59 评论0 收藏0