PostgreSQL数据迁移至Ubuntu的方法

将PostgreSQL数据库迁移至Ubuntu系统，需根据迁移场景（同版本/跨版本、全量/增量、本地/远程）选择合适工具与步骤。以下是常用且可靠的方法，覆盖逻辑备份恢复、物理目录迁移、热备低停机及CSV导入等场景：

一、准备工作

在开始迁移前，需完成以下基础配置：

安装PostgreSQL：在Ubuntu目标服务器上安装与源数据库版本一致的PostgreSQL（避免版本兼容性问题）。
使用官方仓库安装最新版本（以Ubuntu 22.04为例）：

sudo apt update
sudo apt install -y software-properties-common
sudo add-apt-repository 'deb http://apt.postgresql.org/pub/repos/apt/ jammy-pgdg main'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y postgresql-15 postgresql-contrib-15  # 替换为所需版本

创建目标数据库与用户：登录Ubuntu上的PostgreSQL（sudo -u postgres psql），创建与源数据库同名的数据库及对应用户：
```
CREATE USER your_username WITH PASSWORD 'your_password';
CREATE DATABASE your_database OWNER your_username;
\q  # 退出psql
```
备份源数据库：迁移前务必对源数据库进行完整备份，防止数据丢失。

二、使用pg_dump/pg_restore（逻辑备份恢复，推荐）

适用场景：跨版本迁移、需要选择性迁移部分数据库对象（如表、函数）、远程迁移。
步骤：

导出源数据库：在源服务器上使用pg_dump生成逻辑备份文件（支持全量或部分导出）：
- 全量导出（含结构与数据）：
```
pg_dump -U your_username -h source_host -p 5432 -F c -b -v -f your_database.dump your_database
```
  参数说明：-F c（自定义格式，支持压缩与并行恢复）、-b（包含大对象）、-v（详细输出）。
- 仅导出结构（无数据）：
```
pg_dump -U your_username -h source_host -p 5432 -F c -s -v -f your_database_structure.dump your_database
```
- 仅导出数据（无结构）：
```
pg_dump -U your_username -h source_host -p 5432 -F c -a -v -f your_database_data.dump your_database
```
传输备份文件至Ubuntu：使用scp或rsync将.dump文件复制到目标服务器：
```
scp your_database.dump user@ubuntu_server_ip:/path/to/destination/
```
恢复数据至Ubuntu：在Ubuntu目标服务器上，使用pg_restore将备份文件导入目标数据库：
```
pg_restore -U your_username -h localhost -d your_database -v your_database.dump
```
参数说明：-d（目标数据库名）、-v（详细输出）。若备份文件为压缩格式，pg_restore会自动解压。
验证数据完整性：登录Ubuntu上的PostgreSQL，检查表数据、索引、函数等是否与源数据库一致：
```
sudo -u postgres psql -d your_database -c "SELECT COUNT(*) FROM your_table;"
```

三、物理数据目录迁移（适用于同版本、本地迁移）

适用场景：源服务器与目标服务器均为Ubuntu，且PostgreSQL版本相同，需快速迁移大量数据（避免逻辑备份的CPU开销）。
步骤：

停止源与目标PostgreSQL服务：

# 源服务器
sudo systemctl stop postgresql
# 目标服务器
sudo systemctl stop postgresql

复制数据目录：使用rsync将源服务器的PostgreSQL数据目录（默认路径：/var/lib/postgresql//main）同步至目标服务器的相同路径：
```
rsync -avz -e ssh /var/lib/postgresql/15/main/ user@ubuntu_server_ip:/var/lib/postgresql/15/main/
```
参数说明：-a（保留权限与属性）、-v（详细输出）、-z（压缩传输）。
修改目标服务器数据目录权限：确保PostgreSQL用户（postgres）对新数据目录有所有权：
```
sudo chown -R postgres:postgres /var/lib/postgresql/15/main
```

启动目标PostgreSQL服务：

sudo systemctl start postgresql
sudo systemctl enable postgresql  # 设置开机自启

验证数据访问：登录目标服务器的PostgreSQL，检查数据库是否可正常访问：
```
sudo -u postgres psql -d your_database -c "SELECT version();"
```

四、热备低停机迁移（最小化停机时间）

适用场景：生产环境迁移，需将停机时间控制在几秒钟内。
步骤：

配置源服务器为热备节点：
- 编辑源服务器的postgresql.conf（路径：/etc/postgresql//main/postgresql.conf），开启WAL日志与热备：
```
wal_level = hot_standby
hot_standby = on
max_wal_senders = 3
max_replication_slots = 3
```
- 编辑pg_hba.conf（路径：/etc/postgresql//main/pg_hba.conf），允许目标服务器连接：
```
host replication replicator source_host_ip/32 scram-sha-256
```
- 重启源服务器PostgreSQL服务：
```
sudo systemctl restart postgresql
```
创建基础备份：在目标服务器上，使用pg_basebackup从源服务器获取基础数据：
```
sudo -u postgres pg_basebackup -h source_host -p 5432 -U replicator -D /var/lib/postgresql/15/main -Fp -Xs -P -R
```
参数说明：-Fp（普通文件格式）、-Xs（流式传输WAL日志）、-P（显示进度）、-R（自动生成recovery.conf）。
启动目标服务器：目标服务器会自动进入standby模式，同步源服务器数据：
```
sudo systemctl start postgresql
```
切换主从角色：当数据同步完成后，停止源服务器服务，将目标服务器提升为主服务器：
- 源服务器：
```
sudo systemctl stop postgresql
```
- 目标服务器：无需额外操作，pg_basebackup的-R参数已自动配置recovery.conf，目标服务器会自动切换为主服务器。
更新应用程序连接：将应用程序的数据库连接地址从源服务器改为目标服务器IP。

五、导入CSV/TSV文件（补充方法）

适用场景：仅需迁移部分表数据（如历史数据），或源数据以CSV格式存储。
步骤：

将CSV文件传输至Ubuntu：使用scp或rsync将CSV文件复制到目标服务器：
```
scp your_file.csv user@ubuntu_server_ip:/path/to/destination/
```

创建目标表：在Ubuntu的PostgreSQL中创建与CSV文件结构匹配的表：

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

导入CSV数据：使用COPY命令（需PostgreSQL用户有文件读取权限）或\copy命令（当前用户权限）：
- COPY命令（需sudo权限）：
```
sudo -u postgres psql -d your_database -c "COPY your_table(name, age) FROM '/path/to/your_file.csv' WITH CSV HEADER;"
```
- \copy命令（无需sudo）：
```
psql -U your_username -d your_database -c "\copy your_table(name, age) FROM '/path/to/your_file.csv' WITH CSV HEADER;"
```
参数说明：WITH CSV HEADER（指定CSV文件首行为标题行）。

六、常见问题排查

权限问题：若导入时报“permission denied”错误，需检查目标目录/文件的权限（确保postgres用户有所有权）。
版本不兼容：跨版本迁移时，建议先在测试环境验证，或使用pg_upgrade工具（PostgreSQL内置，用于同架构版本升级）。
配置文件差异：迁移后需检查目标服务器的postgresql.conf（如listen_addresses、max_connections）与源服务器是否一致，避免连接问题。

以上方法覆盖了PostgreSQL数据迁移至Ubuntu的常见场景，可根据实际需求选择合适的方式。迁移前务必做好备份，确保数据安全。