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