Steven Lembark <lembark@wrkhors.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