Re: Setting up streaming replication with new server as master? - Mailing list pgsql-admin

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.

 

 

I have run into another issue now though. I'm trying to start up the server after running pg_basebackup and it keeps timing out. Do I have something setup incorrectly? Or what is causing this issue?

It's probably replaying the WAL files to bring itself into a consistent state. If you're using the service options of the OS to start/stop the database it may have a timeout period shorter than the replay takes. I know that is an issue on debian/ubuntu sometimes. Monitor the postgres log files themselves to see where things are at and give it a few minutes to start up and see how it goes. If it's unable to start at all, the log files will tell you that as well. 

Yes, the WAL logs are being replayed and it appears that systemd is timing out and stopping the startup process. I adjusted the systemd parameters to not timeout so quickly and it appears to have resolved the issue.

Thanks for all of the help,

Dave