Re: pg_basebackup on slave running for a long time

Hi John,

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?

On Mon, Nov 21, 2016 at 11:42 AM, John R Pierce <pierce(at)hogranch(dot)com> wrote:
> On 11/20/2016 10:00 PM, Subhankar Chattopadhyay wrote:
>>
>> Thanks for reply. In situations where slave is behind master, if I
>> don't start over, will it catch up automatically?
>> I am using 9.4 version.
>
>
> it should stay within a few seconds under normal conditions. why is it
> falling behind, is your write workload too high for the speed of the
> connection between the hosts? or is the slave shut down for some period of
> time?
>
> If the slave is intermittently offline, and if you're using replication
> slots (that was a new feature in 9.4), then the master will hold a queue of
> pending data as long as is needed until the slave catches up again.
>
> Alternately, you can increase wal_keep_segments for a long enough interval
> to cover the worst case time the slave is offline, or you can implement a
> wal archive that the slave can recover from when resuming streaming
> replication.
>
> See https://www.postgresql.org/docs/9.4/static/warm-standby.html for
> discussions of these various options.
>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--

Subhankar Chattopadhyay
Bangalore, India