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

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

>
>
> 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 agree that continuous archiving can be used to enable replication on a
live database and is the best solution going with current versions of
Postgres. However, adding this feature would simplify scenarios where
someone is bringing replication online with a live database but doesn't
want to run continous archiving long term (my case).

Basically, this feature would mean that enabling replication on an existing
live database could be completed solely with pg_basebackup and without
having to mess with the configuration after the fact.