How to Create a PostgreSQL Read-Only Standby Server Using pg_basebackup

 Maintaining database availability is an important part of any production postgresql deployment. A standby server provides a continuously updated copy of the primary database by receiving and replaying Write-Ahead Log (WAL) records. This replica can be used for disaster recovery, reporting workloads, or to reduce downtime when the primary server becomes unavailable.

One of the simplest and most reliable ways to create a physical standby server is by using pg_basebackup. This utility copies the entire data directory from the primary server and prepares the new instance for streaming replication with minimal manual configuration.

In this blog, we will learn how to configure a postgresql read-only standby server from scratch.

Check the current clusters in the system like this.

pg_lsclusters 

Result:

Ver Cluster Port Status Owner    Data directory     	  Log file
18  main    5432 online postgres /var/lib/postgresql/18/main    /var/log/postgresql/postgresql-18-main.log

Now, check the values of the parameters below. If these are not equal, set the values for these parameters by directly editing the postgres conf file and restart the server, or execute the alter system command with select pg_reload_conf();

Check the current value of the wal_level parameter like this.

 SHOW wal_level; 

Result:

 wal_level 
-----------
 replica
(1 row)

Check the value of the max_wal_senders parameter like this.

SHOW max_wal_senders; 

Result:

 max_wal_senders 
-----------------
 10
(1 row)

Check the value of the max_replication_slots parameter like this.

 SHOW max_replication_slots;

Result:

 max_replication_slots 
-----------------------
 10
(1 row)

Check the value of the listen_addresses parameter like this.

 SHOW listen_addresses; 

Result:

 listen_addresses 
------------------
 localhost
(1 row)

Now, create a role named replicator with the replication privilege and also set the password for this new user.

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'cool';

Now, we need to add this user-related configuration to the postgres hba file.

We can check the path of this postgres hba ( host based authentication ) file like this.

 show hba_file ;

Result:

              hba_file               
-------------------------------------
 /etc/postgresql/18/main/pg_hba.conf
(1 row)

Now, edit this file

 sudo nano /etc/postgresql/18/main/pg_hba.conf

Add this line at the bottom of this file, where the replication-related settings are seen.

host    replication     replicator      127.0.0.1/32        scram-sha-256

Now, restart the postgres to reflect the changes we made in the hba file.

sudo systemctl restart postgresql

Now, check the cluster status again to verify that there is no issue based on the settings we applied.

pg_lsclusters 

Result:

Ver Cluster Port Status Owner    Data directory              Log file
18  main    5432 online postgres /var/lib/postgresql/18/main /var/log/postgresql/postgresql-18-main.log

Now, create the same postgres version cluster on a different port for setting up the standby server.

sudo pg_createcluster 18 replica -p 5433

Result:

Creating new PostgreSQL cluster 18/replica ...
/usr/lib/postgresql/18/bin/initdb -D /var/lib/postgresql/18/replica --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "en_IN".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are enabled.
fixing permissions on existing directory /var/lib/postgresql/18/replica ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Asia/Kolkata
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner    Data directory                 Log file
18  replica 5433 down   postgres /var/lib/postgresql/18/replica 
/var/log/postgresql/postgresql-18-replica.log

Now, the new postgres cluster is created on port 5433.

Now, the status of this newly created cluster is down. Now, use the pg_basebackup command to copy the data from port 5432 to 5433, and the -R option will automatically write the configuration needed for replication.

sudo -u postgres /usr/lib/postgresql/18/bin/pg_basebackup \
  -h 127.0.0.1 -p 5432 -U replicator \
  -D /var/lib/postgresql/18/replica \
  -X stream \
  -C -S replica_slot \
  -R \
  -c fast \
  -P -v
Password: 

Explanation of each option we used here during the execution of this pg_basebackup.

Now, after entering the password, you should see the result of this pg_basebackup like this.

pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/5000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created replication slot "replica_slot"
70701/70701 kB (100%), 1/1 tablespace                                         
pg_basebackup: write-ahead log end point: 0/5000158
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed

Now, check that the standby.singal file is created properly.

sudo -u postgres ls /var/lib/postgresql/18/replica/standby.signal

Result:

/var/lib/postgresql/18/replica/standby.signal

Also check the existence of the postgresql.auto.conf file like this.

sudo -u postgres cat /var/lib/postgresql/18/replica/postgresql.auto.conf

Result :

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator password=cool channel_binding=prefer host=127.0.0.1 port=5432 sslmode=prefer sslnegotiation=postgres sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres gssdelegation=0 target_session_attrs=any load_balance_hosts=disable'
primary_slot_name = 'replica_slot'

Now, everything looks good. So, start the replica cluster directly by using the command below.

sudo pg_ctlcluster 18 replica start

Now, check the status of this postgres cluster to verify whether the replica cluster is running or not.

pg_lsclusters  

Result:

Ver Cluster Port Status Owner    Data directory                 Log file
18  main    5432 online postgres /var/lib/postgresql/18/main    /var/log/postgresql/postgresql-18-main.log
18  replica 5433 online postgres /var/lib/postgresql/18/replica /var/log/postgresql/postgresql-18-replica.log

Now, log in to each psql terminal and check whether streaming replication is working or not.

From psql on port 5432.

 select * from pg_replication_slots ;

Result:

-[ RECORD 1 ]-------+-------------
slot_name           | replica_slot
plugin              | 
slot_type           | physical
datoid              | 
database            | 
temporary           | f
active              | t
active_pid          | 492703
xmin                | 
catalog_xmin        | 
restart_lsn         | 0/6000060
confirmed_flush_lsn | 
wal_status          | reserved
safe_wal_size       | 
two_phase           | f
two_phase_at        | 
inactive_since      | 
conflicting         | 
invalidation_reason | 
failover            | f
synced              | f

Here, we can see the replication slot-related metadata we created during the pg_basebackup command execution.

Now, check the rstandby/recovery mode status of this postgres cluster.

 SELECT pg_is_in_recovery();  

Result:

-[ RECORD 1 ]-----+--
pg_is_in_recovery | f

Now, check the status of the replica cluster to check whether it is in standby/recovery mode from port 5433.

 SELECT pg_is_in_recovery();  

Result :

 pg_is_in_recovery 
-------------------
 t
(1 row)

Check the records from the catalogue named pg_stat_wal_receiver like this.

 SELECT status, sender_host, latest_end_lsn FROM pg_stat_wal_receiver;

Result:

  status   | sender_host | latest_end_lsn 
-----------+-------------+----------------
 streaming | 127.0.0.1   | 0/6000060
(1 row)

Now, it is in streaming mode, and we can also see the latest log sequence number of the WAL files.

Now, on port 5433 (replica cluster), when you try to create anything, it will show an error like this.

 create database demo;

Result:

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

Now, we can say that the postgres standby server creation is working perfectly based on the pg_basebackup method.

A Postgresql standby server created with pg_basebackup provides an efficient and dependable method for maintaining a synchronized copy of the primary database. By configuring the required replication parameters, creating a replication role, taking a base backup, and validating the replication status, you can establish a fully functional read-only standby server.

After completing the steps in this guide, the standby instance continuously receives WAL records from the primary server and applies them in real time, ensuring that it stays up to date. Verifying the replication slot, recovery status, WAL receiver, and read-only behaviour confirms that the standby server has been configured successfully and is ready to support backup, reporting, or high-availability requirements.