centos中pgadmin数据同步方法

CentOS中使用pgAdmin进行数据同步的主要方法

1. 基础备份与恢复（手动同步）

这是最常用的数据同步方式，适用于一次性或少量数据迁移场景。

• 步骤1：导出数据（备份）
  打开pgAdmin，连接到源PostgreSQL服务器，在左侧导航树中选择需要同步的数据库，右键点击→Export Data（或通过顶部菜单栏File→Export→To File…）。选择要导出的表（或整个数据库），设置导出格式（如SQL、CSV，SQL格式保留表结构和数据，适合完整迁移；CSV适合与外部工具兼容），配置文件路径（如/tmp/backup.sql），点击OK完成导出。
• 步骤2：传输备份文件
  使用scp、sftp等工具将导出的备份文件传输到目标服务器。例如：

  scp /tmp/backup.sql user@target_server_ip:/tmp/
  

• 步骤3：导入数据（恢复）
  连接到目标PostgreSQL服务器，在pgAdmin中选择目标数据库，右键点击→Import Data（或File→Import→From File…），选择传输过来的备份文件，确认导入设置（如字符集、表覆盖选项），点击Start完成导入。

2. 使用pg_cron定时同步

若需要定期自动同步数据（如每日增量同步），可通过pg_cron插件实现。

• 步骤1：安装pg_cron
  在CentOS上，通过yum安装对应PostgreSQL版本的pg_cron扩展（以PostgreSQL 12为例）：

  sudo yum install -y pg_cron_12
  

• 步骤2：配置pg_cron
  编辑PostgreSQL配置文件postgresql.conf，添加：

  shared_preload_libraries = 'pg_cron'
  cron.database_name = 'postgres'  # 指定存储pg_cron作业的数据库
  

  重启PostgreSQL使配置生效：

  sudo systemctl restart postgresql-12
  

  登录PostgreSQL，创建pg_cron扩展：

  CREATE EXTENSION pg_cron;
      
  

• 步骤3：创建定时作业
  通过pgAdmin或psql执行SQL语句，设置定时任务。例如，每天凌晨2点同步public.users表：

  SELECT cron.schedule('0 2 * * *', $$
    INSERT INTO target_db.public.users
    SELECT * FROM source_db.public.users
    ON CONFLICT (id) DO UPDATE SET  -- 冲突时更新（增量同步）
      name = EXCLUDED.name,
      email = EXCLUDED.email;
      
  $$);
      
  

  注：需提前配置源数据库和目标数据库的链接（如通过dblink扩展）。

3. 使用pgAgent定时任务

pgAgent是PostgreSQL的任务调度工具，适合复杂的同步流程（如多步骤SQL、脚本调用）。

• 步骤1：安装pgAgent
  通过pgAdmin 4集成安装：打开pgAdmin→Tools→pgAgent→Install pgAgent，按向导完成安装（需依赖pgagent包）。
• 步骤2：创建定时任务
  在pgAdmin左侧导航树中找到pgAgent Jobs节点，右键→Create→pgAgent Job。
  • General：设置任务名称（如“Daily Data Sync”）。
  • Steps：点击Add，选择“SQL”类型，输入同步SQL（如INSERT INTO target_table SELECT * FROM source_table WHERE created_at > NOW() - INTERVAL '1 day'; ）。
  • Schedules：点击Add，设置执行时间（如每天凌晨3点）。
    保存后，pgAgent会自动按计划执行任务。

4. 数据库复制（实时/增量同步）

若需要实时或增量同步数据（如主从服务器、特定表同步），可使用PostgreSQL原生复制功能。

• 流复制（物理复制）
  适用于主从服务器完全同步，配置步骤：
  1. 在源服务器（主库）修改postgresql.conf：

     wal_level = replica
     max_wal_senders = 2
     wal_keep_size = 64
     

  2. 在源服务器pg_hba.conf中添加从库IP访问权限：

     host    replication     replicator     从库IP/32     md5
     

  3. 创建复制用户并获取WAL位置：

     CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'your_password';
         
     SELECT pg_current_wal_lsn();
         
     

  4. 在从库（目标服务器）配置postgresql.conf：

     hot_standby = on
     

  5. 在从库recovery.conf（PostgreSQL 12+为postgresql.auto.conf）中添加：

     standby_mode = 'on'
     primary_conninfo = 'host=源服务器IP port=5432 user=replicator password=your_password'
     restore_command = 'cp /path/to/wal/%f %p'
     

  6. 启动从库PostgreSQL，通过pgAdmin验证复制状态（Servers→目标服务器→Replication→Status）。
• 逻辑复制（逻辑解码）
  适用于特定表或增量数据同步，配置步骤：
  1. 在源服务器安装wal2json扩展：

     CREATE EXTENSION wal2json;
         
     

  2. 创建发布（Publication）：

     CREATE PUBLICATION my_pub FOR TABLE users, orders;
           -- 指定需要同步的表
     

  3. 在目标服务器创建订阅（Subscription）：

     CREATE SUBSCRIPTION my_sub
     CONNECTION 'host=源服务器IP port=5432 dbname=source_db user=replicator password=your_password'
     PUBLICATION my_pub;
         
     

  通过pgAdmin可在Servers→目标服务器→Replication→Subscriptions中管理订阅。

5. 第三方工具同步

若需要更灵活的同步（如跨数据库类型、大数据量），可使用第三方工具：

• HashData：支持PostgreSQL与其他数据库（如MySQL、Oracle）的数据同步，通过FDW（外部数据包装器）实现，需安装对应驱动并配置映射规则。
• rsync：用于同步PostgreSQL数据目录（需停止源服务器），适用于物理备份迁移，命令示例：

  rsync -avz /var/lib/pgsql/12/data/ user@target_server_ip:/var/lib/pgsql/12/data/
  

  注：同步前需确保源服务器已停止PostgreSQL服务，避免数据不一致。

注意事项

• 数据一致性：同步前停止源数据库的写入操作（或使用事务），避免数据冲突。
• 权限配置：确保pgAdmin、pg_cron/pgAgent有足够的权限访问数据库和文件系统。
• 测试验证：正式同步前，在测试环境验证流程的正确性（如数据完整性、性能影响）。
• 备份策略：同步前备份源数据库和目标数据库，防止误操作导致数据丢失。

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！