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

Steven Lembark <lembark(at)wrkhors(dot)com> writes:
> Trying to find a way of moving a large table between databases
> in the same cluster. There is not sufficient space to copy the
> contents -- the dedicated tablespace that fits the beastie is
> on an 80% full disk.

> Given that the two databases live in the same cluster and have
> the owner & the tablespace in common, is there any way to move
> the contents without a dump & reload?

In principle you could do that; it's more or less the same thing that
pg_upgrade --link does. But doing it by hand is not officially supported
and there are multiple ways to shoot yourself in the foot. Basically
the idea is to create an identically-declared table in the target
database, and then swap the physical files of the two tables. Read

https://www.postgresql.org/docs/current/storage.html

--- *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.

I'd strongly counsel practicing on a test setup before you try to
do this to your live data.

Oh: and you have a backup, I trust.

regards, tom lane