Debian PostgreSQL数据迁移方法有哪些

1. 使用pg_dump与pg_restore工具（逻辑备份/恢复）
这是PostgreSQL官方推荐的逻辑迁移方法，适用于大多数场景（如单数据库迁移、跨版本升级）。

备份源数据库：在源Debian服务器上，使用pg_dump命令生成自定义格式（推荐）的备份文件，包含数据库结构和数据。示例命令：pg_dump -U postgres -d source_db -F c -b -v -f /path/to/source_db.dump（-F c表示自定义格式，-b包含大对象，-v显示详细过程）。
传输备份文件：通过scp等工具将备份文件复制到目标Debian服务器，例如：scp /path/to/source_db.dump user@target_debian:/path/to/destination/。
恢复到目标数据库：在目标服务器上，使用pg_restore命令将备份文件恢复到指定数据库（需提前创建目标数据库）。示例命令：pg_restore -U postgres -d target_db -v /path/to/source_db.dump（-d指定目标数据库，-v显示恢复详情）。
该方法灵活，支持增量备份，但大数据量时恢复时间较长。

2. 使用pg_dumpall备份所有数据库（集群级迁移）
若需要迁移整个PostgreSQL集群（包括所有数据库、角色、表空间等），可使用pg_dumpall工具。

备份所有数据库：在源服务器上，以超级用户身份运行pg_dumpall命令，生成包含所有数据库信息的SQL文件。示例命令：sudo -u postgres pg_dumpall -f /path/to/all_databases.sql（-f指定输出文件）。
传输备份文件：将生成的SQL文件复制到目标服务器，例如：scp /path/to/all_databases.sql user@target_debian:/path/to/destination/。
恢复所有数据库：在目标服务器上，以超级用户身份运行psql命令恢复数据。示例命令：sudo -u postgres psql -f /path/to/all_databases.sql（直接执行SQL文件，无需指定数据库，因为文件中包含CREATE DATABASE语句）。
该方法适合需要完整迁移整个PostgreSQL环境的场景，但恢复时间随数据库规模增大而增加。

3. 物理复制（流复制/数据目录同步）
物理复制通过直接复制PostgreSQL数据目录实现，适用于大型数据库或需要实时同步的场景（如主从复制、零停机迁移）。

配置源服务器：修改源服务器的postgresql.conf（设置wal_level = replica、max_wal_senders = 10）和pg_hba.conf（允许复制用户连接），然后重启PostgreSQL服务：sudo systemctl restart postgresql。
创建复制槽：在源服务器上创建物理复制槽（可选但推荐），确保备库不会丢失WAL日志。示例命令：SELECT * FROM pg_create_physical_replication_slot('replication_slot');。
配置目标服务器：修改目标服务器的postgresql.conf（设置hot_standby = on），并配置primary_conninfo指向源服务器（如primary_conninfo = 'host=source_ip port=5432 user=replicator password=password'），然后重启PostgreSQL服务。
启动复制：在目标服务器上执行SELECT pg_create_restore_point('migration_point');和SELECT pg_start_backup('migration_backup', true);，通过rsync同步数据目录（rsync -avz --delete /var/lib/postgresql/<version>/main/ user@source_debian:/var/lib/postgresql/<version>/main/），最后执行SELECT pg_stop_backup();完成复制。
物理复制速度快，适合大数据量迁移，但需确保源服务器和目标服务器版本一致，且配置复杂。

4. 使用pgloader工具（跨数据库/格式迁移）
pgloader是一款开源数据加载工具，支持从MySQL、Oracle、CSV等多种数据源迁移到PostgreSQL，尤其适合异构数据库迁移。

安装pgloader：在Debian上通过包管理器安装：sudo apt install pgloader。

配置迁移文件：创建.load配置文件（如migration.load），定义源数据库和目标数据库的连接信息及迁移规则。示例内容：

LOAD DATABASE FROM mysql://user:password@source_mysql_ip/source_db INTO postgresql://user:password@target_postgres_ip/target_db WITH include drop, create tables, create indexes, reset sequences SET maintenance_work_mem to '128MB', work_mem to '12MB';

执行迁移：运行pgloader migration.load命令，pgloader会自动处理数据类型转换、表结构创建、数据迁移等步骤，并输出详细日志。
pgloader的优势在于支持多种数据源、自动化处理兼容性问题，但需提前编写配置文件。

5. 使用COPY命令迁移CSV/TSV数据
若数据以CSV或TSV格式存在（如从Excel导出、日志文件），可直接使用PostgreSQL的COPY命令迁移，适合结构化数据的快速导入。

准备数据文件：确保CSV/TSV文件的列顺序与目标表结构一致（第一行为表头时需指定HEADER选项）。
创建目标表：在目标数据库中创建与源数据结构相同的表。示例命令：CREATE TABLE employees (id SERIAL PRIMARY KEY, name VARCHAR(100), salary NUMERIC(10,2));。
导入数据：使用COPY命令将数据文件导入目标表。若文件在本地，使用：COPY employees(name, salary) FROM '/path/to/employees.csv' WITH CSV HEADER;；若文件在远程服务器，可通过psql的\copy命令（客户端工具）：\copy employees(name, salary) FROM '/path/to/employees.csv' WITH CSV HEADER;。
该方法简单高效，适合小批量结构化数据迁移，但不支持复杂数据类型（如BLOB）。

最新问答

相关标签