Ubuntu中PostgreSQL数据库迁移方法 - 问答

Ubuntu环境下PostgreSQL数据库迁移的常见方法

一、逻辑备份与恢复（适用于跨版本/跨平台迁移）

逻辑备份是最常用的迁移方式，通过pg_dump导出数据库结构和数据，再用pg_restore或psql导入目标服务器，支持跨PostgreSQL版本、跨操作系统迁移。

备份源数据库：使用pg_dump生成逻辑备份文件（支持自定义格式.dump或SQL格式.sql）。
- 备份整个数据库（自定义格式，支持压缩）：
```
pg_dump -U postgres -h 源服务器IP -Fc 数据库名 > 数据库名.dump
```
- 备份单个表（SQL格式，可直接查看）：
```
pg_dump -U postgres -h 源服务器IP -t 表名 数据库名 > 表名.sql
```
- 压缩备份（节省空间）：
```
pg_dump -U postgres -h 源服务器IP -Fc 数据库名 | gzip > 数据库名.dump.gz
```
传输备份文件到目标服务器：使用scp等工具将备份文件复制到目标Ubuntu服务器。
```
scp 数据库名.dump.gz 目标服务器IP:/home/用户名/
```

恢复到目标数据库：

解压备份文件（若为压缩格式）：
```
gunzip 数据库名.dump.gz
```
创建目标数据库（若不存在）：
```
createdb -U postgres 数据库名
```

恢复数据（自定义格式用pg_restore，SQL格式用psql）：

pg_restore -U postgres -d 数据库名 数据库名.dump  # 自定义格式
psql -U postgres -d 数据库名 -f 数据库名.sql    # SQL格式

验证数据完整性：登录目标数据库，检查表结构、数据记录是否与源数据库一致。
```
psql -U postgres -d 数据库名 -c "SELECT COUNT(*) FROM 表名;"
```

二、物理迁移数据目录（适用于同版本/同架构迁移）

若源服务器与目标服务器的PostgreSQL版本、操作系统架构一致（如均为Ubuntu 22.04 + PostgreSQL 14），可直接迁移数据目录（默认路径为/var/lib/postgresql/<版本>/main），步骤更快捷。

准备阶段：

确认源数据库状态正常：
```
sudo systemctl status postgresql
```

查看当前数据目录：

sudo -u postgres psql -c "SHOW data_directory;"

停止数据库服务：
```
sudo systemctl stop postgresql
```

迁移数据目录：

使用rsync保留文件权限（避免权限问题）：

sudo rsync -av /var/lib/postgresql/ 源数据目录路径/ /目标数据目录路径/

备份旧数据目录（可选）：

sudo mv /var/lib/postgresql /var/lib/postgresql.bak

修改配置文件：
编辑/etc/postgresql/<版本>/main/postgresql.conf，更新data_directory参数指向新路径：
```
data_directory = '/目标数据目录路径/postgresql/<版本>/main'
```

启动服务并验证：

sudo systemctl start postgresql
sudo -u postgres psql -c "SHOW data_directory;"  # 确认路径已变更

三、热备迁移（最小化停机时间，适用于生产环境）

若需实现几乎零停机的迁移（停机时间通常在几秒内），可使用PostgreSQL的热备功能（基于WAL日志同步），适合大型数据库或对可用性要求高的场景。

配置源服务器为热备模式：
- 编辑源服务器的postgresql.conf，开启WAL日志：
```
wal_level = hot_standby
max_wal_senders = 3
max_replication_slots = 3
```
- 编辑pg_hba.conf，允许目标服务器连接：
```
host replication replicator 源服务器IP/32 md5
```
- 重启源服务器使配置生效：
```
sudo systemctl restart postgresql
```
创建热备节点（目标服务器）：
- 在目标服务器上安装相同版本的PostgreSQL，初始化数据目录但不启动服务。
- 编辑目标服务器的postgresql.conf，设置为热备模式：
```
hot_standby = on
```
- 使用pg_basebackup从源服务器同步数据：
```
pg_basebackup -h 源服务器IP -U replicator -D /var/lib/postgresql/<版本>/main -P -R
```

切换主从角色：

当数据同步完成后，停止源服务器服务，提升目标服务器为主节点：

# 在源服务器上执行
pg_ctl stop -D /var/lib/postgresql/<版本>/main

# 在目标服务器上执行（提升为主节点）
pg_ctl promote -D /var/lib/postgresql/<版本>/main

验证迁移：
登录目标服务器，检查数据是否与源服务器一致，确认应用可正常连接。

四、使用pgloader迁移（适用于异构数据库迁移）

若需从MySQL、Oracle等其他数据库迁移到PostgreSQL，可使用pgloader工具，支持自动转换数据类型、处理约束和索引。

安装pgloader：

sudo apt update
sudo apt install pgloader

创建迁移配置文件（如migration.load）：

LOAD DATABASE
    FROM mysql://用户名:密码@源服务器IP/数据库名
    INTO postgresql://用户名:密码@目标服务器IP/数据库名
    WITH include no drop, create tables, create indexes, reset sequences

执行迁移：
```
pgloader migration.load
```
pgloader会自动处理表结构转换、数据同步及约束迁移。

迁移注意事项

备份优先：迁移前务必对源数据库进行完整备份，避免数据丢失。
版本兼容：跨版本迁移时，建议先升级源数据库到目标版本（或使用兼容的逻辑备份格式）。
权限检查：确保目标服务器的PostgreSQL用户具有足够的权限（如创建数据库、写入数据）。
停机时间评估：根据数据库大小选择合适的迁移方式（逻辑备份适合大型数据库，热备适合生产环境）。
数据验证：迁移后务必检查数据完整性（如记录数、约束有效性）。

0 赞

0 踩