Date: Wed, 24 Feb 2016 13:10:04 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Keith <keith@keithf4.com>
Cc: "drum.lucas@gmail.com" <drum.lucas@gmail.com>,
	Scott Whitney <scott@journyx.com>,
	"David G. Johnston" <david.g.johnston@gmail.com>,
	"pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Subject: Re: Incremental Refresh - PostgreSQL 9.2
Message-ID: <20160224181004.GH3127@tamriel.snowman.net>
References: 
 <CAE_gQfUqvmewj91Sg81Orc7S+=aO0xG-xftMB4UycByVEdWHZw@mail.gmail.com>
 <CAKFQuwYFoEwQOXwvOuiqx40mJb+T2P3LKKXcTJNKGsX0M+LZ=w@mail.gmail.com>
 <e0121fj0w32l77ofwjfb14i2.1456267414369@email.android.com>
 <CAE_gQfXJx81wGXRYEO2jUTLTLkvkWvCiE6bRUxjQAOG08zPVGQ@mail.gmail.com>
 <ewwmlvageqrkn3rdthfso2nq.1456277100577@email.android.com>
 <CAE_gQfVqK3VZzQGUnK0aQd55VmQVRGQ3c2v4vQ=NXuQjotUM-w@mail.gmail.com>
 <CAE_gQfUb90_aD3643SLNT7fMO4z+43auVp7nN7Sb7g7zrTKO5g@mail.gmail.com>
 <CAHw75vu9hPUp6ACaizxUGAY1J96JdKLXdjeK37LiBLAUrZbJmw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="it/zdz3K1bH9Y8/E"
Content-Disposition: inline
In-Reply-To: 
 <CAHw75vu9hPUp6ACaizxUGAY1J96JdKLXdjeK37LiBLAUrZbJmw@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

Keith,

* Keith (keith@keithf4.com) wrote:
> However, once you restore that backup and bring it up as a standalone
> database instance, you can no longer use the WAL stream from the original
> instance to "replay" those changes. This is also the case with any sort of
> rsync'ing of the data files from one instance to another.

Right, but if you don't mind throwing away the changes which were made
to the restored server (the test server) then you can use rsync (if the
source database is shut down) to re-sync the restored server without
having to re-copy everything (I'd strongly recommend using the CRC
option to rsync).  The same is possible with pgBackRest, which allows
you to keep the source database online (pgBackRest will check that the
hashes match between the backup and the restored system when doing
incremental restores, there's no need to explicitly ask for that as
there is with rsync).

> You may be better off looking into a logical replication solution to copy
> your data to a testing system if it needs to be a standalone system. That
> is not currently built into Postgres, but it's on track for possible
> inclusion with 9.6. In the mean time look into tools such as Mimeo or
> Bucardo.
>=20
> https://github.com/omniti-labs/mimeo
> https://bucardo.org/wiki/Bucardo

Right, and there's also Slony.  These are useful if the requirement is
that changes to the test/restored server be persisted.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJWzfH8AAoJEO1sijiDR2RVT+wP/0v8TbQUvMaTdrAJHKmZUZGZ
4E2lSc6U8QzLd1tDW+Kpnrv4cNz98TiCk3fxdp+x5LQR3kX6KRO/jMNtUY0sCTDO
9Rn7H7aLsfUeLwBYDepdv2O2g8y/PUUIUqqbCsJguIVcgODcXuhSxupUxiilG411
RbsJoI/HnQ9iEBONQ5bGVb2JZ8EjHozI6Nn03Z+8c6cQ55ZBFVzI6Ca4wgw6q2c3
P3AM34vgq7uJvAl9zE8aWr3+m+7WetJ5Lae66/i/Kh7wZP/GRjo6QwBPKa6zUnNc
CXX4CA5UBTKov8D7l7Yr/lnMUqT8EpeJ8cYfQJ/rH12qEX6vuW0588jMAQy+yuuh
6DlR9S4sbvFneLUsXyjK5Zn7HQBx3vV8FzRASh7KHkc5UIs3mIqqjCbyT6Rb+qfX
hDr6kp1ugR+HTNdfGLYfmZ8StaxPGeKJBzEddA7GePQ8W/xia7dU3mFzGf6ovN9Z
2mS9j3BLACZ+snXyFne+QwjKLLCRzQ+vpdfgpwL8dzWrIpEsAZrkG9qIcZeq71HO
CcsnI9j+VzO+LVhWKSP0boumdlsqA7XkKVk4+1aDrrZatkOEnNY8uBcfIPRY3qDx
xqLJBm3kGI+Y9hqtS5fAI5kdBWzfPc8NS08L61xkay7DpgsR3y32XvQHaJbw7t8j
2J4fl/JVNs77Wcwk0pyp
=MRfL
-----END PGP SIGNATURE-----

--it/zdz3K1bH9Y8/E--