Use 9.6 With synchronous_commit = 'remote_apply' > On Thu, Oct 13, 2016 at 5:39 PM, Todd Nine <todd.nine@xxxxxxxxx> wrote: > >> Hi all, >> I'm in the process of writing an application/api that will perform >> similar functionality to RDS on Kuberentes using Postgres. For our >> first >> use case, I have the following setup. >> >> Master (0) --(synchronous replica) -> Standby 1 >> >> --(synchronous replica) -> Standby 2 >> >> >> I have the following configuration in my postgresql.conf on the master. >> >> synchronous_standby_names = '1,2' >> >> And the following connections in standby 1 and standby 2, respectively. >> >> >> standby_mode = on >> primary_conninfo = 'host=postgres-toddtest-write port=5432 >> user=postgres >> application_name=1' >> trigger_file = '/tmp/postgresql.trigger.5432' >> recovery_target_timeline=latest >> >> >> standby_mode = on >> primary_conninfo = 'host=postgres-toddtest-write port=5432 >> user=postgres >> application_name=2' >> trigger_file = '/tmp/postgresql.trigger.5432' >> recovery_target_timeline=latest >> >> Replication appears to be working. In my master, I see the following in >> my logs. >> >> LOG: database system is ready to accept connections >> LOG: standby "1" is now the synchronous standby with priority 1 >> LOG: standby "2" is now the synchronous standby with priority 2 >> LOG: standby "2" is now the synchronous standby with priority 2 >> LOG: standby "1" is now the synchronous standby with priority 1 >> >> >> However, when I run the following on my slave nodes, the lag time seems >> enormous. >> >> SELECT now() - pg_last_xact_replay_timestamp() AS time_lag; >> time_lag >> ---------------- >> 00:21:26.33019 >> >> As a result, I have a few questions I can't seem to find the answers to >> in >> the documentation, any help would be greatly appreciated. >> >> >> 1) In this doc, it states ". If the standby is the first matching >> standby, as specified in synchronous_standby_names on the primary, the >> reply messages from that standby will be used to wake users waiting for >> confirmation that the commit record has been received." >> >> https://www.postgresql.org/docs/9.5/static/warm-standby.html >> >> My understanding is that means if Standby 1 has successfully fsynced >> that >> commit to disk, the server will return a response to the client as a >> successful commit. What happens to Standby 2? I'm assuming it's sent >> the >> same WAL entry asynchronously, but wanted to be sure. >> > > I believe you have this correct. I'd still get confirmation on this from > someone else, though. As of 9.6, you also have the option of requiring a > minimum number of synchronous standbys that must respond. Before 9.6, it > just went down the list in order and the first one to respond was all that > was necessary for the master to confirm the transaction. > > https://www.postgresql.org/docs/9.6/static/warm-standby.html#SYNCHRONOUS-REPLICATION > > >> >> 2) How can I validate the replication latency in standbys that are NOT >> the >> current hot standby? For instance, I want to deploy or upgrade the PG >> nodes automatically. I would add more hot standbys, wait for them to >> catch >> up. I would then fail over to one of them as the new master node. Once >> this is complete and working, I'd remove the old master and replicas. >> >> In order to automate 2), I need some way to verify that the standby >> that's >> just been created is up to date, as well as receiving the latest >> traffic. >> >> Thanks in advance! >> Todd >> >> >> > To more reliably get the status of the slaves, you'll want to query from > the master for the byte lag vs querying the slave for the last transaction > replay. If the master is getting no writes, then checking for replay on > the > slave will give a false report that it is falling behind simply because > it's run no transactions. I've explained this in more detail and have some > example queries on my blog > > https://www.keithf4.com/monitoring_streaming_slave_lag/ > > It's still a good monitor to have, though, and I recommend monitoring both > byte lag from the master and replay from the slave. Just set your > monitoring alerts appropriately > -- Saludos, Gilberto Castillo ETECSA, La Habana, Cuba -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
