Configure postgresql.conf for cloud migration - ApsaraDB RDS

Before migrating a self-managed PostgreSQL instance to ApsaraDB RDS for PostgreSQL, you must configure two parameters in the postgresql.conf file: listen_addresses to allow the RDS instance to connect during migration, and a WAL retention parameter (wal_keep_segments or wal_keep_size) to prevent write-ahead logging (WAL) records from being deleted before replication completes.

The examples in this topic use PostgreSQL 13 on CentOS 7. Adjust the version number in file paths and commands to match your environment.

Prerequisites

Before you begin, ensure that you have:

A self-managed PostgreSQL instance deployed on an Elastic Compute Service (ECS) instance or in a data center
Access to both the postgres user and the root user on the host

Step 1: Check current parameter values

Connect to the self-managed PostgreSQL instance and run the following commands to check the current values.

Parameter	Applies to	Command	Required value
`listen_addresses`	All versions	`SHOW listen_addresses;`	`*`
`wal_keep_segments`	PostgreSQL 10, 11, 12	`SHOW wal_keep_segments;`	`4096` or greater
`wal_keep_size`	PostgreSQL 13 and later	`SHOW wal_keep_size;`	`65536` MB or greater

What these parameters control:

listen_addresses: Specifies which IP addresses the instance accepts connections from. Setting it to * allows the RDS instance to connect during migration.
wal_keep_segments / wal_keep_size: Controls how much WAL data is retained in the pg_wal directory. If the value is too low, WAL records are deleted after a full backup, which interrupts replication and forces you to restart the backup from the beginning.

Both listen_addresses and the WAL retention parameters are static parameters that take effect only after a full service restart. If any value does not meet the requirement, complete Steps 2 through 5 to stop the service, update the file, and restart.

If all values already meet the requirements, skip to Create an account for cloud migration on a self-managed PostgreSQL instance.

Step 2: Stop the PostgreSQL database service

Only the postgres user can run pg_ctl commands. Run su - postgres to switch to the postgres user.

/usr/pgsql-13/bin/pg_ctl stop -m fast

Step 3: Locate the postgresql.conf file

Only the root user can run the find command. Run su - root to switch to the root user.

find / -name postgresql.conf

The output shows the file path, for example:

/var/lib/pgsql/13/data/postgresql.conf

Step 4: Edit the postgresql.conf file

Navigate to the directory and open the file.

cd /var/lib/pgsql/13/data/
vim postgresql.conf

Set the parameters to the required values. These parameters may be commented out by default — delete the # at the start of each line after editing.

listen_addresses = '*'

# PostgreSQL 10, 11, or 12:
wal_keep_segments = 4096

# PostgreSQL 13 and later:
wal_keep_size = 65536

Press Esc, then enter :wq to save and exit.

Step 5: Start the PostgreSQL database service

Only the postgres user can run pg_ctl commands. Run su - postgres to switch to the postgres user.

/usr/pgsql-13/bin/pg_ctl start

What to do next

Create an account for cloud migration on a self-managed PostgreSQL instance