Обсуждение: Help with synchronous replication automation
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."
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.
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
On Thu, Oct 13, 2016 at 5:39 PM, Todd Nine <todd.nine@gmail.com> 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 2I 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 = onprimary_conninfo = 'host=postgres-toddtest-write port=5432 user=postgres application_name=1'trigger_file = '/tmp/postgresql.trigger.5432'recovery_target_timeline=latest standby_mode = onprimary_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 connectionsLOG: standby "1" is now the synchronous standby with priority 1LOG: standby "2" is now the synchronous standby with priority 2LOG: standby "2" is now the synchronous standby with priority 2LOG: standby "1" is now the synchronous standby with priority 1However, 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.33019As 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." 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
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
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
Use 9.6 With synchronous_commit = 'remote_apply' > On Thu, Oct 13, 2016 at 5:39 PM, Todd Nine <todd.nine@gmail.com> 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