Date: Mon, 25 Jul 2016 12:37:00 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Jerry Sievers <gsievers19@comcast.net>
Cc: pgsql-admin@postgresql.org
Subject: Re: Pg-Upgrade standbys via rsync... and avoid sending
 UNlogged data?
Message-ID: <20160725163700.GZ4028@tamriel.snowman.net>
References: <86d1m5b6bi.fsf@jerry.enova.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="Y3YVtFU9bcHhbVJF"
Content-Disposition: inline
In-Reply-To: <86d1m5b6bi.fsf@jerry.enova.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


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

* Jerry Sievers (gsievers19@comcast.net) wrote:
> Has anyone found a clever way to $subject that doesn't involved
> calculating which are all of the  files unnecessary to send and then
> running rsync with an exclude list?
>=20
> I'm pondering this right now.=20

Certainly an interesting issue to consider.  I have to admit that I
hadn't considered unlogged tables when working out how to do the
hard-link-based approach.

> ISTM for each unlogged table/index/toast  in pg_class where
> relpersistence=3D'u'...
>=20
> rsync --exclude-from... (and exclude-from-file contains)
>=20
> */base/$dbid/$relfilenode
> */base/$dbid/$relfilenode.*
> */base/$dbid/$relfilenode_*
> ...
>=20
> The leading * will match the old/new data directoryes being sent to the
> standby.

You would have to address tablespaces too, if you have any.

> I have among others, a 5TB system  w/about 40G of unlogged data.

Honestly, sending an extra 40G out of 5TB (we're talking less than 1%
here...) might be worth it to keep the process simple.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJXlkAsAAoJEO1sijiDR2RVTLsQAIgUmlJwzdRFAL6cbTfy+73u
4a4E8m2kV5/xALu6vGFeR9kd61JEYUnYLWV7if3y7gyifEFQkBzqX7jYRdC4JkNM
TpCuLjCYe9QmqBjA6m/Bi/yuvlHyGM/hSiF1eT4NldOTeggjV+IuHOF5V0hGFT3L
bzN19PhwNca+uqx8sPNODYFsWOmbOF+5bAvQta+EJYedJFrhO88Vrho7VKWb0nRq
pGsiXRGOUGwsWoFKeIha5YMzLU96XGAFXt2s6g05j509UTZOtTcit5wD1iQTLlGm
xp/g+Ec+AezCx7bsdl5thdUEUoJ6cT/rxAlBm6QaLBzOzDwU6FPtqbanchU2d2B0
BbNenQeoDhnFtWQeLZQ0Q7TLUcTrFI6iH1vp5uKce/wFZlz+ajF0d9Q+NZnBYL3o
Rfps07ln8aPRVR5IrvsvJwDxxXCLHSpNOlqIvUF+wyPLqNJHA5sf0vrj3hQgDfHY
g2lHLcDszxUG/c4HWyNjhORz/VhMLxdEfXNWzSFxkvrK6li8cFiIWWF3f3GIpyqm
DIOmrcqiCastiq1e5jKYxgIVmBojIed5Bbkv8HBn8A+A1e2fHII75G7HH8hofAYN
nXTtU43R4hh9bwlOWWPhi4EqT98qdFtr2+HJFBBQmOayELou0Ih8khNEAkwdnZkK
OEXLih47y/D8KYRz6YJj
=Imd7
-----END PGP SIGNATURE-----

--Y3YVtFU9bcHhbVJF--