Setting Up a Standby Server

In this lesson, we'll walk through the practical steps to set up a PostgreSQL standby server. This server will continuously synchronize data from your primary server, acting as a read-replica and a crucial part of your disaster recovery strategy.

We'll cover the necessary configurations on both the primary and the standby.

Before initializing our standby, we need a dedicated user on the primary server with replication privileges. This user will be used by the standby to connect and stream WAL (Write-Ahead Log) data.

Run this SQL command on your primary PostgreSQL server:

Next, we need to tell the primary server to accept connections from our standby server for replication. Edit the pg_hba.conf file on your primary server.

Add an entry like this, replacing 192.168.1.100 with the actual IP address of your standby server:

After modifying pg_hba.conf, PostgreSQL needs to reload its configuration. You can do this without a full restart in most cases.

Execute this command on your primary server:

Now, let's move to the standby server. The first step is to get a base copy of the primary's data directory. We use pg_basebackup for this. This command connects to the primary, copies all its data, and creates necessary recovery configuration files.

Run this command on your standby server, replacing placeholders:

Let's break down the pg_basebackup options:

• -h primary_ip: Hostname or IP of the primary server.
• -U repluser: The replication user we created.
• -D /path/to/data: The data directory on the standby.
• -P: Show progress during the backup.
• -R: Crucial! Creates a standby.signal file and appends primary_conninfo to postgresql.conf, configuring the standby for recovery.

The -R option handles most of the recovery setup. However, ensure hot_standby is enabled in the standby's postgresql.conf if you want to use it for read queries.

Open postgresql.conf on the standby and ensure this line is present and uncommented:

The -R option should have automatically added a primary_conninfo entry to your standby's postgresql.conf. This tells the standby how to connect to the primary.

Verify it looks similar to this, with your actual primary IP and replication user password:

With the data copied and configuration in place, you can now start the PostgreSQL service on your standby server. It will automatically begin connecting to the primary and streaming WAL records.

Execute this command on your standby server:

To confirm that replication is working, connect to your primary server and query the pg_stat_replication view. You should see an entry for your standby server.

A state of 'streaming' indicates success!

You've just set up a PostgreSQL standby! Let's test your understanding of a key step.

Great job! You've successfully configured a PostgreSQL standby server for streaming replication. This involves:

• Creating a replication user on the primary.
• Configuring pg_hba.conf on the primary.
• Using pg_basebackup -R on the standby for initial data copy and automatic configuration.
• Ensuring hot_standby = on on the standby.
• Starting the standby and verifying its status.

Your standby is now ready to receive continuous updates from the primary!