Re: Setting up streaming replication with new server as master?

On Wed, Feb 17, 2016 at 12:49 PM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 9:45 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 11:34 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith@keithf4.com> wrote:

On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen@gmail.com> wrote:

On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix@gmail.com> wrote:

If you specify -X f or more likely -X s, that will cause pg_basebackup to include the WAL files that were written after you started the operation. Since you're setting up a replica, use the -X s option as that's for streaming.

I ran pg_basebackup with -X s and it finished in the middle of the night last night. I would now like to make the switch, but what's the best way to copy over the records that have been inserted since the backup stopped?

The -Xs option just keeps the WAL files that were written during the backup run so that if you restore it, it's brought back up to a consistent state at the point when the backup itself finished.

If you want to be able to bring up a slave from a backup at any point after that backup was complete, you have to keep all the WAL files that have been written since then. This is what is called Point-In-Time Recovery (PITR). I highly recommend you read up on the docs for how WAL files in postgres work and how to use them with backups and slaves. I think that is a key point your missing in understanding how to get a streaming slave set up and working.

http://www.postgresql.org/docs/9.5/static/continuous-archiving.html

Ok, that was a misunderstanding on my part. I had understood that with the -Xs option pg_basebackup would stay online and keep streaming the WAL files until it was turned off. Thanks for the clarification, but on a related note, that would be a really nice feature that would make doing this sort of replication a lot easier.

That feature does currently exist, it's just not part of pg_basebackup, which is why I recommended that you read up on the documentation for PITR in postgres. 

I was referring to the feature being added to pg_basebackup itself. I'm not familiar at all with the code and what adding a feature like this would take, but it already has support for streaming the WAL logs but just exits when the base backup is complete. I was thinking of something along the lines of --continue or --stayalive that would keep the streaming of the WAL logs going even after the base backup completes.

Can almost guarantee you this will never be something pg_basebackup does. 

Look into the archive_command

http://www.postgresql.org/docs/9.5/static/runtime-config-wal.html#GUC-ARCHIVE-COMMAND

or setting up pg_receivexlog

http://www.postgresql.org/docs/9.5/static/app-pgreceivexlog.html

if you need something that continuously keeps a backup of WAL files.