Pg-Upgrade standbys via rsync... and avoid sending UNlogged data?

From: Jerry Sievers <gsievers19(at)comcast(dot)net>
To: pgsql-admin(at)postgresql(dot)org
Subject: Pg-Upgrade standbys via rsync... and avoid sending UNlogged data?
Date: 2016-07-22 23:29:05
Message-ID: 86d1m5b6bi.fsf@jerry.enova.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(dot)consulting(at)comcast(dot)net
p: 312.241.7800

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Bruce Momjian 2016-07-22 23:43:12 Re: Pg-Upgrade standbys via rsync... and avoid sending UNlogged data?
Previous Message Gilberto Castillo 2016-07-22 17:41:28 Re: [MASSMAIL]Re: High-end PG database configuration help