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

On Wed, Feb 17, 2016 at 9:45 AM, Keith <keith(at)keithf4(dot)com> wrote:

>
>
> On Wed, Feb 17, 2016 at 11:34 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
>> On Wed, Feb 17, 2016 at 8:32 AM, Keith <keith(at)keithf4(dot)com> wrote:
>>
>>>
>>>
>>> On Wed, Feb 17, 2016 at 10:02 AM, Dave Johansen <davejohansen(at)gmail(dot)com>
>>> wrote:
>>>
>>>> On Sat, Feb 6, 2016 at 6:38 PM, John Scalia <jayknowsunix(at)gmail(dot)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