Multi-Cluster Replication with Pigsty

[mulyadi1994]

Is it possible to deploy two Pigsty-based PostgreSQL clusters in a replication setup, such as active-standby or multi-cluster replication, for high availability or cross-region failover?

[Vonng]

Check the standby cluster in configration docs, you can also use logical replication for that purpose

[mulyadi1994]

I've successfully deployed two PostgreSQL clusters using Pigsty. Now I would like to convert one of the clusters into a standby of the other (primary). I tried re-running the install.yml playbook on the cluster I want to use as standby, but I noticed:

The database on the standby cluster is not synchronized with the primary.

There’s no replication activity shown in pg_stat_replication.

I’m unsure if replication is automatically configured in this scenario, or if additional manual steps are required.

My questions:

What is the proper way to convert an existing Pigsty cluster into a standby if it was initially deployed as a primary?

Is edit pigsty.yml and re-running install.yml enough ?

[mulyadi1994]

I’ve recently created a new PostgreSQL cluster intended to serve as a logical standby for an existing production cluster, using Pigsty.

The setup steps were:

On Existing:

pg-meta:
hosts:
10.10.10.56: { pg_seq: 1, pg_role: primary } # <---- primary instance with read-write capability
10.10.10.59: { pg_seq: 2, pg_role: replica } # <---- read only replica for read-only online traffic
10.10.10.62: { pg_seq: 3, pg_role: offline } # <---- offline instance of ETL & interactive queries
vars:
pg_cluster: pg-meta # required identity parameter, usually same as group name

On New Cluster:

pg-meta-dev:
hosts:
10.10.10.160: { pg_seq: 1, pg_role: primary, pg_upstream: 10.10.10.56 } # <---- primary instance with read-write capability
10.10.10.163: { pg_seq: 2, pg_role: replica } # <---- read only replica for read-only online traffic
10.10.10.129: { pg_seq: 3, pg_role: offline } # <---- offline instance of ETL & interactive queries
vars:
pg_cluster: pg-meta-dev # required identity parameter, usually same as group name

Issue:
On the primary (existing) cluster, I only see replication connections from local nodes in the same cluster (pg_stat_replication only shows existing cluster IPs).

On the new standby cluster, I can see the replication status as streaming, but the receive_start_lsn, write_lsn, and flush_lsn all remain at 0/17000000.

It seems like replication is not progressing at all.

Questions:

Is this normal behavior for a newly initialized logical replica?

Are there any steps I might be missing to properly register the standby with the primary?

Is it expected that the standby cluster node doesn’t appear in pg_stat_replication on the primary in logical replication?

Any advice or insight would be greatly appreciated. Thanks in advance!

[Vonng]

the pg_upstream: 10.10.10.56 here is setting up a standby cluster which are physical copies of original cluster. You'll have to create publication and subscription with SQL to build a logical replication.