Run PostgreSQL streaming replication synchronous and asynchronous simultaneously

Question

I am using PostgreSQL 14 and ubuntu as my OS.

• I have done the database replication in synchronous mode
• Now I want to add another server and the relationship between primary and new_standby node will be asynchronous.
• Another problem, how to set application_name for different nodes? I have got same application_name=14/main for my two standby servers

Can anyone help me to solve these issues?

Answer 1

Yes, you can run it simultaneously.

You should have this configuration in your primary node:

listen_addresses = '*'
port = 5432
wal_level = hot_standby
max_wal_senders = 16
wal_keep_segments = 32
synchronous_commit = on
synchronous_standby_names = 'pgsql_0_node_0'

Restart the node to take the changes:

$ systemctl restart postgresql-14

Create the replication role:

$ CREATE ROLE replication_user WITH LOGIN PASSWORD 'PASSWORD' REPLICATION;

And configure this in your standby nodes:

Both:

wal_level = hot_standby
hot_standby = on

Sync:

standby_mode = 'on'
promote_trigger_file='/tmp/failover_5432.trigger'
recovery_target_timeline=latest
primary_conninfo='application_name=pgsql_0_node_0 host=PRIMARY_NODE port=5432 user=replication_user password=PASSWORD'

Replace PRIMARY_NODE, user, and password with the correct values.

Async:

promote_trigger_file='/tmp/failover_5432.trigger'
recovery_target_timeline=latest
primary_conninfo='application_name=pgsql_0_node_1 host=PRIMARY_NODE port=5432 user=replication_user password=PASSWORD'

Replace PRIMARY_NODE, user, and password with the correct values.

Restart the node to take the changes:

$ systemctl restart postgresql-14

Then, you can run this in your Primary node to see the replication nodes:

$ SELECT pid,usename,application_name,state,sync_state FROM pg_stat_replication;

  pid  |     usename      | application_name |   state   | sync_state
-------+------------------+------------------+-----------+------------
 10951 | replication_user | pgsql_0_node_1   | streaming | async
 10952 | replication_user | pgsql_0_node_0   | streaming | sync
(2 rows)