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

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
> <javascript:_e(%7B%7D,'cvml','sfrost(at)snowman(dot)net');>> wrote:
>
>> * Dave Johansen (davejohansen(at)gmail(dot)com
>> <javascript:_e(%7B%7D,'cvml','davejohansen(at)gmail(dot)com');>) wrote:
>> > On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost(at)snowman(dot)net
>> <javascript:_e(%7B%7D,'cvml','sfrost(at)snowman(dot)net');>> wrote:
>> > > * Dave Johansen (davejohansen(at)gmail(dot)com
>> <javascript:_e(%7B%7D,'cvml','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 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.

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

Thanks and Regards

Ajinkya Bangale
Database Administrator
Shreeyansh Technologies
Www.shreeyansh.com