Re: pg_basebackup on slave running for a long time

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.

--
john r pierce, recycling bits in santa cruz