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

On Fri, Feb 5, 2016 at 1:27 PM, Stephen Frost <sfrost(at)snowman(dot)net> wrote:

> * Dave Johansen (davejohansen(at)gmail(dot)com) wrote:
> > We would like to start using streaming replication, but the catch is that
> > we want the new server to be the master. The server is still in use and
> > receiving new data, so we would like to minimize down time, and I would
> > prefer to be able to copy all, or at least the large majority of the data
> > to the new server before doing the final sync to make it the master.
> >
> > Will pg_basebackup work?
>
> Partially.
>
> > Can it be run multiple times? (i.e. start it now to copy all of the data
> > over the weekend and then a second time on Monday before doing the
> switch)
>
> No.
>
> > Do I need to manually copy the files with rsync?
>
> I wouldn't suggest rsync, unless you do it when the database is shut
> down and you checksum the files.
>
> > What's the recommended way to handle this?
>
> I'd suggest pg_basebackup and then set up the new system as a follower
> of the primary using a recovery.conf. When you shut down the current
> server, all of the WAL changes should be streamed over to the new server
> and then you can trigger it to become a read/write master, once it has
> the last of the WAL from the current server, and then you can just
> redirect new connections to it, or flip it into place by giving it the
> new IP, etc.
>
> If you want to do actual incremental backups as you were thinking
> pg_basebackup might do, you could use pgBackRest, which does support
> that.
>

I'm not really worried about support incremental backups at the moment, but
just want to be able to start the copy of data now (it will probably take a
good chunk of the weekend) and have it be ready to do a quick change on
Monday.

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?

Thanks,
Dave