本地PostgreSQL 9.5迁移至AWS RDS的级联复制方案咨询

Hey there! Let's break down the key considerations and common pitfalls for connecting your on-prem PostgreSQL 9.5 standby (set up with cascading replication) to an AWS DMS replication instance for your migration to RDS PostgreSQL. Here's what you need to know:

First, make sure your cascading standby is properly configured to support DMS's replication needs:

• Adjust WAL-related settings in postgresql.conf:
  • Set max_wal_senders to a value that accounts for both the existing cascading replication link (from your on-prem master to standby) and the DMS connection. For example, max_wal_senders = 10 gives you enough headroom for additional connections.
  • Confirm wal_level = hot_standby (this is required for hot standby mode in PostgreSQL 9.5) and hot_standby = on.
• Update pg_hba.conf to allow DMS access:
  Add a rule to let the DMS replication instance's IP connect for replication:

  host    replication     dms_repl_user     [DMS_REPL_INSTANCE_IP]/32     md5
  

  Create a dedicated replication user for DMS if you haven't already:

  CREATE ROLE dms_repl_user REPLICATION LOGIN PASSWORD 'your_secure_password';
  

• Network access: Ensure your on-prem firewall, security groups, or VPN/Direct Connect setup allows incoming traffic from the DMS replication instance's IP to the standby's PostgreSQL port (default 5432).

When setting up the DMS source endpoint for your standby:

• Select PostgreSQL as the source engine, then input your standby's host, port, and the dedicated replication credentials you created. You can use any existing database name here (DMS uses replication slots/WAL for sync, not a specific database).
• Enable Use SSL if your PostgreSQL standby is configured to require SSL connections. If you run into SSL errors, double-check that your standby's ssl = on in postgresql.conf and that you've provided the correct SSL certificate in DMS if needed.
• Always run the Test Connection tool in DMS before starting the migration task. This will catch issues like incorrect credentials, network blocks, or insufficient replication permissions early.

DMS relies on continuous access to WAL logs to keep your RDS instance in sync. To avoid gaps that break replication:

• Enable WAL archiving on your standby: Set archive_mode = on and configure an archive_command to store WAL segments in a durable location (like local disk or an S3 gateway via a local proxy). For example:

  archive_command = 'cp %p /path/to/wal/archive/%f'
  

• Increase wal_keep_segments in postgresql.conf to retain enough WAL segments locally. A value like wal_keep_segments = 1000 (each segment is ~16MB, so 16GB total) gives you a buffer if DMS falls behind temporarily.
• Monitor your cascading replication health: Use the pg_stat_replication view on your standby to confirm it's still pulling WAL from the on-prem master. If the standby loses sync with the master, DMS will also lose access to new WAL data.

Since you're using a standby for migration, you want to avoid overwhelming it with DMS traffic:

• Tune DMS task parameters: Adjust MaxFullLoadSubTasks and ParallelLoadThreads based on your standby's CPU and IO capacity. Start with lower values (e.g., 2-4) and increment if the standby can handle it.
• Optimize standby query performance: For the full-load phase (where DMS runs SELECTs), tweak work_mem and shared_buffers in the standby's postgresql.conf to speed up large data reads.
• Use a high-bandwidth connection: AWS Direct Connect or a site-to-site VPN will reduce latency and increase throughput between your on-prem environment and AWS, which is critical for keeping WAL sync up to date.

To minimize downtime if your standby fails:

• Set up multiple on-prem standbys (in a cascading chain or as parallel replicas from the master) and use a load balancer (like HAProxy or pgpool-II) in front of them. Configure DMS to connect to the load balancer's address instead of a single standby—this way, if one standby goes down, the load balancer will route DMS traffic to a healthy replica.
• Monitor closely: Use AWS CloudWatch to track DMS metrics like Latency and RowsPending, and check the standby's pg_stat_replication view to ensure the DMS connection is active and catching up.

• "Could not connect to server" errors: Verify network access, pg_hba.conf rules, and credentials.
• "WAL segment not found": Check that wal_keep_segments is large enough, WAL archiving is working, and the standby is still in sync with the master.
• Slow full-load sync: Increase DMS parallelism (if the standby can handle it), check network bandwidth, or optimize the standby's PostgreSQL configuration.

内容的提问来源于stack exchange，提问作者Ming Hsieh