Setting up PostgreSQL Write Ahead Logs for Logical Replication

A Write Ahead Log(WAL) is a collection of log files that record information about data modifications and data object modifications made on a Postgres server instance. Typically WAL is used for data replication and data recovery.

Hevo supports data ingestion for replication from Postgres servers via Write Ahead Logs set at the logical level(available on Postgres version 9.4). Following are the instructions below to enable WAL.

Setting up WAL on self-hosted PostgreSQL server

  1. Changes to be made in the Postgres server configuration: Postgres configuration file i.e. postgresql.conf is generally found in /etc/postgresql/\<version\>/main/ directory in Linux system. The options to configure are as follow. To understand more about the options please head on to this Postgres documentation:
    • wal_level: set the value to logical
    • max_wal_senders: This is a number field indicating the number of processes that can simultaneously transmit WAL log, it is set to zero value (default). This means that WAL is disabled. In order to enable WAL, this value should be set to at least 1.
    • max_replication_slots: This is a number field indicating the number of clients that can connect to the server. This value should be set to at least 1.
  2. Changes to be made in Postgres authentication configuration: Postgres configuration file i.e. pg_hba.conf is again generally found in /etc/postgresql/\<version\>/main/ directory in Linux system. In this file, you will have to give the replication user permission to connect from an external network to the Postgres server. Remember to replace <user> with your intended user. The options to configure are as follows. To understand more about the options please head on to this Postgres documentation:
    • local replication <user> peer
      host replication <user> 127.0.0.1/0 md5       
      host replication <user> ::1/0 md5
      
  3. Assigning the role to the user: Once you are done with the changes 1 and 2 restart the Postgres server and run the following command to give permission to access the WAL by the replication user. Remember to replace <user> with your intended user.:
    • alter role <user> with replication;
    • alter role <user> with login;

Setting up WAL on PostgreSQL server hosted with AWS RDS

  1. Log on to AWS RDS dashboard
  2. Navigate to Parameter groups in the left sidebar.
  3. Select the Parameter group corresponding to your Postgres instance, to edit. If this is a default Parameter group, RDS would restrict to edit the Parameter group, in such case go ahead create a new parameter group.
  4. In your Parameter group search and change the following parameters.
    1. rds.logical_replication: Set this value to 1. This will enable WAL logs at logical level.
    2. max_wal_senders: This field indicates number of processes that can simultaneously transmit WAL. RDS recommends this value to be set to at least 5. This is so that the internal replication by RDS is not affected.
    3. max_replication_slots: This field indicates the number of clients that can connect to the server. RDS recommends this value to be set to at least 5. This is so that the internal replication by RDS is not affected.
  5. Login to your RDS instance and create a user in your postgres instance and grant replication permission with the following command. Note DB master user might already have the permission.
    1. grant rds_replication to <user>;
  6. Ensure that the Parameter group you just changed is attached to the RDS instance you wish to replicate. You will have to restart the RDS instance to apply the parameter group changes.
Last updated on 18 Aug 2020