PostgreSQLWhen you build replication in PostgreSQL using Streaming replication, you cannot perform writes to a standby node; only reads. This way, you could offload reads or reporting queries to standby servers and send writes to master. Additionally, starting from PostgreSQL 10, libpq  and psql clients could probe the connection for a master and allow connections to a master for read-write or any node for read-only connections automatically.

For example, consider three database nodes – Server_A, Server_B, and Server_C in replication using streaming replication, with Server_A being the Master/Primary node. You could specify all three servers in a connection string and request the connection to be redirected to a read-write node only, which is Server_A in this scenario. If a failover or a switchover happened to Server_B, the read-write connections will be automatically redirected to Server_B. To understand this in detail, let us see a simple scenario in action.

I have set up a three-node replication cluster using streaming replication with the following roles.

Now, let us use psql with all the three IPs specified in the connection string. We would, however, use target_session_attrs this time to connect to a master node.

Connecting to Master Using Read-Write Mode

Connecting to any Server for Reads

Please note that the server that is first in the list is automatically connected when you used target_session_attrs as any .

If the server that is first in the list is not reachable, the driver tries to connect to the next server in the list for reads. So, a reads connection would never fail when you have multiple standbys and at least one of the database nodes is reachable while using target_session_attrs as “any”.

An important point to note is that the driver might take additional time in connecting to each node in the list to determine if it is a master. Let’s say that the server: 192.168.70.10 is no longer a master and 192.168.70.20 (second in the list of servers in the connection string) is the new master accepting writes. When you specify that the connections should go to a read-write node, the driver checks if the first server in the list accepts writes and then connects to the second server. If the first server is not reachable, then you may experience further delay. However, this is still a seamless failover as you do not have to disturb the application during this switchover.

Let us say that you use Python or PHP to connect to PostgreSQL. As the application interfaces for Python, PHP, and several other programming languages use libpq as the underlying engine, you could use multiple IPs in the connection string and request the connections be redirected to a read-write or any node.

Below is an example to achieve this with Python. I have a written a simple python script and specified target_session_attrs as "read-write" by passing multiple IPs to the host. Now, when I execute the script, it confirms the IP connected to (192.168.70.10 is a master here) and shows that the server is not in a recovery mode.

I could similarly use PHP to connect to postgres and specify that the connections should only be directed to a master node as seen in the following example.

An important point to note is that the clients are able to achieve this because they are using libpq that belongs to PG10 or later.

We have discussed that you might expect some slowness due to multiple hops while connecting to an appropriate master server, but this approach still helps for a seamless application failover. And we have discussed the built-in mechanism available with Community PostgreSQL by default. In the next blog post, Jobin Augustine will be talking about using HAProxy (Open Source) for achieving a much more robust and reliable way to perform a seamless application failover with PostgreSQL.