Debian环境下PostgreSQL数据库迁移实用技巧

一、逻辑备份与恢复（适用于中小型数据库）

逻辑备份是最常用的迁移方式，通过pg_dump（备份）和pg_restore（恢复）工具实现，支持自定义格式（适合大文件）和大对象（如BLOB）迁移。

1. 备份源数据库

使用pg_dump命令生成自定义格式（-Fc）的备份文件，包含大对象（-b）并开启详细模式（-v）以便跟踪进度：

pg_dump -U postgres -d source_db -Fc -b -v -f /path/to/backup.dump

参数说明：

• -U postgres：指定PostgreSQL超级用户（需具备源数据库读取权限）；
• -d source_db：源数据库名称；
• -Fc：自定义格式（支持压缩和并行恢复）；
• -b：包含大对象（如图片、文件等）；
• -v：显示详细操作日志。

2. 传输备份文件到目标服务器

使用scp（安全拷贝）将备份文件传输至目标Debian服务器（替换user、target_server和路径）：

scp /path/to/backup.dump user@target_server:/opt/postgresql/backup/

3. 恢复到目标数据库

在目标服务器上，先创建目标数据库（若不存在）：

createdb -U postgres target_db

再使用pg_restore恢复数据（-d指定目标数据库，-v显示恢复进度）：

pg_restore -U postgres -d target_db -v /opt/postgresql/backup/backup.dump

4. 验证数据完整性

登录目标数据库，执行简单查询确认数据是否迁移成功：

psql -U postgres -d target_db -c "SELECT COUNT(*) FROM key_table;"

二、物理复制（适用于大型数据库或实时同步）

物理复制通过复制源数据库的数据目录实现，速度快且支持实时同步（流复制），适合TB级数据或需要零停机时间的场景。

1. 准备源服务器

（1）修改配置文件

编辑postgresql.conf（通常位于/etc/postgresql/<version>/main/），开启归档和流复制：

wal_level = replica                # 日志级别设为replica（支持流复制）
max_wal_senders = 10               # 允许的最大WAL发送进程数
wal_keep_segments = 64             # 保留的WAL段文件数量（避免被清理）
archive_mode = on                  # 开启归档模式
archive_command = 'cp %p /var/lib/postgresql/<version>/main/archive/%f'  # 归档命令（可选）

编辑pg_hba.conf，允许目标服务器连接：

host    replication     replicator     target_server_ip/32    md5

重启PostgreSQL服务使配置生效：

sudo systemctl restart postgresql

（2）创建复制用户

在源数据库中创建专用于复制的用户（需具备REPLICATION权限）：

CREATE USER replicator WITH REPLICATION PASSWORD 'StrongPassword123!' LOGIN;

2. 准备目标服务器

（1）安装PostgreSQL并配置

在目标服务器上安装与源服务器相同版本的PostgreSQL（避免版本冲突）：

sudo apt update && sudo apt install postgresql postgresql-contrib

修改postgresql.conf，开启热备模式：

hot_standby = on                   # 允许目标服务器作为热备

编辑pg_hba.conf，允许源服务器连接（可选，用于流复制）：

host    all             all             source_server_ip/32    md5

重启PostgreSQL服务：

sudo systemctl restart postgresql

（2）启动复制

在目标服务器上执行SQL命令，启动流复制：

-- 创建恢复点（可选，用于标记恢复位置）
SELECT pg_create_restore_point('migration_start');

-- 开始备份（源服务器执行）
-- 在源服务器上执行：
SELECT pg_start_backup('full_backup', true);

-- 将源数据库数据目录复制到目标服务器（使用rsync同步增量数据）
rsync -avz --delete /var/lib/postgresql/<version>/main/ user@target_server:/var/lib/postgresql/<version>/main/

-- 停止备份（源服务器执行）
SELECT pg_stop_backup();

在目标服务器的postgresql.conf中指定恢复配置（或通过recovery.conf文件，PostgreSQL 12+已整合到postgresql.conf）：

primary_conninfo = 'host=source_server_ip port=5432 user=replicator password=StrongPassword123!'
restore_command = 'cp /var/lib/postgresql/<version>/main/archive/%f %p'  # 若开启归档，需配置
standby_mode = on

重启目标服务器的PostgreSQL服务，开始同步：

sudo systemctl restart postgresql

（3）验证复制状态

在目标服务器上查询pg_stat_replication视图，确认复制进程是否正常：

SELECT * FROM pg_stat_replication;

若返回结果包含state = 'streaming'，说明复制成功。

三、使用pgloader工具（适用于异构数据库迁移）

pgloader是开源的异构数据库迁移工具，支持从MySQL、Oracle、SQLite等迁移到PostgreSQL，自动处理数据类型转换和表结构迁移。

1. 安装pgloader

在Debian上通过APT安装：

sudo apt update && sudo apt install pgloader

2. 配置迁移任务

创建配置文件（如mysql_to_pg.load），定义源数据库和目标数据库的连接信息及迁移选项：

LOAD DATABASE
    FROM mysql://root:password@source_mysql_host/source_db
    INTO postgresql://postgres:StrongPassword123@target_pg_host/target_db
    WITH include drop, create tables, create indexes, reset sequences, foreign keys
    SET maintenance_work_mem to '128MB', work_mem to '16MB', search_path to 'public'
    CAST type datetime to timestamptz drop default drop not null using zerodatestonull,
         type date to timestamptz drop not null drop default using zerodatestonull;

参数说明：

• include drop：删除目标数据库中已存在的表（避免冲突）；
• reset sequences：重置序列值（如自增ID）；
• CAST：处理数据类型差异（如MySQL的datetime转PostgreSQL的timestamptz）。

3. 执行迁移

运行pgloader命令启动迁移（替换配置文件路径）：

pgloader mysql_to_pg.load

迁移过程中会显示进度条和日志，完成后会生成报告（如/tmp/pgloader/mysql_to_pg.load.log）。

四、迁移注意事项

1. 版本兼容性

确保源服务器和目标服务器的PostgreSQL版本兼容（建议版本号差异不超过2，如源为12.x，目标可为12.x/13.x/14.x），避免因版本差异导致功能失效。

2. 权限准备

• 源数据库：备份用户需具备SELECT权限（逻辑备份）或REPLICATION权限（物理复制）；
• 目标数据库：恢复用户需具备CREATEDB（创建数据库）、SUPERUSER（超级用户，用于物理复制）或对应表的INSERT权限。

3. 数据一致性

• 逻辑备份：迁移前停止对源数据库的写入（或使用pg_dump的--snapshot选项保证一致性）；
• 物理复制：迁移前停止源数据库的写入（pg_stop_backup()），确保数据完全同步。

4. 大文件处理

• 逻辑备份：使用gzip压缩备份文件减少传输时间（pg_dump ... | gzip > backup.dump.gz），恢复时解压（gunzip < backup.dump.gz | pg_restore ...）；
• 物理复制：使用rsync增量同步数据目录，减少网络传输量。

5. 验证与测试

• 迁移后执行SELECT COUNT(*)、SELECT * FROM table LIMIT 10等查询，确认数据量一致；
• 测试应用连接目标数据库，验证功能是否正常（如插入、更新、删除操作）。

通过以上技巧，可在Debian环境下高效完成PostgreSQL数据库迁移，根据数据规模和需求选择合适的方法（逻辑备份适合中小型数据库，物理复制适合大型数据库，pgloader适合异构迁移）。