Debian系统中PostgreSQL数据迁移的常用方法

一、使用pg_dump与pg_restore工具（逻辑备份/恢复）

适用场景：跨版本/跨平台迁移、部分数据库迁移、需要灵活选择迁移内容的场景。
这是PostgreSQL官方推荐的逻辑备份工具，通过生成自定义格式的备份文件，支持压缩、并行恢复等特性。

1. 备份源数据库

在源服务器上执行以下命令，生成自定义格式（-Fc）的备份文件（包含大对象-b，详细模式-v）：

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

-U postgres：指定源数据库管理员账号（根据实际情况替换）；
-d source_db_name：源数据库名称；
-f /path/to/backup/source_db.dump：备份文件输出路径。

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

使用scp等工具将备份文件复制到目标Debian服务器：

scp /path/to/backup/source_db.dump user@target_server:/path/to/target/backup/

3. 恢复到目标数据库

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

sudo -u postgres createdb -U postgres target_db_name

再使用pg_restore恢复数据（详细模式-v）：

sudo -u postgres pg_restore -U postgres -d target_db_name -v /path/to/target/backup/source_db.dump

4. 验证迁移

登录目标数据库，执行简单查询确认数据完整性：

sudo -u postgres psql -d target_db_name -c "SELECT COUNT(*) FROM key_table;"

二、使用pgloader工具（异构数据库迁移）

适用场景：从MySQL、Oracle等其他数据库迁移到PostgreSQL，或需要自动处理数据类型转换的场景。
pgloader是开源工具，支持自动映射不同数据库的数据类型，简化迁移流程。

1. 安装pgloader

在Debian上通过APT安装：

sudo apt update && sudo apt install pgloader

2. 配置迁移文件

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

LOAD DATABASE
    FROM mysql://mysql_user:mysql_password@source_host/source_db
    INTO postgresql://postgres:postgres_password@target_host/target_db
    WITH include drop, create tables, create indexes, reset sequences
    SET maintenance_work_mem to '128MB', work_mem to '16MB';

include drop：迁移前删除目标数据库中的同名对象（谨慎使用）；
reset sequences：重置序列值以匹配源数据。

3. 执行迁移

运行pgloader命令，加载配置文件：

pgloader mysql_to_pg.load

工具会自动处理数据类型转换、索引创建等操作，并输出详细的迁移日志。

三、物理复制（流复制/备份目录复制）

适用场景：超大型数据库迁移（TB级）、需要最小化停机时间的场景。
物理复制通过直接复制数据库数据目录实现，速度快于逻辑备份，但要求源与目标服务器的PostgreSQL版本完全一致。

1. 配置源服务器（流复制模式）

编辑源服务器的postgresql.conf文件（路径：/etc/postgresql/<version>/main/postgresql.conf）：

wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

重启PostgreSQL服务使配置生效：

sudo systemctl restart postgresql

2. 创建复制用户

在源数据库中创建用于复制的用户，并授予权限：

CREATE USER replicator WITH REPLICATION PASSWORD 'strong_password' LOGIN;
GRANT ALL PRIVILEGES ON DATABASE source_db_name TO replicator;

3. 配置目标服务器（接收复制）

编辑目标服务器的postgresql.conf文件，启用热备：

hot_standby = on

重启PostgreSQL服务：

sudo systemctl restart postgresql

4. 执行物理备份与恢复

方式一：使用pg_basebackup（推荐）

在目标服务器上执行pg_basebackup，从源服务器拉取数据目录：

sudo -u postgres pg_basebackup -h source_host -U replicator -D /var/lib/postgresql/<version>/main -P -R

-h source_host：源服务器IP或主机名；
-D：目标数据目录路径；
-R：自动生成recovery.conf文件（PostgreSQL 12+版本无需此参数，改用postgresql.auto.conf）。

方式二：手动复制数据目录（需停机）

停止源服务器PostgreSQL服务：
```
sudo systemctl stop postgresql
```

复制数据目录到目标服务器：

scp -r /var/lib/postgresql/<version>/main/ user@target_server:/var/lib/postgresql/<version>/main/

启动目标服务器PostgreSQL服务：
```
sudo systemctl start postgresql
```

四、使用COPY命令迁移CSV/TSV数据

适用场景：迁移结构化文本数据（如CSV文件）、小批量数据导入的场景。
若数据已存在于CSV或TSV文件中，可直接使用PostgreSQL的COPY命令导入。

1. 准备数据文件

确保CSV文件格式正确（如包含表头，字段分隔符为逗号）：

id,name,age
1,John Doe,30
2,Jane Smith,25

2. 创建目标表

在目标数据库中创建与CSV结构匹配的表：

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INTEGER
);

3. 导入数据

使用psql命令的\copy（本地文件，无需超级用户权限）或COPY（服务器端文件，需超级用户权限）导入：

-- 本地文件导入（推荐）
\copy employees(name, age) FROM '/path/to/employees.csv' WITH CSV HEADER;

-- 服务器端文件导入（需sudo -u postgres权限）
COPY employees(name, age) FROM '/var/lib/postgresql/employees.csv' WITH CSV HEADER;

迁移注意事项

版本兼容性：确保源与目标服务器的PostgreSQL版本相同或兼容（如12.x→13.x，避免跨大版本直接迁移）；
备份优先：迁移前务必备份源数据库（pg_dump/pg_basebackup），防止数据丢失；
权限检查：目标数据库用户需具备足够的权限（如创建表、插入数据、访问目录）；
停机时间：大型数据库迁移建议选择业务低峰期，必要时停机以确保数据一致性；
数据验证：迁移后务必核对数据量（SELECT COUNT(*)）、关键字段值（如主键、外键），确保无遗漏或错误。

Debian中PostgreSQL数据迁移方法