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

Dave,

* Dave Johansen (davejohansen(at)gmail(dot)com) wrote:
> 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:
> >> Just to clarify, do you mean *wal_level* set to archive or higher (i.e.
> >> wal_level instead of archive_mode)?

Yes, sorry about the confusion.

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

Yes.

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

Right, that should work.

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

Yeah, note that 150 WAL segments will be like 2.5G, so make sure you
have enough disk space on your WAL volume for them.

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

If you set up the new server as a streaming replica then the data which
is added after the pg_basebackup will be streamed automatically to the
new server, all the way up til the current server is taken offline.

Thanks!

Stephen