PG数据库迁移操作指南

1. 前期检查

在进行数据库迁移前，要对新老集群进行全面检查，确保无异常，防止迁移过程中出现问题。

• 检查节点状态：查看新老集群各个节点的运行状态，保证所有节点正常工作。
• 检查数据库日志：仔细查看数据库日志，确认无报错信息或异常事件。
• 检查高可用日志：检查高可用日志，确保高可用机制正常运行，无故障或切换事件。

2. 导出数据库角色和表结构

在迁移数据前，需导出数据库的角色和表结构，以便在新数据库中重新创建。

pg_dumpall  --roles-only > roles.sql
pg_dump -d database -s -f db_schema.sql

上述命令会把数据库角色导出到 roles.sql 文件，将数据库表结构导出到 db_schema.sql 文件。

3. 确认高可用配置文件中的密码

导入数据库角色后，可能会修改原有用户的密码。所以，要确认高可用配置文件中的超级用户和流复制用户密码与新导入的数据库角色密码一致。

• 打开高可用配置文件，找到超级用户和流复制用户的密码配置项。
• 对比配置文件中的密码与新导入的数据库角色密码，保证一致。

4. 迁移数据

依据表数据量大小，可选择不同的迁移方式。

4.1 方式 1：表数据量较小

若表数据量较小，可按以下步骤迁移数据：

-- 创建发布
CREATE PUBLICATION test_pub for tables in schema public;

-- 创建订阅
CREATE SUBSCRIPTION test_sub
CONNECTION 'host=IP地址（发布端） port=端口号 user=用户名（超级用户） dbname=数据库名 password=密码'
PUBLICATION test_pub;

上述命令会创建一个发布 test_pub，并把 public 模式下的所有表添加到发布中。接着创建一个订阅 test_sub，连接到发布端，并订阅 test_pub 发布。

4.2 方式 2：表数据量较大

若表数据量较大，可分批添加表进行数据迁移：

-- 创建新的发布
CREATE PUBLICATION test_pub;

-- 创建订阅
CREATE SUBSCRIPTION test_sub
CONNECTION 'host=IP地址（发布端） port=端口号 user=用户名（超级用户） dbname=数据库名 password=密码'
PUBLICATION test_pub;

-- 给发布添加表
ALTER PUBLICATION test_pub ADD TABLE 
    table1,
    table2,
    table3;

-- 刷新订阅
ALTER SUBSCRIPTION test_sub REFRESH PUBLICATION;

-- 验证发布状态
SELECT * FROM pg_publication;
-- 验证订阅状态
SELECT * FROM pg_subscription;
-- 查看复制槽状态
SELECT * FROM pg_replication_slots;

上述命令会创建一个新的发布 test_pub，并创建一个订阅 test_sub。然后将指定的表添加到发布中，并刷新订阅。最后，通过查询系统表验证发布、订阅和复制槽的状态。

5. 后期检查

数据迁移完成后，要进行最后的检查，确保迁移过程无异常。

• 查看逻辑复制状态：通过查询系统表或监控工具，查看逻辑复制的状态，确保数据正常复制。
• 查看数据库日志：查看订阅端和发布端的数据库日志，确认无报错信息或异常事件。

6. 回退操作

若迁移过程中出现问题，需进行回退操作：

6.1 停止订阅

ALTER SUBSCRIPTION test_sub DISABLE;

6.2 解除订阅端与复制槽的关系

将复制槽的 slot_type 设置为 logical 且 active 为 false 时，可通过修改 pg_replication_slots 系统表中的 restart_lsn 为 '0/0' 并把 confirmed_flush_lsn 也置为 '0/0' 来解除订阅端与复制槽的关系，不过更推荐使用以下命令：

ALTER SUBSCRIPTION test_sub SET (slot_name = NONE);

6.3 删除订阅

DROP SUBSCRIPTION test_sub;

6.4 删除发布

DROP PUBLICATION test_pub;

6.5 删除订阅端的数据库

DROP DATABASE 数据库名;