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

On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba <shreeyansh2014(at)gmail(dot)com>
wrote:

>
>
> On Saturday, February 6, 2016, Dave Johansen <davejohansen(at)gmail(dot)com>
> wrote:
>
>> On Fri, Feb 5, 2016 at 2:09 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:
>>
>>> * Dave Johansen (davejohansen(at)gmail(dot)com) wrote:
>>> > On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost(at)snowman(dot)net>
>>> wrote:
>>> > > * Dave Johansen (davejohansen(at)gmail(dot)com) wrote:
>>> > > > So will pg_basebackup keep copying the new data that is added over
>>> the
>>> > > > weekend until I tell it to stop? If so, then on Monday, can I close
>>> > > things
>>> > > > down, wait for the WAL to finish, swap the roles of the 2 server
>>> and then
>>> > > > restart?
>>> > >
>>> > > No, pg_basebackup will take a snapshot of the current system,
>>> > > essentially. You can set up the new system to connect to the current
>>> > > system, using recovery.conf, to pull the WAL records and apply them
>>> to
>>> > > the new server.
>>> > >
>>> >
>>> > So do I set that up before running pg_basebackup?
>>> >
>>> > Or in other words, pg_basebackup will get all of the existing data and
>>> then
>>> > the setup using recovery.conf will get the new data?
>>>
>>> Right. Note that to use pg_basebackup or to have a replica connect to
>>> the existing system, you need to have archive_mode set to archive or
>>> higher (and you should at least do hot_standby, really), and have
>>> max_wal_senders set higher than 0 (I tend to set it to about 3, just
>>> because it uses little in the way of resources, etc). If you have a
>>> high transaction rate, you might also want to set wal_keep_segments up a
>>> bit.
>>>
>>
>> Just to clarify, do you mean *wal_level* set to archive or higher (i.e.
>> wal_level instead of archive_mode)?
>>
>> One final question, step #5 in "How to Use" (
>> https://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use )
>> makes it sounds like having wal_keep_segments at a high enough level means
>> that archive_mode being on isn't necessary. Is that a correct understanding
>> of the description there?
>>
>> Thanks a ton for all the help!
>> Dave
>>
>
>
>
> Hi Dave,
>
> If you are using PostgreSQL version greater than 9.1 then you can go for
> pg_basebackup and create recovery.conf in backup .
>

I'm using 9.2 on RHEL 7.2, so it sounds like that's a good option.

> I suggest you to go for wal to wal replication so that the archive
> overhead will reduce.
>
> Put wal_keep_segment=150.
> Also
> Wal_level = hot_standby
>
> Not necessary to enable archive_mode and archive_command.
>

Ok, I'll go with that and thanks for the clarification.

> Once the replication comes in sync you can take down time in off peak
> hours and promote the slave.
>

Is there a recommended way to migrate the data that was added after the use
of pg_basebackup happened and before the processing was taken offline?