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