Yes so if the slave is behind I need to start over pgbasebackup. I saw according to the documentation this query gives us the replication state. Can somebody tell me if this would be sufficient to know if I need to start over the backup ?
On 11/20/2016 10:37 PM, Subhankar Chattopadhyay wrote:
We are using the database in a cloud setup and the nodes are running in VMs. The slave can fall behind for various reasons as you have stated, like slave is shut down or high write workload.
We don't use replication slot but the wal_keep_segments is high enough for us, 5000 to be exact.
In that case, we can do pg_basebackup only when necessary, like only when slave is behind. We can check that from the query "SELECT state FROM pg_stat_replication WHERE pid <> pg_backend_pid();" Will that be correct way to do it?
maintaining a wal archive, and configuring the slaves so they can recover from it when they are restarted will likely take care of things if they are getting so far behind that 5000 wal segments is insufficient..
I'm not that familiar with pg_stat_replication, I'm not sure what that query is telling you. others can probably chime in with more info on this.