Re: Setting up streaming replication with new server as master? - Mailing list pgsql-admin

On Saturday, February 6, 2016, Dave Johansen <davejohansen@gmail.com> wrote:

On Fri, Feb 5, 2016 at 2:09 PM, Stephen Frost <sfrost@snowman.net> wrote:

* Dave Johansen (davejohansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 1:54 PM, Stephen Frost <sfrost@snowman.net> wrote:
> > * Dave Johansen (davejohansen@gmail.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.