Re: Move vs. copy table between databases that share a tablespace?

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(at)momjian(dot)us> 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 +