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.

Note

If you are running a Managed Service for TimescaleDB Pro plan, 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.

  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.

  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.

Note

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.

Found an issue on this page?Report an issue or Edit this page in GitHub.