Aiven PostgreSQL read-only replicas

Aiven PostgreSQL services allows read-only replica access to all of our PostgreSQL plans that have one or more standby nodes. Utilizing the standby server nodes for read-only queries allows you to scale your database reads by moving some of the load away from the master server node to the otherwise mostly idle replica nodes.

What are master and standby nodes?

PostgreSQL master node is the primary server node that processes SQL queries, makes the necessary changes to the database files on the disk and returns back the results to the client application.

  • Having a standby node typically reduces the data loss window in disaster scenarios
  • Restoring the database back to operation is quick by a controlled failover in case of failures, as the standby is already installed, running and in-sync with the data
  • Standby nodes can be used for read-only queries to reduce the load on the master server

What is the difference between having 0, 1 or even 2 standby nodes?

Aiven offers PostgreSQL plans with different number of standby server nodes in each:

  • Business plans have one master node and one standby node
  • Premium plans have one master node and two standby nodes

Using standby nodes for read-only queries

Standby nodes are also useful for distributing the load away from the master server. In Aiven the replica nodes can be accessed by using the separate “Read-only replica URL” visible in the Aiven web console:

Replica Usage

To start using your read-replica find its database URL and after that you can connect to it by copying the Read-only replica URL:

$ psql postgres://avnadmin:foo@replica.demopg.demoprj.aivencloud.com:10546/defaultdb?sslmode=require
psql (9.6.1, server 9.6.1)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)Type “help” for help.
defaultdb=>
defaultdb=> SELECT * FROM pg_is_in_recovery();
pg_is_in_recovery
— — — — — — — — — -
t
(1 row)

Try PostgreSQL 9.6 for free in Aiven

Remember that trying Aiven is free: you will receive US$10 worth of free credits at sign-up which you can use to try any of our service plans. The offer works for all of our services: PostgreSQL, Redis, InfluxDB, Grafana, Elasticsearch and Kafka!

Your database in the cloud, www.aiven.io

Your database in the cloud, www.aiven.io