Skip to content

Latest commit

 

History

History
77 lines (55 loc) · 2.6 KB

postgresql-read-replica.md

File metadata and controls

77 lines (55 loc) · 2.6 KB
title excerpt products
Create a read-only replica of PostgreSQL
Create and use a read-only replica for your services in Managed Service for TimescaleDB
mst

Create a read-only replica of PostgreSQL service

PostgreSQL read-only replicas allow you to perform read-only queries against the replica and reduce the load on the primary server. You can optimize query response times across different geographical locations because the replica can be created in different regions or on different cloud providers. For information about creating a read-only replica using the Aiven client, see the documentation on creating a read replica using the CLI.

If you are running a Managed Service for TimescaleDB [Pro plan](https://docs.timescale.com/mst/latest/about-mst/#service-configuration-plans), you have standby nodes available in a high availability setup. The standby nodes support read-only queries to reduce the effect of slow queries on the primary node.

Creating a replica of PostgreSQL

  1. In MST Portal, click the service you want to create a remote replica for.

  2. In Overview, click Create a read replica.

  3. In Create a PostgreSQL read replica, type a name for the remote replica, select the cloud provider, location, plan that you want to use, and click Create.

When the read-only replica is created it is listed as a service in your project. The Overview tab of the replica also lists the name of the primary service for the replica. To promote a read-only replica as a master database, click the Promote to master button.

Using read-only replica for the service on MST

  1. In the Overview page of the read-only replica for the service on MST, copy the Service URI.

  2. At the psql prompt, connect to the read-only service:

    psql <SERVICE_URI>

  3. To check whether you are connected to a primary or replica node:

    SELECT * FROM pg_is_in_recovery();

    If the output is TRUE you are connected to the replica, and if the output is FALSE you are connected to the primary server.

Managed Service for TimescaleDB uses asynchronous replication, so some lag is expected. When you run an `INSERT` operation on the primary node, a small delay of less than a second is expected for the change to propagate to the replica.