PostgreSQL Migration

一、迁移前准备

1.1 备份源数据

pg_dumpall -U postgres -f /backup/postgresql-source-$(date +%Y%m%d).sql

1.2 检查源数据库状态

psql -U postgres -c 'SELECT datname FROM pg_database;'

1.3 准备目标环境

• 安装 PostgreSQL（参考 PostgreSQL 安装指南）
• 配置网络连接
• 创建必要的用户和权限

二、迁移方法

2.1 使用 pg_dump/pg_restore

2.1.1 导出数据

pg_dump -U postgres -d testdb -F custom -f /backup/testdb.dump

2.1.2 导入数据

pg_restore -U postgres -d testdb /backup/testdb.dump

2.2 使用 pg_dumpall

2.2.1 导出所有数据库

pg_dumpall -U postgres -f /backup/all-databases.sql

2.2.2 导入所有数据库

psql -U postgres -f /backup/all-databases.sql

2.3 使用 pg_basebackup

2.3.1 创建基础备份

pg_basebackup -h source-host -U replicator -D /var/lib/pgsql/14/data -P -X stream

2.3.2 启动目标服务器

sudo systemctl start postgresql-14

2.4 使用逻辑复制

2.4.1 在源数据库创建发布

psql -U postgres -d testdb -c 'CREATE PUBLICATION mypub FOR ALL TABLES;'

2.4.2 在目标数据库创建订阅

psql -U postgres -d testdb -c "CREATE SUBSCRIPTION mysub CONNECTION 'host=source-host port=5432 dbname=testdb user=postgres password=yourpassword' PUBLICATION mypub;"

三、迁移后验证

3.1 检查数据完整性

psql -U postgres -d testdb -c 'SELECT COUNT(*) FROM users;'

3.2 检查索引

psql -U postgres -d testdb -c '\d users'

3.3 检查用户权限

psql -U postgres -c '\du'

3.4 性能测试

psql -U postgres -d testdb -c 'EXPLAIN ANALYZE SELECT * FROM users WHERE age > 30;'

四、常见问题处理

4.1 迁移速度慢

• 增加网络带宽
• 使用并行备份：pg_dump -j 4
• 分批迁移大表

4.2 数据不一致

• 检查源数据和目标数据的计数
• 使用校验工具验证数据
• 重新执行迁移操作

4.3 索引丢失

• 在迁移前导出索引信息：\d users
• 在迁移后重新创建索引

4.4 权限问题

• 确保目标环境有相同的用户和权限
• 使用 CREATE USER 创建用户
• 使用 GRANT 授予权限

五、最佳实践

• 在非业务高峰期进行迁移
• 先在测试环境进行迁移测试
• 保留源数据库一段时间，以防回滚
• 使用监控工具监控迁移过程
• 对大表进行分批迁移