On Wed, Apr 3, 2019 at 10:10:54AM -0400, Tom Lane wrote: > --- *carefully* --- to find out how to identify the right physical > files. > > A few foot-guns I can think of: > > * Making an identically-declared table might be more complicated than > you'd think, if the table has had any ALTERs done to its rowtype over > its lifetime (ALTER DROP COLUMN is a particularly critical bit of > history here). A good way to proceed is to see what > "pg_dump -s --binary_upgrade" does to recreate the table. > > * Shut down the postmaster while doing the actual file movement, > else you'll get burnt by cached page copies. > > * Don't forget to move all the associated files, including multiple > segment files (I'm sure you have a lot, if this table is big enough > to be worth troubling over), and FSM and VM files. > > * The indexes on the table also need to be moved through the same > type of process. Uh, there is also pgclass's relfrozenxid and relminmxid that have to be preserved, plus you have to update the new database's pg_database row if its datfrozenxid and datminmxid are higher than the old database's. Fundamentally, you have to walk through each step pg_upgrade does to see if it applies, and use pg_dump in --binary-upgrade mode. pg_upgrade does cluster-level stuff (which would not apply), database-level stuff (which might), and heap/index level stuff. It would be an interesting exercise for someone to outline all the steps necessary. This is not for the faint of heart. ;-) -- Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription +
