/
Creating PostgreSQL Standby Cluster

Creating PostgreSQL Standby Cluster

Owned by Jan Klinke
Last updated: Mar 20, 2025
8 min read

This page summarizes the step necessary for creating the postgreSQL standby cluster.

1. Prerequistes and Considerations

1.1 Pre-create the data directory on standby host

It is recommended to pre-create the PostrgeSQL data directory on the standby host, for example:

[postgres@pgdemo2 ~]$ mkdir /var/lib/pgsql/16/data [postgres@pgdemo2 ~]$ chmod 750 /var/lib/pgsql/16/data

note that you should be logged on as user who will run postgreSQL cluster and the same user should be used for running dbvagentmanager.

Any directory for postgreSQL cluster should have 750 permissions or 700

1.2 Decide for replication mode and standby Database mode

Dbvisit StandbyMP supports two modes for PostgreSQL replication to standby:

A. WAL streaming

WAL Streaming (Streaming Replication) is more common method for replication. Its setup requires dedicated user on primary PostgreSQL cluster, modification of pg_hba.conf file and creates replication slot on primary which is used to synchronize the standby PostgreSQL cluster.

Dbvisit automatically modifies the postgresql.conf on primary and standby.

B. WAL file archiving

WAL file archiving takes WAL files generated by primary, transfer them to standby and apply to the replica. The Primary Cluster will be restarted to enable the WAL archive mode and also provide the location for archive and restore command along with archive timeout parameter. This is updated automatically in postgresql.conf file. Existing WAL archiving configuration can be disrupted.

For each mode (streaming and archiving), it is possible to set the Standby PostgreSQL cluster to be HOT or WARM:

  • HOT Standby: read only queries are possible

  • WARM Standby: not possible to run read only queries

If you’re considering creating cascade configuration, you must use HOT Standby

1.3 Check primary postgresql.conf

During the standby PostgreSQL cluster creation, Dbvisit will copy postgresql.conf to the standby host “as it is” and add new lines related to the replication. You need to ensure that variables which are currently set in postgresql.conf file will work on standby as well.

For example listen_address parameter should be set to listen on all NICs:

listen_addresses='*'

Otherwise, if set to specific IP, standby cluster will fail to start.

You also need to make sure you have following line in primary postgresql.conf:

wal_level = replica

1.4 Create Replication user and modify pg_hba.conf on primary

This is required prerequiste for WAL Streaming replication only. WAL Streaming replication requires dedicated user in primary cluster as well as entry in primary pg_hba.conf to allow standby cluster to connect to this user for replication.

Example for creating replication user on primary:

psql postgres=# create user dbvreplica with replication encrypted password 'Dbvisit2025!'; CREATE ROLE

Example add line to pg_hba.conf:

host replication dbvreplica 10.1.1.0/16 scram-sha-256

Note that all lines in pg_hba.conf follow precedence rule from top to bottom. For example, adding this line to the end of the file will have no effect if any previous line will reject the access for this user or host / IP range

Doing any modification to pg_hba.conf will require reload of primary cluster:

/usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data reload

Test the connection from the standby server (enter the password you specified for the user on primary earlier):

$ psql "dbname=postgres replication=true" -c "IDENTIFY_SYSTEM;" -U dbvreplica -h pgdemo1 Password for user dbvreplica: systemid | timeline | xlogpos | dbname ---------------------+----------+------------+-------- 7477896642589677573 | 1 | 0/42000000 | (1 row)

You will see systemid together with timeline identifier in response if communication works fine.

2. Create WAL Streaming DR Cluster

To create WAL Streaming DR cluster (ensure that all prerequisites are met) click on “Setup Disaster Recovery” on the right ACTIONS pane:

image-20250318-162252.png

Following form will be displayed where you can change various settings for your DR cluster (this form is the same for Streaming and Archiving replication):

image-20250318-162438.png

Number

Description

Explanation

 

Number

Description

Explanation

 

1

Location of cluster datafiles on standby

Choose location on standby host where postgreSQL cluster datafiles will be placed. The directory must exist on standby and have either 750 or 700 permissions

 

2

Location of cluster configuration files on standby

Choose location on standby host where postgreSQL cluster configuration files will be placed (you use the same location as for the datafiles). The directory must exist on standby and have either 750 or 700 permissions. The configuration files stored here will be:

pg_hba.conf, pg_ident.conf, postgresql.conf

 

3

Standby cluster mode

Choose between HOT or WARM standby

 

4

Standby cluster recovery model

Choose WAL Streaming, WAL File archiving is described further down on this page

 

5

Replication Username and Password

Use the username and password you have created before as described in section “1.4 Create Replication user and modify pg_hba.conf on primary”

 

6

Test Connection

Test the replication username and password

 

7

Enable / Disable Observer

See Observer / Automatic Failover for more details about observer.

 

8

Start

Start the create standby postgreSQL cluster task

 

Once the task is submitted, you will be able to see it in the task pane:

image-20250319-112755.png

and click on it to see the details:

image-20250319-112821.png

During the standby cluster creation, Dbvisit will automatically modify and create necessary files on standby (for example postgresql.auto.conf, standby.signal).

Modifications for standby postgresql.conf:

# Added by Dbvisit StandbyMP archive_mode = 'off' # Added by Dbvisit StandbyMP data_directory = '/var/lib/pgsql/16/data' # Added by Dbvisit StandbyMP hot_standby = 'on' # Added by Dbvisit StandbyMP port = 5432 # Added by Dbvisit StandbyMP primary_slot_name = 'dbvisit_18'

Once the whole process is finished, you PostgreSQL configuration will show following state:

image-20250319-114128.png

3. Create WAL File Archiving DR Cluster

User who don’t wish to create Streaming Replica, can use more traditional WAL File Archiving method for replicating to the standby PostgreSQL cluster. The Primary Cluster will be restarted to enable the archive mode and also to provide the location for archive and restore command along with archive timeout parameter. This is automatically updated on postgresql.conf file.

The primary cluster will be restarted during this process and changes will be done to postgresql.conf file even if existing WAL File archiving setup is in place. We do recommend to take backup of postgresql.conf file on primary.

Start the standby PostgreSQL cluster creation by selecting “Set Up Disaster Recovery” from right ACTIONS pane:

image-20250318-162252.png

Following form will be displayed where you can change various settings for your DR cluster (this form is the same for Streaming and Archiving replication):

image-20250319-115559.png
image-20250319-115725.png

aas

Number

Description

Explanation

Number

Description

Explanation

1

Location of cluster datafiles on standby

Choose location on standby host where postgreSQL cluster datafiles will be placed. The directory must exist on standby and have either 750 or 700 permissions

2

Location of cluster configuration files on standby

Choose location on standby host where postgreSQL cluster configuration files will be placed (you use the same location as for the datafiles). The directory must exist on standby and have either 750 or 700 permissions. The configuration files stored here will be:

pg_hba.conf, pg_ident.conf, postgresql.conf

3

Standby cluster mode

Choose between HOT or WARM standby

4

Standby cluster recovery model

Choose WAL File archiving, WAL Streaming is described in previous section

5

Base Backup Locations

The location on a primary where the base backup is taken and the location to which it's transferred in the standby. This location provided is a default location but it's recommended to provide a different location to accommodate backups that are larger in size.

6

Custom Backup Locations

The custom backup location of primary and standby in which the WAL archive files are copied in the primary and transferred to standby.

7

Archiving Settings, Archive Timeout

The archive timeout in seconds. This corresponds to the archive_timeout parameter in postgresql.conf. The archive_timeout specifies the maximum time interval between the completion of a WAL file and its archiving, and also indicates the maximum lag standby (replica) database can potentially fall behind the primary database in terms of the time of changes applied.

8

Enable / Disable Observer

See Observer / Automatic Failover for more details about observer.

9

Start

Start the create standby postgreSQL cluster task

After submitting, as always, you will be able to see immediately the create standby cluster task and view its details:

image-20250319-120606.png
image-20250319-120625.png

During the process, Dbvisit automatically creates necessary files on standby server and modifies primary and standby postgresql.conf file. As noted previously, primary cluster will be restarted.

Modifications for primary postgresql.conf:

# Added by Dbvisit StandbyMP archive_command = '"/opt/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "19" "-targetAddr" "pgdemo2.qadev.cz" "-targetPort" "7890" "-sourceFile" "/var/lib/pgsql/16/data/%p" "-targetFile" "/opt/dbvisit/standbymp/backup/19/%f" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-16/bin" "-pgData" "/var/lib/pgsql/16/data" "-backupFile" "/opt/dbvisit/standbymp/backup/19/%f"' # Added by Dbvisit StandbyMP archive_mode = 'on' # Added by Dbvisit StandbyMP archive_timeout = 60 # Added by Dbvisit StandbyMP hot_standby = 'off'

Modifications for standby postgresql.conf:

# Added by Dbvisit StandbyMP archive_command = '"/opt/dbvisit/standbymp/bin/dbvpgarchive" "-configurationId" "19" "-targetAddr" "pgdemo2.qadev.cz" "-targetPort" "7890" "-sourceFile" "/var/lib/pgsql/16/data/%p" "-targetFile" "/opt/dbvisit/standbymp/backup/19/%f" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-16/bin" "-pgData" "/var/lib/pgsql/16/data" "-backupFile" "/opt/dbvisit/standbymp/backup/19/%f"' # Added by Dbvisit StandbyMP archive_mode = 'off' # Added by Dbvisit StandbyMP archive_timeout = 60 # Added by Dbvisit StandbyMP hot_standby = 'on' # Added by Dbvisit StandbyMP archive_cleanup_command = '"/usr/pgsql-16/bin/pg_archivecleanup" "/opt/dbvisit/standbymp/backup/19" "%r"' # Added by Dbvisit StandbyMP data_directory = '/var/lib/pgsql/16/data' # Added by Dbvisit StandbyMP port = 5432 # Added by Dbvisit StandbyMP restore_command = '"/opt/dbvisit/standbymp/bin/dbvpgrestore" "-configurationId" "19" "-sourceFile" "/opt/dbvisit/standbymp/backup/19/%f" "-targetFile" "%p" "-localAddr" "" "-localPort" "7890" "-pgBin" "/usr/pgsql-16/bin" "-pgData" ""'

Once the whole process is finished, you PostgreSQL configuration will show following state:

image-20250319-121523.png

4. Post-Standby Creation Tasks

Dbvagentmanager doesn’t manage the startup of standby postgreSQL cluster. If it is required for standby cluster to start automatically, we recommend to implement systemctl service as per official PostgreSQL documentation:

18.3. Starting the Database Server

for example:

[Unit] Description=PostgreSQL database server Documentation=man:postgres(1) After=network-online.target Wants=network-online.target [Service] Type=notify User=postgres ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data ExecReload=/bin/kill -HUP $MAINPID KillMode=mixed KillSignal=SIGINT TimeoutSec=infinity [Install] WantedBy=multi-user.target

 

Related content