Our PostgreSQL plans that have one or more standby nodes also provide direct access to these standby nodes. Using the standby server nodes for read-only queries allows you to scale your database read operations by moving some of the load away from the primary node to the otherwise mostly idle replica nodes.
With Aiven for PostgreSQL, you can access the replica nodes by using the separate
Replica URI that is shown in the Aiven web console:
Replica URI in a database client application connects to one of the available replica server nodes. Replica node access is available in our Business (primary + one standby) and Premium (primary + two standbys) service plans. In addition, these service plans improve the high availability characteristics by having a standby to fail over to.
If you are running a Premium plan then you will still see a single
Replica URI that will resolve to both of your standbys. Using the
Replica URI, the connection will follow the Round-Robin when deciding which standby to connect to.
If you add a remote read-replica then you will receive a separate
Replica URI for each standby you deploy.
It is worth noting that since the PostgreSQL replication used in Aiven for PostgreSQL is asynchronous, there is a small replication lag involved. In practice, this means that if you do run an
INSERT operation on the primary node, it takes a while (usually much less than a second) for the change to be propagated to the standby and to be visible there.
To start using your read-only replica:
Log in to the Aiven web console and select your PostgreSQL service.
On the Overview page, click Copy next to Replica URI.
Open a connection to the replica by pasting the copied URL:
$ psql postgres://avnadmin:firstname.lastname@example.org: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.
You can the run any read-only query without slowing down the primary server.
To check whether you are connected to a primary or standby node, run the following command:
defaultdb=> SELECT * FROM pg_is_in_recovery();
TRUEif you are connected to the replica, and
FALSEif you are connected to the primary server.