Overview

PostgreSQL Read Replicas

PostgreSQL read replicas provide improved performance by distributing read queries, thus reducing the load on the primary database. They also enhance high availability, scalability, and disaster recovery. Additionally, replicas can be placed in different geographical zones to further improve availability and reduce latency.

Creating a Read Replica

To create a read replica for an existing PostgreSQL database in another zone, you can use the following command:

exo api --zone at-vie-1 create-dbaas-service-pg rocket <<EOF
{
  "name": "rocket",
  "plan": "startup-8",
  "integrations": [{"type": "read_replica", "source-service": "launch-site"}]
}
EOF

This will create an instance called rocket in Zone AT-VIE-1 for an existing database called launch-site.

NOTE - Do not forget to configure the IP Filter to access the new database.

Verifying the Replica

To monitor the replication status in PostgreSQL, you can utilize the pg_stat_replication system view. This view offers insights into the replication status on a primary server, displaying details for each replication connection, such as the state, sent and written positions, and any lag.

To check the replication status, execute the following SQL query on your primary server:

\x
SELECT * FROM pg_stat_replication;

This query will return columns like pid, application_name, client_addr, state, sent_lsn, and write_lsn.

Promoting the Replica to Master

Step 1: Get details of the replica service to find the integration ID

exo api get-dbaas-service-pg -z at-vie-1 rocket

Step 2: Check Integration Status

exo api get-dbaas-integration <integration_id>

Step 3: Promote Replica to a master Database (Remove Replication)

exo api delete-dbaas-integration <integration_id>

NOTE -

  • Customers can create a read replica on a startup plan, regardless of the main cluster’s plan.
  • The read replica node is vertically scalable.
  • It can be horizontally scaled by promoting it to the primary, becoming a standalone service detached from the main cluster.