WARNING: it is an internal article. Please do NOT use it in production! Contact Canonical Data Platform team if you are interested in the topic.
Creating a PostgreSQL async replication setup
1 - Deploy two PostgreSQL clusters:
juju add-model az1 # db cluster 1, location: Rome
juju add-model az2 # db cluster 1, location: Lisbon
juju add-model app # db client application, location: somewhere
Rome:
juju switch az1
juju deploy postgresql-k8s db1 --trust --channel=14/edge/async --config profile=testing --base ubuntu@22.04
Lisbon:
juju switch az2
juju deploy postgresql-k8s db2 --trust --channel=14/edge/async --config profile=testing --base ubuntu@22.04
Client:
juju switch app
juju deploy postgresql-test-app
2 - Create and consume an offer
It’s required to define the roles that the clusters will play in the async replication setup, i.e., which will be the main and secondary clusters. For this setup, the application db1 will be set up as the main cluster, and db2 will be the secondary cluster.
To create a cluster set from these two clusters, we need to create a relation which uses the async_replication interface through the async-primary and async-replica relation names.
But first, it’s necessary to create and consume an offer:
juju switch az1
juju offer db1:async-primary async-primary
juju offer db1:database db1database
juju switch az2
juju offer db2:async-primary async-primary
juju offer db2:database db2database
juju switch app
juju consume az1.db1database
juju consume az2.db2database
juju consume az1.async-primary -m az2
juju consume az2.async-primary -m az1
3 - Relate the applications
juju relate -m app postgresql-test-app:first-database db1database
juju relate -m az2 async-primary db2:async-replica
4 - Promote cluster to be the main cluster
juju run -m az1 db1/leader promote-standby-cluster
Wait until the process is finished. Behind the scenes, the secondary cluster will be set up as a standby cluster, cloning data from the main cluster and rejoining all units to it. The postgresql-test-app will automatically write to db1 on the az1 side, and data will be automatically propagated to db2 on the az2 side.
5 - Remove the relation
When an async_replication relation is removed, the primary cluster will continue working as is, while the secondary cluster will be dissolved with all its units in read-only mode.
juju remove-relation -m az2 async-primary db2
6 - Recovering the read-only cluster
To recover a secondary cluster after the relation is removed, there’s a provided action:
juju run -m az2 db2/leader promote-standby-cluster
The action will recover the cluster as a standalone cluster with the data from that standby cluster.
7 - Switch app/clients between AZ
It is necessary to switch between AZ only if the previous AZ is not reachable:
juju remove-relation -m app postgresql-test-app db1database
# wait for the relation to be removed
juju relate -m app postgresql-test-app:first-database db2database