From: Jerry Sievers <gsievers19@comcast.net>
To: pgsql-admin@postgresql.org
Subject: Pg-Upgrade standbys via rsync... and avoid sending UNlogged data?
Date: Fri, 22 Jul 2016 18:29:05 -0500
Message-ID: <86d1m5b6bi.fsf@jerry.enova.com>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/23.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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?

I'm pondering this right now. 

ISTM for each unlogged table/index/toast  in pg_class where
relpersistence='u'...

rsync --exclude-from... (and exclude-from-file contains)

*/base/$dbid/$relfilenode
*/base/$dbid/$relfilenode.*
*/base/$dbid/$relfilenode_*
...

The leading * will match the old/new data directoryes being sent to the
standby.

MAster upgrade is done using hard-links and the rsync method generally
as per the pg docs.

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

The rsync method of upgrade takes only several seconds during a snapshot
based test however as the snap was off a live system, the crash recovery
startup trunc'd all the unlogged data.

This will not be the case during the real upgrade and I just as soon
avoid sending the unlogged data only to have it trunc'd away as soon as
the standby is started after the upgrade. 

Thanks

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net
p: 312.241.7800


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin