Date: Sat, 6 Feb 2016 22:00:37 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Dave Johansen <davejohansen@gmail.com>
Cc: Shreeyansh Dba <shreeyansh2014@gmail.com>,
 "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Setting up streaming replication with new server as
 master?
Message-ID: <20160207030037.GL3331@tamriel.snowman.net>
References: 
 <CAAcYxUdTYWFdUOhiNzBv290YmeYgAv4iqh4gyiyBW3XgcCmjSw@mail.gmail.com>
 <20160205202729.GZ3331@tamriel.snowman.net>
 <CAAcYxUcPNa_c_dQd_-2Ez6HmntLkqOmNohag_EbKVDgV372SzA@mail.gmail.com>
 <20160205205456.GA3331@tamriel.snowman.net>
 <CAAcYxUe7zkKz6DSeqW88mPv4BasJnWwO2APnbU8=S20sSyTDMw@mail.gmail.com>
 <20160205210928.GB3331@tamriel.snowman.net>
 <CAAcYxUdVPaubLovuB_ZA8yRwgD3sO6bXTspCModDYECA6_mtzw@mail.gmail.com>
 <CAGDYbUP_FSweNn=_t6qijJqPG5_88zzHTL_e+yi3mYa6HNbPLg@mail.gmail.com>
 <CAAcYxUeTGoOd0Pnz4ts0XeRSFdju_a57=dnQLJX1tikC1vc2Kg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
 protocol="application/pgp-signature"; boundary="7AAf1pfr5RnNeEo0"
Content-Disposition: inline
In-Reply-To: 
 <CAAcYxUeTGoOd0Pnz4ts0XeRSFdju_a57=dnQLJX1tikC1vc2Kg@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

Dave,

* Dave Johansen (davejohansen@gmail.com) wrote:
> On Fri, Feb 5, 2016 at 8:29 PM, Shreeyansh Dba <shreeyansh2014@gmail.com>
> wrote:
> > On Saturday, February 6, 2016, Dave Johansen <davejohansen@gmail.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 m=
eans
> >> that archive_mode being on isn't necessary. Is that a correct understa=
nding
> >> 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 .
>=20
> 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=3D150.
> > Also
> > Wal_level =3D hot_standby
> >
> > Not necessary to enable archive_mode and archive_command.
>=20
> 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.
>=20
> Is there a recommended way to migrate the data that was added after the u=
se
> 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

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

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

iQIcBAEBCgAGBQJWtrNVAAoJEO1sijiDR2RV5psP/2N3rZ61+W6iQ2WyZRndRN1Y
p9jvmXDgCtOA/v0fmzgpMyecJVoMC8hIHqfxiWuWe5VkuKKRqsdV1b7CqLT2UT49
6cwkR0Sqeoa0t9pJCruAigrc4V90w0Kd1f6OcxEuUSIyvggNlFJuzj7F02LpLAE9
GoNGXHVs0iRaPeQkyUVH/nejCvc7quVEbKl37gzE7hrNMP17Ft6NPvRviWHCDoyd
CwG/McmvsBfPfW/wvmUA5RY0nIHB+QIR3AmbWQT2hgjR+N4nooXTTwfZe8heeJFV
GpEZESgy0AxTvSfEC6bAlHd2cMbbT4CGggOj0yg3DokQnLjV/c7RJiEUFZCn6IOl
WQwn6Wb46qB4jNfeOFTfRgpDWBPlXkagHL8Gs0ZU6Np2ooGtPUS/eS6w4vgI6373
ax2UUiGrSxJf+CG7OWRWdTv7nYtGNLapw8AcYvXELdk1G6/JMYphGZxOFWfRaIvQ
ex78imMw2eUy86McIOd3f+zOLA2LhQQ3hCmfSeJqwoZ0th6nqA2eKkfjg67rmt88
mEgsC+8W27hWMAkKfPL29RHQMEPy1L4z4HZ3B2InTsziT6XOrKK92IP1YOyceHDX
2giIHXBTe/o8fSBUWskl10TGSCu7OSNJLDd9vv5r1LsjgOsJannGBJAP6Rw9W0Gu
55gHZ4ujl0fMLWeOtD05
=Y2zq
-----END PGP SIGNATURE-----

--7AAf1pfr5RnNeEo0--