All Products
Search
Document Center

ApsaraDB RDS:Configure the postgresql.conf file

Last Updated:Sep 22, 2023

ApsaraDB RDS for PostgreSQL allows you to use the cloud migration feature to migrate the data of the self-managed PostgreSQL instance that is deployed on an Elastic Compute Service (ECS) instance or in a data center to an ApsaraDB RDS for PostgreSQL instance. This topic describes how to configure the postgresql.conf file of a self-managed PostgreSQL instance.

Procedure

Note

In this topic, the self-managed PostgreSQL instance and the RDS instance run PostgreSQL 13 in CentOS 7.

  1. Connect to the self-managed PostgreSQL instance and query the value of the listen_addresses parameter and the value of the wal_keep_segments or wal_keep_size parameter.

    Parameter

    Description

    Command

    Expected Value

    listen_addresses

    Specifies whether the self-managed PostgreSQL instance supports remote connections.

    SHOW listen_addresses;

    *

    wal_keep_segments

    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 suitable for the self-managed PostgreSQL instance that runs PostgreSQL 10, PostgreSQL 11, or PostgreSQL 12.

    SHOW wal_keep_segments;

    4096

    wal_keep_size

    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 suitable for the self-managed PostgreSQL instance that runs PostgreSQL 13, PostgreSQL 14, or PostgreSQL 15.

    SHOW wal_keep_size;

    64GB

  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
  3. 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

    Sample output:

    /var/lib/pgsql/13/data/postgresql.conf
  4. Go to the directory in which the postgresql.conf file is stored.

    cd /var/lib/pgsql/13/data/
  5. 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 the 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.

  6. Press Ecs and enter :wq to save the postgresql.conf file and exit.

  7. 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