Introduction:
PostgreSQL, one of the most popular open-source relational database management systems, offers robust replication capabilities to ensure high availability and fault tolerance. Streaming replication, a built-in feature introduced in PostgreSQL version 9.0 and onwards, provides an efficient and synchronous replication solution. In this blog post, we will guide you through the commands and configuration changes necessary to set up streaming replication in PostgreSQL version 11.

Prerequisites:
Before we dive into the configuration, make sure you have the following prerequisites in place:

• A working installation of PostgreSQL version 11.
• Two or more PostgreSQL instances to act as the primary and standby servers.
• A reliable network connection between the primary and standby servers.

Step 1: Enable Replication on the Primary Server

Open the PostgreSQL configuration file on the primary server (typically located at /etc/postgresql/11/main/postgresql.conf).

Uncomment or add the following line to enable replication:

This allows the primary server to accept connections from the standby server(s).

 listen_addresses = '*'
 

Set a unique identifier for your primary server by adding or modifying the following line:

 primary_conninfo = 'host=<primary_ip> port=<primary_port> user=<replication_user> password=<replication_password>'
 

Add the replication user by opening the pg_hba.conf file (usually located at /etc/postgresql/11/main/pg_hba.conf) and appending the following line:

Replace <replication_user> and <standby_ip> with the respective values.

 host replication <replication_user> <standby_ip>/32 md5
 

Restart the PostgreSQL service on the primary server for the changes to take effect:

 $ sudo service postgresql restart
 

Step 2: Configure the Standby Server(s)

On each standby server, open the PostgreSQL configuration file (postgresql.conf) and add or modify the following lines:

Replace <primary_ip>, <primary_port>, <replication_user>, <replication_password>, and <standby_name> with the respective values.

 hot_standby = on
primary_conninfo = 'host=<primary_ip> port=<primary_port> user=<replication_user> password=<replication_password> application_name=<standby_name>'
trigger_file = '/tmp/postgresql.trigger.<standby_name>'
 

Restart the PostgreSQL service on each standby server:

 $ sudo service postgresql restart
 

Step 3: Set up Replication

On the primary server, create a base backup by executing the following command:

Replace <primary_ip> and <replication_user> with the respective values.

 $ pg_basebackup -h <primary_ip> -D /var/lib/postgresql/11/main -U <replication_user> -P
 

Start the standby server(s) by executing the following command:

 $ pg_ctlcluster 11 main start
 

Verify that the standby server(s) are connected and replicating by checking the PostgreSQL log files and using the following SQL command on the primary server:

 SELECT * FROM pg_stat_replication;
 

Conclusion: Congratulations! You have successfully configured streaming replication in PostgreSQL version 11. By following the steps outlined in this guide, you can ensure data redundancy and high availability for your PostgreSQL databases. Remember to regularly monitor the replication status and test failover scenarios to ensure the reliability of your setup.

Note: This guide assumes a basic understanding of PostgreSQL administration and Linux commands. Always refer to the official PostgreSQL documentation for detailed information and specific configuration options.