PostgreSQL如何进行跨服务器迁移数据

1、方案

在针对PostgreSQL的数据迁移这一块功能，官方有相应的工具支持：pg_dump和pg_restore，通过这两个工具，可以很好的对数据库中的数据进行备份和恢复。那既然支持通过包来备份和恢复，那在跨服务器数据迁移，区别也仅仅在于PostgreSQL服务器地址存在差异，需要增加步骤来在两台服务器之间进行备份数据包的传输即可，基于此，下面来说明如何来操作这一系列过程。

2、准备工作

梳理本地环境以及目标环境相应参数表如下：

配置项	源数据库（本地）	目标数据库（远程）
服务器IP	localhost(或127.0.0.1)	10.17.100.245
数据库名称	sonarqube_db	sonarqube_db（可同名或不同名）
Postgresql连接用户	sonar	sonar
Postgresql端口	5432（默认端口）	5432（默认端口）
远程服务器系统用户	本地用户	sonar（用于SSH/SCP传输）

上层服务应用：Sonarqube

操作流程与实际的应用服务理论上无关，这里说明上层应用仅方便后续验证实际数据恢复后的效果用。

3、操作流程

3.1 导出原始数据库数据

登录原始数据库所在的Linux服务器，以数据库管理员身份登录到PostgreSQL：

sudo -u postgres psql

使用pg_dump命令导出数据库中的数据:

# 从本地localhost 5432端口以sonar身份登录postgresql将sonarqube_db数据库导出到文件source_db_dump.custom
pg_dump -h localhost -p 5432 -U sonar -F c -v -f source_db_dump.custom sonarqube_db

执行后，会要求输入sonar账户的口令密码，输入密码后开始正常导出数据：

导出完成后，数据文件source_db_dump.custom在当前命令执行的目录下生成：

3.2 传输数据文件

在完成原始数据库的数据导出之后，使用scp工具将其远程传输到需要恢复数据的目标服务器上：

# 将以sonar用户身份登录到10.17.100.245服务器并传输source_db_dump.custom到/home/sonar路径
scp source_db_dump.custom sonar@10.17.100.245:/home/sonar/

输入目标服务器的连接账户密码后开始传输：

传输完成后，开始准备下一步导入数据。

3.3 目标数据库导入数据

3.3.1 导入前目标数据库准备

在开始导入数据前，需提前对目标服务器中的数据库做一些配置：

1）创建目标数据库

• 登录远程服务器，连接到远程 PostgreSQL 数据库：

# 可能需要输入 postgres 用户的密码
sudo -u postgres psql

• 创建目标数据库（已有则忽略此步骤）

# 创建sonar账户下的sonarqube_db数据库
CREATE DATABASE sonarqube_db OWNER sonar;
# 检查是否创建成功
\l
# 切换到sonarqube_db数据库
\c sonarqube_db
# 完成创建后，退出数据库到终端
\q

2）扩展检查（可选）

当源数据库使用了扩展时，必须在导入数据前提前安装完成所有的插件，否则导入时会出现异常：

• 检查数据库使用的扩展

sudo -u postgres psql登录到源PostgreSQL数据库，执行下列语句检查：

# 检查数据库使用的插件
\dx

• 安装必要插件

当检查出有插件在使用时，需针对每一个插件进行安装：

-- 示例：安装postgis插件
CREATE EXTENSION IF NOT EXISTS "postgis";

每个扩展使用CREATE EXTENSION进行安装。

3.3.2 数据导入

完成导入前的准备操作后，在目标服务器终端开始导入数据：

• 数据文件路径在：/home/sonar
• 使用pg_restore导入数据：

# 目标服务器停止sonar服务：停止上层应用服务
/home/sonar/etc/sonarqube/sonarqube-10.4.0.87286/bin/linux-x86-64/sonar.sh stop
# 移动到数据文件目录
cd /home/sonar
# 终端执行将source_db_dump.custom数据文件导入到数据库sonarqube_db
pg_restore -h localhost -p 5432 -U sonar -v -d sonarqube_db --clean source_db_dump.custom

这里我选择使用--clean参数以覆盖的形式替代原有的所有同名数据，无需覆盖可不加此参数，根据需求使用：

注意： 覆盖现有的数据后，目标服务器中已有的数据会被清掉，应用服务中的账户内容也会替换为源数据库中的账户，如果账户同名，后续使用时注意密码会更新。

3.4 数据验证

# 连接到目标数据库
sudo -u postgres psql
# 切换至sonarqube_db数据库
\c sonarqube_db
# 列出所有数据库
\l

所有的数据库结果如下：

# 列出所有表，检查数量是否与源数据库大致相同。q退出
\dt

记录数检查结果如下，可以看到数据记录是前后一致的：

# 检查所有序列是否存在
\ds

选取projects表作为数据对比表，检查记录：

SELECT COUNT(*) FROM projects;

上述记录可以看到数据已经同步正常，所有的项目均已导入。也可以对数据进行抽查检查数据内容是否有错误：

SELECT * FROM projects LIMIT 10;

Tips： 在导入数据时没有要求数据库要停止服务，但避免可能在导入时有其他用户或应用存在数据更新而导致数据异常，最好还是先停止数据库服务再进行数据导入。

3.5 更新数据库统计信息

当数据存在更新时，需要对数据库统计信息进行更新：

# 切换到postgresql管理员用户
sudo -i -u postgres
psql -d sonarqube_db
# 更新统计信息
ANALYZE VERBOSE;

当出现以下报错，为非管理员账户进行的更新操作导致，切换管理员身份重新操作即可：

如果数据库存在名称变更，上层使用此数据库的应用一定要更新指向新的数据库。至此，所有数据完成迁移。

4、 应用服务数据更新

4.1 重建ElasticSearch索引

由于我这边上层使用PostgreSQL数据的应用为Sonarqube，其中包含ES服务，对于数据库存在更新时，为了保障数据使用正常，需要对ES进行索引重建，操作步骤如下：

• 1、停止SonarQube服务器/集群

• 2、进入SonarQube的安装目录/etc/sonarqube/sonarqube-10.4.0.87286/data

/etc/sonarqube/sonarqube-10.4.0.87286为本地Sonar安装路径，根据实际路径执行

• 3、删除/etc/sonarqube/sonarqube-10.4.0.87286/data/data/es8 目录下的所有内容 （需要注意在较旧的ES版本中，此目录可能为 es6 或 es7）

• 4、重新启动SonarQube服务器/集群。

具体原因是，es8目录包含Elasticsearch的索引，当此目录不存在时，迫使SonarQube识别到Elasticsearch索引的缺失，在服务启动时会触发完整的索引重建过程，从而确保索引是基于新迁移的数据库构建的。

4.2 应用数据检查

重新启动服务，检查数据是否正常：可以看到所有的项目都已完全导入，部分细节访问，如issue板块，需要在索引重建完成后才能完整查看。

完成索引重建后，所有页面都可正常访问：

5、附录：pg_restore参数说明

pg_restore命令格式如下pg_restore [选项]... [文件名]，具体支持的参数如下，供使用时参考：

一般选项:-d, --dbname=名字        连接数据库名字-f, --file=文件名        输出文件名-F, --format=c|d|t       备份文件格式(应该自动进行)-l, --list               打印归档文件的 TOC 概述-v, --verbose            详细模式-V, --version            输出版本信息, 然后退出-?, --help               显示此帮助, 然后退出恢复控制选项:-a, --data-only              只恢复数据, 不包括模式-c, --clean                  在重新创建之前，先清除（删除）数据库对象-C, --create                 创建目标数据库-e, --exit-on-error          发生错误退出, 默认为继续-I, --index=NAME             恢复指定名称的索引-j, --jobs=NUM               执行多个并行任务进行恢复工作-L, --use-list=FILENAME      从这个文件中使用指定的内容表排序输出-n, --schema=NAME            在这个模式中只恢复对象-N, --exclude-schema=NAME    不恢复此模式中的对象-O, --no-owner               不恢复对象所属者-P, --function=NAME(args)    恢复指定名字的函数-s, --schema-only            只恢复模式, 不包括数据-S, --superuser=NAME         使用指定的超级用户来禁用触发器-t, --table=NAME             恢复命名关系（表、视图等）-T, --trigger=NAME           恢复指定名字的触发器-x, --no-privileges          跳过处理权限的恢复 (grant/revoke)-1, --single-transaction     作为单个事务恢复--disable-triggers           在只恢复数据的过程中禁用触发器--enable-row-security        启用行安全性--if-exists                  当删除对象时使用IF EXISTS--no-comments                不恢复注释--no-data-for-failed-tables  对那些无法创建的表不进行数据恢复--no-publications            不恢复发行--no-security-labels         不恢复安全标签信息--no-subscriptions           不恢复订阅--no-tablespaces             不恢复表空间的分配信息--section=SECTION            恢复命名节 (数据前、数据及数据后)--strict-names               要求每个表和(或)schema包括模式以匹配至少一个实体--use-set-session-authorization使用 SESSION AUTHORIZATION 命令代替ALTER OWNER 命令来设置所有权联接选项:-h, --host=主机名        数据库服务器的主机名或套接字目录-p, --port=端口号        数据库服务器的端口号-U, --username=名字      以指定的数据库用户联接-w, --no-password        永远不提示输入口令-W, --password           强制口令提示 (自动)--role=ROLENAME          在恢复前执行SET ROLE操作选项 -I, -n, -N, -P, -t, -T, 以及 --section 可以组合使用和指定