Date: Fri, 5 Feb 2016 15:27:29 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Dave Johansen <davejohansen@gmail.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Setting up streaming replication with new server as
 master?
Message-ID: <20160205202729.GZ3331@tamriel.snowman.net>
References: 
 <CAAcYxUdTYWFdUOhiNzBv290YmeYgAv4iqh4gyiyBW3XgcCmjSw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="HIPflPGvV5kKjZ73"
Content-Disposition: inline
In-Reply-To: 
 <CAAcYxUdTYWFdUOhiNzBv290YmeYgAv4iqh4gyiyBW3XgcCmjSw@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--HIPflPGvV5kKjZ73
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* 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.
>=20
> 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.

Thanks!

Stephen

--HIPflPGvV5kKjZ73
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJWtQWxAAoJEO1sijiDR2RVy50P/0nURlYxZAc9rg5VuCD/1mas
LhDQgIj5HS3HF7ykz/jRIDC8mj7H5F3psJwtOFiZPJZA+VINv/84I5GRTZ8O7k7v
DlLuUg3vV+WIa06ULAbHBp+Tb1WU0yp1SVW410kR5U8jlFPLDFEZ+Q4vC9pWNnBH
kV+6/cf3ciFtgvRpmPw/rWZjoEMRKj5dRSiljeJDpXKAMCBz5KzPveI73N93UuuN
eqaNvXgqE2PqqqadnvlTyb2bFnlu3oyRPuG6fsgmoVz5A7DtTNjW8mLDFskOkRbO
bx7HW8oJC8ATDNock5xxoMbWJx4R4LbG4mRWwTsSQZLXGKOPxIX1cmcFLc1tIgXh
WxN2pRI5gg61qdz/ro1M07UzO5VrhrJhPbx14GDSHZbVYUlhpzz7ix3OzOo4hRjT
b88Czsdw6TNRDDsAXbSDxnpQDZ9OHPQpQTrEbf6otAm2SwDlM+I43qqfYUz1NXb1
5/732jfL4Ar4C1pUMDGqqCffnl6ac7Ezt3UEwNGAzl+nqQ2fTt7iuamTRSDJJJJN
WSMASe89Vc06PmG0a8NNcwqRfyBzzuibblHp95ecckjkCRpB10ru0xR8bo7DrNjN
HWYqYxHWAcwFEItupmawQzbiywfQEcykm9WPRIVWrXQQ8E+nZPBiFFhTnQaaKaYb
ubW1c8YIxE2PuvaQkwKV
=gXxZ
-----END PGP SIGNATURE-----

--HIPflPGvV5kKjZ73--