Configuring PostgreSQL Streaming Replication


Streaming replication in PostgreSQL allows a standby server to stay up-to-date with the primary server in real-time or near real-time. Here’s a step-by-step guide to set up streaming replication in PostgreSQL 14:

PostgreSQL Master name and IP address:

Hostname : PGmaster
IP Address :

PostgreSQL Slave/Replica name and IP address:

Hostname : PGSlave
IP Address :

On Master and Slave servers, PostgreSQL 14 must have installed.

Lets Start:

Step1: Configurations on master server

1.1. On master server, configure the IP address(es) listen to for connections from clients in postgresql.conf
by removing # in front of listen_address and give *. Which means listen connections from all.

Allow Connections from Any IP Address:

  • By setting listen_addresses to '*', PostgreSQL allows connections from any IP address. This is particularly useful in scenarios where your database server needs to accept connections from various clients on different networks.

1.2. Now, connect to PostgreSQL on master server and create replica login.

1.3. Enter the following entry pg_hba.conf file which is located in /etc/postgresql/14/main on Ubuntu(debian systems).

  1. host:
    • Indicates that this line defines a host-based access rule.
  2. replication:
    • Specifies the database for which this rule applies. In this case, it’s for replication, meaning it’s controlling access for replication connections.
  3. replicator:
    • Specifies the user that is allowed to connect for replication. In PostgreSQL, each database role (user) has specific privileges, and in replication scenarios, a dedicated user, often named “replicator,” is commonly used.
    • Specifies the IP address range that is allowed to connect. In this case, it’s with a subnet mask of /24, meaning it includes all IP addresses in the range to Only connections from this IP range will be accepted.
  5. md5:
    • Specifies the authentication method to be used. In this context, it’s set to md5, indicating that password-based authentication using an MD5-hashed password is required. The connecting client needs to provide a valid username and password.

1.4. Now, restart the PostgreSQL on Master server by using below command.

Step2: Configurations on slave(standby) server

2.1. We have to stop PostgreSQL on Slave server by using following command.

2.2. Now, switch to postgres user and take backup of main(data) directory.

2.3. Now, remove the contents of main(data) directory on slave server.

2.4. Now, use basebackup to take the base backup with the right ownership with postgres(or any user with right permissions).

  1. -h
    • Specifies the hostname or IP address of the PostgreSQL server from which to take the base backup. In this case, it’s set to
  2. -D /var/lib/postgresql/14/main/:
    • Specifies the target directory where the base backup will be stored. In this example, it’s set to /var/lib/postgresql/14/main/. This should be an empty directory, as pg_basebackup will populate it with the necessary files.
  3. -U replicator:
    • Specifies the username (role) to use when connecting to the PostgreSQL server. In this case, it’s set to replicator. This is likely the user with replication privileges.
  4. -P:
    • Indicates that pg_basebackup should prompt for a password. This is necessary when connecting to the PostgreSQL server, especially when the -U flag is used.
  5. -v:
    • Enables verbose mode, providing more detailed information about the backup process.
  6. -R:
    • Enables the creation of a replication slot during the backup. A replication slot is used to track the changes made to the database, making it useful for setting up streaming replication.
  7. -X stream:
    • Specifies the method of transferring data during the backup. In this case, it’s set to stream, indicating that the backup should be performed using streaming replication.
  8. -C:
    • Specifies that the backup should be taken in a consistent state, meaning it will not include any changes made to the database during the backup process.
  9. -S slaveslot1:
    • Specifies the name of the replication slot to be created. Replication slots are used to keep track of the changes sent to replicas in streaming replication setups. In this case, a slot named slaveslot1 is created.

In summary, this pg_basebackup command is creating a base backup of a PostgreSQL cluster running on the server with IP The backup will be stored in the directory /var/lib/postgresql/14/main/. It uses the replicator user for authentication, prompts for the user’s password, and sets up a replication slot named slaveslot1 for streaming replication. The backup is taken in a consistent state (-C) and is transferred using streaming replication (-X stream). The -R flag ensures that a replication slot is created during the backup process.

Note : Then provide the password for user replicator created in master server.

2.5. Notice that standby.signal is created and the connection settings are appended to

2.6. postgresql.conf and there is a standby.signal file present in the data directory.

2.7. Now connect the master server, you should be able to see the replication slot called slotslave1 when you open the pg_replication_slots view as follows.

Step3. Test replication setup

3.1. Now start PostgreSQL on slave(standby) server.

3.2. Now, try to create object or database in slave(standby) server. It throws error, because slave(standby) is read-only server.

3.3. WE can check the status on standby using below command.

3.4. Now, verify the replication type synchronous or aynchronous using below command on master database server.

3.5. Lets create a database in master server and verify its going to replicate to slave or not.

3.6. Now, connect to slave and verify the database copied or not.

3.7. If you want to enable synchronous, the run the below command on master database server and reload postgresql service.

Thats all. We have successfully setup streaming replication in PostgreSQL step by step on Ubuntu.


  • Test the Replication:
    • Create some test data on the primary and ensure it replicates to the standby.
    • Check the replication status with pg_stat_replication on the primary.
  • Monitoring and Maintenance:
    • Regularly monitor replication lag and the health of both servers.
    • Plan for failover and backup strategies.


  • Network Configuration: Ensure reliable network connectivity between the primary and standby servers.
  • Firewall Settings: Adjust firewall settings to allow required connections.
  • Version Compatibility: Use the same PostgreSQL version on both primary and standby servers.
  • Security: Use strong passwords and consider using SSL connections for replication.
  • Data Integrity: Regularly validate the data integrity on the standby server.

This setup provides a basic streaming replication configuration. Depending on your environment and requirements, you might need to consider additional configurations like synchronous replication, multiple standbys, or using replication slots. Always refer to the PostgreSQL documentation for detailed information and best practices.

Leave a Reply

Your email address will not be published. Required fields are marked *