PostgreSQL 同版本迁移

迁移方法

基于备份恢复的迁移

pg_dump + pg_restore 迁移

bash

# 全量备份源数据库
pg_dump -h source_host -p source_port -U username -d dbname -F c -b -v -f backup.dump

# 恢复到目标数据库
pg_restore -h target_host -p target_port -U username -d dbname -v backup.dump

pg_dumpall + psql 迁移

bash

# 备份所有数据库
pg_dumpall -h source_host -p source_port -U username -v > all_databases.sql

# 恢复到目标服务器
psql -h target_host -p target_port -U username -v -f all_databases.sql

WAL 归档迁移

bash

# 源服务器配置WAL归档
echo "wal_level = archive" >> postgresql.conf
echo "archive_mode = on" >> postgresql.conf
echo "archive_command = 'cp %p /path/to/archive/%f'" >> postgresql.conf

# 进行基础备份
pg_basebackup -h source_host -p source_port -D /path/to/backup -U replication -v -P

# 复制备份到目标服务器并恢复
rsync -av /path/to/backup/ target_host:/path/to/data/

基于文件系统的迁移

直接文件复制

bash

# 停止源数据库
pg_ctl -D /path/to/source/data stop

# 复制数据目录到目标服务器
rsync -av /path/to/source/data/ target_host:/path/to/target/data/

# 启动目标数据库
pg_ctl -D /path/to/target/data start

文件系统快照迁移

bash

# 创建源数据库文件系统快照
lvcreate -L 100G -s -n pg_snap /dev/vg/pg_data

# 挂载快照
mkdir /mnt/pg_snap
mount /dev/vg/pg_snap /mnt/pg_snap

# 复制数据
rsync -av /mnt/pg_snap/ target_host:/path/to/target/data/

# 卸载并删除快照
umount /mnt/pg_snap
lvremove -f /dev/vg/pg_snap

基于流复制的迁移

主从复制迁移

bash

# 源服务器配置流复制
echo "max_wal_senders = 5" >> postgresql.conf
echo "wal_keep_size = 1GB" >> postgresql.conf

# 添加复制用户
CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 5 ENCRYPTED PASSWORD 'password';

# 配置pg_hba.conf
echo "host replication replication target_host/32 md5" >> pg_hba.conf

# 重启源数据库
pg_ctl reload

# 目标服务器初始化并开始复制
pg_basebackup -h source_host -p source_port -D /path/to/target/data -U replication -v -P -R
pg_ctl -D /path/to/target/data start

迁移准备

环境检查

版本一致性验证

bash

# 检查源数据库版本
psql -h source_host -p source_port -U username -c "SELECT version();"

# 检查目标数据库版本
psql -h target_host -p target_port -U username -c "SELECT version();"

硬件资源评估

bash

# 检查磁盘空间
df -h

# 检查内存和CPU
free -h
lscpu

网络连通性测试

bash

# 测试TCP连接
telnet target_host 5432

# 测试网络带宽
iperf3 -c target_host

数据准备

数据清理

sql

-- 删除过期数据
DELETE FROM log_table WHERE created_at < CURRENT_DATE - INTERVAL '30 days';

-- 清理死元组
VACUUM ANALYZE VERBOSE;

-- 重建索引
REINDEX DATABASE dbname;

权限准备

sql

-- 创建迁移用户
CREATE USER migration_user SUPERUSER LOGIN ENCRYPTED PASSWORD 'password';

-- 配置pg_hba.conf
echo "host all migration_user 0.0.0.0/0 md5" >> pg_hba.conf

迁移执行

迁移步骤

预迁移检查

bash

# 运行pg_upgrade检查（同版本也可使用）
pg_upgrade --old-datadir=/path/to/source/data --new-datadir=/path/to/target/data \
           --old-bindir=/path/to/source/bin --new-bindir=/path/to/target/bin \
           --check

执行迁移
- 根据选择的迁移方法执行具体操作
- 监控迁移进度
- 记录迁移日志

迁移验证

sql

-- 验证数据库对象完整性
SELECT schemaname, relname, relkind FROM pg_class 
WHERE relkind IN ('r', 'i', 'S', 'v', 'm', 'c', 'f', 'p')
AND schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schemaname, relname;

-- 验证数据完整性
SELECT COUNT(*) FROM important_table;

迁移后处理

服务切换

应用切换策略
- 蓝绿部署切换
- 滚动切换
- 双写切换

切换验证

bash

# 检查应用连接
psql -h new_host -p new_port -U app_user -d dbname -c "SELECT 1;"

# 监控应用性能
SELECT usename, application_name, client_addr, state, query 
FROM pg_stat_activity 
WHERE state <> 'idle';

优化配置

调整目标数据库配置

sql

-- 根据目标服务器硬件调整参数
ALTER SYSTEM SET shared_buffers = '8GB';
ALTER SYSTEM SET effective_cache_size = '24GB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET wal_buffers = '16MB';

重建统计信息

sql

-- 重建所有数据库统计信息
ANALYZE VERBOSE;

-- 仅重建特定表
ANALYZE VERBOSE important_table;

最佳实践

迁移策略选择

迁移方法	适用场景	优点	缺点
pg_dump/pg_restore	小到中型数据库	简单易用，跨平台	迁移时间长，锁表
文件系统复制	所有规模数据库	迁移速度快	需要停机时间
流复制	高可用迁移	几乎零停机	配置复杂
WAL归档	大型数据库	增量迁移	需要预配置

性能优化

并行迁移

bash

# 使用并行备份
pg_dump -j 4 -h source_host -p source_port -U username -d dbname -F d -b -v -f /path/to/backup/

# 使用并行恢复
pg_restore -j 4 -h target_host -p target_port -U username -d dbname -v /path/to/backup/

压缩传输

bash

# 压缩备份
pg_dump -h source_host -p source_port -U username -d dbname -F c -Z 5 -b -v -f backup.dump

# 压缩传输
rsync -avz /path/to/backup/ target_host:/path/to/data/

增量迁移

bash

# 先进行基础备份
pg_basebackup -h source_host -p source_port -D /path/to/backup -U replication -v -P

# 迁移基础备份
rsync -av /path/to/backup/ target_host:/path/to/data/

# 应用WAL增量
pg_receivewal -h source_host -p source_port -U replication -D /path/to/wal -v

常见问题（FAQ）

Q1：同版本迁移需要停机吗？

A1：这取决于迁移方法：

文件系统复制：需要停机
pg_dump/pg_restore：备份时会锁表，恢复时也需要锁表
流复制：几乎零停机，可实现平滑切换

Q2：迁移后权限问题如何处理？

A2：建议：

sql

-- 迁移后检查用户权限
SELECT usename, usesuper, passwd FROM pg_user;

-- 重新创建用户或修改密码
CREATE USER username WITH PASSWORD 'new_password';
ALTER USER username WITH PASSWORD 'new_password';

Q3：如何加快大型数据库的迁移速度？

A3：可以采取以下措施：

使用并行备份和恢复（-j参数）
启用压缩传输
使用文件系统快照或直接文件复制
采用增量迁移策略
调整源数据库的wal_level和checkpoint参数

Q4：迁移后索引失效怎么办？

A4：可以重建索引：

sql

-- 重建单个索引
REINDEX INDEX index_name;

-- 重建整个表的索引
REINDEX TABLE table_name;

-- 重建整个数据库
REINDEX DATABASE dbname;

Q5：如何验证迁移数据的一致性？

A5：可以使用以下方法：

sql

-- 比较源和目标数据库的表行数
-- 在源数据库执行
SELECT relname, n_live_tup FROM pg_stat_user_tables ORDER BY relname;

-- 在目标数据库执行同样的查询并对比结果

-- 对关键表进行哈希验证
SELECT md5(CAST((SELECT * FROM important_table ORDER BY id) AS text));

PostgreSQL 同版本迁移 ​

迁移方法 ​

基于备份恢复的迁移 ​

基于文件系统的迁移 ​

基于流复制的迁移 ​

迁移准备 ​

环境检查 ​

数据准备 ​

迁移执行 ​

迁移步骤 ​

迁移后处理 ​

服务切换 ​

优化配置 ​

最佳实践 ​

迁移策略选择 ​

性能优化 ​

常见问题（FAQ） ​

Q1：同版本迁移需要停机吗？ ​

Q2：迁移后权限问题如何处理？ ​

Q3：如何加快大型数据库的迁移速度？ ​

Q4：迁移后索引失效怎么办？ ​

Q5：如何验证迁移数据的一致性？ ​

PostgreSQL 同版本迁移

迁移方法

基于备份恢复的迁移

基于文件系统的迁移

基于流复制的迁移

迁移准备

环境检查

数据准备

迁移执行

迁移步骤

迁移后处理

服务切换

优化配置

最佳实践

迁移策略选择

性能优化

常见问题（FAQ）

Q1：同版本迁移需要停机吗？

Q2：迁移后权限问题如何处理？

Q3：如何加快大型数据库的迁移速度？

Q4：迁移后索引失效怎么办？

Q5：如何验证迁移数据的一致性？