This topic describes how to configure the postgresql.conf file of a self-managed PostgreSQL instance before you use the cloud migration feature of ApsaraDB RDS for PostgreSQL to migrate the data of the self-managed PostgreSQL instance from an Elastic Compute Service (ECS) instance or a data center to an ApsaraDB RDS for PostgreSQL instance.
Procedure
Note In this topic, the self-managed PostgreSQL instance and the ApsaraDB RDS for PostgreSQL instance run PostgreSQL 13 in the CentOS 7 operating system.
- Connect to the self-managed PostgreSQL instance and query the value of the listen_addresses parameter, the value of the wal_keep_segments, or the value of the wal_keep_size parameter.
Parameter Description Command Expected output listen_addresses This parameter specifies whether the self-managed PostgreSQL instance supports remote connections. SHOW listen_addresses;
*
wal_keep_segments This parameter specifies the minimum number of log files that can be retained in the pg_wal directory. We recommend that you set this parameter to a value that is greater than or equal to 4096. If the value of this parameter is less than 4096, write-ahead logging (WAL) records of the self-managed PostgreSQL instance are deleted after a full backup. In this case, you must back up the self-managed PostgreSQL instance again. Note This parameter is valid when the self-managed PostgreSQL instance runs PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12.SHOW wal_keep_segments;
4096
wal_keep_size This parameter specifies the minimum size of log files that can be retained in the pg_wal directory. We recommend that you set this parameter to a value that is greater than or equal to 64 GB. If the value of this parameter is less than 64 GB, WAL records of the self-managed PostgreSQL instance are deleted after a full backup. In this case, you must back up the self-managed PostgreSQL instance again. Note This parameter is valid when the self-managed PostgreSQL instance runs PostgreSQL 13, PostgreSQL 14 or PostgreSQL 15.SHOW wal_keep_size;
65536
- If the values of the parameters meet your business requirements, you do not need to configure the postgresql.conf file. You can proceed to create an account that is used for the cloud migration. For more information, see Create an account for cloud migration on a self-managed PostgreSQL instance.
- If the values of the parameters do not meet your business requirements, go to Step 2.
- Stop the PostgreSQL database service. Note Only the postgres user has the permissions to run the following command. You can run the
su - postgres
command to switch to the postgres user./usr/pgsql-13/bin/pg_ctl stop -m fast
- Find the postgresql.conf file. Note Only the root user has the permissions to run the following command. You can run the
su - root
command to switch to the root user.find / -name postgresql.conf
The following or similar command output is displayed:/var/lib/pgsql/13/data/postgresql.conf
- Go to the directory in which the postgresql.conf file is stored.
cd /var/lib/pgsql/13/data/
- Run the
vim postgresql.conf
command to open the postgresql.conf file. Then, modify the value of the listen_addresses parameter and the value of wal_keep_segments or wal_keep_size parameter in the file.listen_addresses = '*' # If the self-managed PostgreSQL instance runs PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12, perform the following modification: wal_keep_segments = 4096 # If the self-managed PostgreSQL instance runs PostgreSQL 13 or PostgreSQL 14, perform the following modification: wal_keep_size = 65536
Note The preceding parameters may be commented out by default. After you modify the value of a parameter, you must delete the number sign(#)
at the beginning of the line in which the parameter resides. - Press Esc and enter
:wq
to save the postgresql.conf file and exit. - Start the PostgreSQL database service. Note Only the postgres user has the permissions to run the following command. You can run the
su - postgres
command 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