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

On Wed, Feb 17, 2016 at 12:49 PM, Dave Johansen <davejohansen(at)gmail(dot)com>
wrote:

> 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.
>

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.

>
>>
>>
>>> 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
>