Restart replicated slave procedure

Joseph Kregloh <jkregloh@sproutloud.com> writes:

Hi,

Currently I am doing asynchronous replication from master to
slave. Now if I restart the slave it will fall out of sync with the
master. Is there a correct procedure or set of steps to avoid this? I
am looking for best practices or suggestions. Whenever my slave fell
out of sync I would either issue a new pg_base_backup() or set the
master to pg_start_backup() do an rsync and stop using
pg_stop_backup(). If there is a way to avoid any of that, for example
pause replication to hold all the wal files until the replicated slave
comes back and then release them once the replicated slave is up.

I apologize if this question has already been asked. I did some searching beforehand.

See the manual and read up on the 2 GUCs; archive_command and wal_keep_segments.

wal_keep_segments lets you hold a configurable number of WAL segments
back and buy some more time till you have to resync the stand bys.

Setting archive_command to '' or something like '/bin/false' lets you
delay archiving forever till you change them back again and/or fill
whatever file system pg_xlog writes to :-)

Thanks,
-Joseph Kregloh

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

Joseph Kregloh <jkregloh@sproutloud.com> writes:

Hi,

Currently I am doing asynchronous replication from master to
slave. Now if I restart the slave it will fall out of sync with the
master. Is there a correct procedure or set of steps to avoid this? I
am looking for best practices or suggestions. Whenever my slave fell
out of sync I would either issue a new pg_base_backup() or set the
master to pg_start_backup() do an rsync and stop using
pg_stop_backup(). If there is a way to avoid any of that, for example
pause replication to hold all the wal files until the replicated slave
comes back and then release them once the replicated slave is up.

I apologize if this question has already been asked. I did some

searching beforehand.

See the manual and read up on the 2 GUCs; archive_command and
wal_keep_segments.

Thanks, i'll read into this some more.

wal_keep_segments lets you hold a configurable number of WAL segments
back and buy some more time till you have to resync the stand bys.

Setting archive_command to '' or something like '/bin/false' lets you
delay archiving forever till you change them back again and/or fill
whatever file system pg_xlog writes to :-)

So disabling the archive_command by setting it to and empty string or
/bin/false will effectively pause log shipping? When I re-enable the
archive command will it continue where it left of when the archive_command
was "disabled"?

On Fri, Aug 22, 2014 at 3:47 PM, Jerry Sievers <jerry.sievers@comcast.net>
wrote:

Yes, changing archive_command to '' or something that returns false will
let you queue the WALs until reverting the change.

I am assuming you run a version where the archive_mode setting exists
which will be set to 'on' and left that way.

Yep, I run version 9.3 on all the environments.

On Sat, Aug 23, 2014 at 2:18 AM, Joseph Kregloh <jkregloh@sproutloud.com>
wrote:

On Fri, Aug 22, 2014 at 2:21 PM, Jerry Sievers <gsievers19@comcast.net>
wrote:

Joseph Kregloh <jkregloh@sproutloud.com> writes:

Hi,

Currently I am doing asynchronous replication from master to
slave. Now if I restart the slave it will fall out of sync with the
master. Is there a correct procedure or set of steps to avoid this? I
am looking for best practices or suggestions. Whenever my slave fell
out of sync I would either issue a new pg_base_backup() or set the
master to pg_start_backup() do an rsync and stop using
pg_stop_backup(). If there is a way to avoid any of that, for example
pause replication to hold all the wal files until the replicated slave
comes back and then release them once the replicated slave is up.

I apologize if this question has already been asked. I did some

searching beforehand.

See the manual and read up on the 2 GUCs; archive_command and
wal_keep_segments.

Thanks, i'll read into this some more.

wal_keep_segments lets you hold a configurable number of WAL segments
back and buy some more time till you have to resync the stand bys.

Setting archive_command to '' or something like '/bin/false' lets you
delay archiving forever till you change them back again and/or fill
whatever file system pg_xlog writes to :-)

So disabling the archive_command by setting it to and empty string or
/bin/false will effectively pause log shipping? When I re-enable the
archive command will it continue where it left of when the archive_command
was "disabled"?

AFAIK, disabling archive_command will result on accumulated wal files on
xlog dir on master. And when You re-enable the archive_command, it will
continue where it left of. It has the status of last archived wal files.
Check on PGDATA/pg_xlog/archive_status/

Thanks,
-Joseph Kregloh

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800

--
Regards,

Soni Maula Harriz