Patroni: Expose synchronous replication status through API

[ClaudiuLSH]

Hi,

I started a cluster for testing, made of 3 nodes and I think that the current setup does not allow me to scale reads and backups.

My desired scenario:

1. Master - writes and reads
2. Synchronous slave - reads only for offloading of master
3. Potential synchronous slave - backups only

The current haproxy config and patroni API allows for this:

backend be_db_rw
    option httpchk GET /master
    http-check expect status 200
    ...

backend be_db_ro
    option httpchk GET /
    http-check expect string replica
    ...

But this will result in having one master and 2 read only servers.

I was thinking of adding another key - value to the API response containing the result of:
postgres=# select client_addr, sync_state from pg_stat_replication; client_addr | sync_state -------------+------------ 10.0.0.1 | sync 10.0.0.2 | potential

This is visible only from the current master so I can't simply add it to the API response.
So I was thinking of adding the key client_addr and value sync_state in etcd and after expose them through API. There should be a match of IP so that the value is displayed on the correct server.
This way the haproxy setup for sync and potential slaves will be strait forward.

Maybe I am missing a feature of patroni that allows one to find out which is the sync and potential.
Or maybe there is a better way of doing this.
Ideas?

[CyberDem0n]

Hi.
You are right, currently there is no way to tell which replica is synchronous and which is potential.
I even knew better way to identify replicas (without relying on client_addr). We just need to set application_name in a primary_conninfo parameter.
But... Will it really help you? First of all, you are getting information about replica status asynchronously. At the moment when you got this info it could happen that in sync replica already was changed.
Postgres changes in sync replicas really easy and fast. For example I was running a test cluster with 2 replicas, postgresql1 and postgresql2. postgresql1 was in sync replica. I stopped it and a few moments later started it up.

2016-05-05 21:44:36,613 INFO: no action.  i am the leader with the lock
LOG:  standby "postgresql2" is now the synchronous standby with priority 1
LOG:  standby "postgresql1" is now the synchronous standby with priority 1
2016-05-05 21:44:46,605 INFO: Lock owner: postgresql0; I am postgresql0

As it was expected master almost immediately changed in sync replica to the postgresql2. But after postgresql1 became available it switched back to it.

Well, lets assume that in sync replica is not changing very often and you are reading from the "right" one. But what postgres documentation tells about synchronous replication?
http://www.postgresql.org/docs/9.5/static/warm-standby.html#SYNCHRONOUS-REPLICATION

When requesting synchronous replication, each commit of a write transaction will wait
until confirmation is received that the commit has been written to the transaction log on
disk of both the primary and standby server. 

Synchronous replication follows different purpose, it increases data loss protection, but gives not warranty that you will read exactly the same data as from master.

Unfortunately there is no _easy_ way to scale read workload. Your application should aware of the fact that it could get some old data from the replica (even from in sync replica).

Anyway I like your idea of exposing such information. It could be very useful for example for monitoring.

P.S., there is a better way to identify replicas:

backend be_db_ro
-    option httpchk GET /
-    http-check expect string replica
+    option httpchk GET /replica
+    http-check expect status 200
    ...

We could even think about excluding some replicas from load balancing if they are more then N bytes behind the master (N could be passed as a request parameter). But one should keep in mind that master updates its position in etcd once in a while (once per 10 seconds by default).

[ClaudiuLSH]

Thanks for haproxy update, did not see that url in the code, I just started reading it to be honest :)
Btw, maybe an updated example of haproxy might help others.

For sync slaves, as per postgresql documentation:

synchronous_standby_names - The first named standby will be used as the synchronous standby.
Standbys listed after this will take over the role of synchronous standby if the first one should fail.

What one has to do is to list all servers in the desired order. I am not sure how postgres will react if the first server in the list is the actual master though. But if it works (have to test it) it will overcome the fast switching of sync slave (unless some node fails and comes back online in a short amount of time).

As per my understanding according to: http://www.postgresql.org/docs/current/static/runtime-config-wal.html
If one sets synchronous_commit to on or remote_write, the master will reply to commits only after the slave receives the data as well (even if it was not written to disk yet, it is visible in postgres). Oh and this requires synchronous_standby_names as well obviously. Correct me if I am wrong here.

For what you suggested as implementation, you want to add another parameter to promary_conninfo which allows one to specify to which server to connect? And this way one could simply connect to the master directly and query for slave status. Is this what you are planning? If so I can give it a try today.

[CyberDem0n]

Btw, maybe an updated example of haproxy might help others.

Yeah, documentation is pretty outdated, we need to work on it. But actually we are open to any kind of collaboration, including documentation and providing nice configuration examples.

If one sets synchronous_commit to on or remote_write, the master will reply to commits only after the slave receives the data as well (even if it was not written to disk yet, it is visible in postgres).

remote_write only guaranties that transaction was written on disk on remote site to write ahead log (wal). It will take some time until replica will replay it. In postgresql-9.6 it would be possible to set synchronous_commit to remote_apply. It will do exactly what you want.

When set to remote_apply, commits will wait until replies from the current synchronous
standby(s) indicate they have received the commit record of the transaction and applied
it, so that it has become visible to queries on the standby(s).

But don't forget, that synchronous commit affects performance of the master and setting it to remote_apply globally is not the best idea. I believe such behavior should be controlled by application (it's possible to change value of synchronous_commit at any time), because not every transaction should be applied immediately on replica.

Oh and this requires synchronous_standby_names as well obviously.

Currently the only possible way to enable synchronous commit in Patroni is set synchronous_standby_names to '*' Sure, it's not that flexible as specifying list of standby names divided by comma, but it suits it's purpose. The name of synchronous standby is set as an application_name in primary_conninfo.

For what you suggested as implementation, you want to add another parameter to primary_conninfo which allows one to specify to which server to connect?

First of all it helps to identify node. And later one could use this names instead of '*'

1) application_name is NOT set in primary_conninfo

localhost/postgres=# select usename, application_name, client_addr, sync_state from pg_stat_replication;
  usename   │ application_name │ client_addr │ sync_state 
────────────┼──────────────────┼─────────────┼────────────
 replicator │ walreceiver      │ 127.0.0.1   │ sync
 replicator │ walreceiver      │ 127.0.0.1   │ potential
(2 rows)

2) application_name is set in primary_conninfo

localhost/postgres=# select usename, application_name, client_addr, sync_state from pg_stat_replication;
  usename   │ application_name │ client_addr │ sync_state 
────────────┼──────────────────┼─────────────┼────────────
 replicator │ postgresql2      │ 127.0.0.1   │ sync
 replicator │ postgresql0      │ 127.0.0.1   │ potential
(2 rows)

Basically application_name is matching with the node name (postgresql.name parameter from yml file).

[alexeyklyukin]

Using the node's name in order to identify the synchronous replica is not a good strategy: another replica may step up as the synchronous one if the original one is not available (if you need to exclude a specific replica from the load balancer, there is already a noloadbalance you can tag your excluded replica with).

Would do you think of teaching the GET endpoints to accept parameters, i.e. GET /replica?mode=async in order to pick only non-synchronous replicas, or GET /replica?state=running to connect to patronis with running PostgreSQL database.

This would also help with https://github.com/zalando/patroni/issues/119

[ClaudiuLSH]

@CyberDem0n you are right about this.
I tried looking at alternatives, like combining patroni with citus (which works smoothly) but unfortunately citus does not support all features required by my app.

So back to square one to using only patroni in synchronous mode.
I found a way to play with routers (Django) so I am able to offload some traffic to slaves even if they are not in sync.

@alexeyklyukin how would one tag a replica with noloadbalance? And how would the tag be removed if the replica is not in sync anymore?

[alexeyklyukin]

@ClaudiuPID you just set the noloadbalance in the tags section of the patroni configuration. You cannot remove it dynamically, but if you tag a _specific_ replica (the same one that you've designated as a synchronous one), you don't need to.

[LappleApple]

@CyberDem0n @alexeyklyukin Noted Alex's comment from May 6 about updating/enhancing the docs--can I help you with that? Seems like we could turn this issue into a documentation section.

Other than this potential docs-related task, is there anything else you/@ClaudiuPID need on this issue?

[alexeyklyukin]

Yes, I think the idea about filtering out the GET points based on whether the replica is synchronous or not should be implemented.

[LappleApple]

@alexeyklyukin Added this to my to-do section

[imcitius]

Hi,

Is there any news on this matter?
I'd like to have any way to select async slave (or even set up never-master server) for heavy read load, and for remote datacenter backup.

Thanks.