Thread: Move vs. copy table between databases that share a tablespace?
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? Thanks -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222 lembark@wrkhors.com +1 888 359 3508
On 4/3/19 8:18 AM, Steven Lembark wrote: > 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? COPY TO STDIN and pipe that through ssh to another server? -- Angular momentum makes the world go 'round.
On Wed, 3 Apr 2019 08:33:54 -0500 Ron <ronljohnsonjr@gmail.com> wrote: > On 4/3/19 8:18 AM, Steven Lembark wrote: > > 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? > > COPY TO STDIN and pipe that through ssh to another server? That makes a copy. There is no place to store two copies of the full data on disk. I'm basically trying to perform the moral equivalent of what "mv" does in the filesystem: re-link the inode and remove the old inode without duplicating the data. Since the tablespace is visible to both I would get that there is some way to alter table... that allocates the space on disk to the new database w/o having to unload all 400M rows -- if it comes down to that I'll have to copy it into xz and load it back in the new database. -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222 lembark@wrkhors.com +1 888 359 3508
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
On 4/3/19 8:39 AM, Steven Lembark wrote: > On Wed, 3 Apr 2019 08:33:54 -0500 > Ron <ronljohnsonjr@gmail.com> wrote: > >> On 4/3/19 8:18 AM, Steven Lembark wrote: >>> 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? >> COPY TO STDIN and pipe that through ssh to another server? > That makes a copy. There is no place to store two copies of the > full data on disk. Through a pipe to a different server. > I'm basically trying to perform the moral equivalent of what "mv" > does in the filesystem: re-link the inode and remove the old inode > without duplicating the data. > > Since the tablespace is visible to both I would get that there is > some way to alter table... that allocates the space on disk to the > new database w/o having to unload all 400M rows -- if it comes down > to that I'll have to copy it into xz and load it back in the new > database. > -- Angular momentum makes the world go 'round.
> Through a pipe to a different server. No access to a server w/ sufficient space. Looks like a compressed dump + restore... Thanks -- Steven Lembark 3920 10th Ave South Workhorse Computing Birmingham, AL 35222 lembark@wrkhors.com +1 888 359 3508
On 4/3/19 7:28 AM, Steven Lembark wrote: > >> Through a pipe to a different server. > > No access to a server w/ sufficient space. Am I correct in assuming that the long term goal is to create more space? Otherwise I am not sure what the below is going to get you? > > Looks like a compressed dump + restore... > > Thanks > > -- Adrian Klaver adrian.klaver@aklaver.com
I wrote:
> Steven Lembark <lembark@wrkhors.com> writes:
>> 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.
Actually, thinking about that a bit harder: there's one aspect of
what pg_upgrade does that's really hard to control from userspace,
and that's forcing tables to have the same OIDs as before. In this
context, that means you're probably out of luck if the table has a
TOAST table, unless the TOAST table is empty. There wouldn't be
any good way to keep TOAST pointers valid across the move.
(PG v12 will make that easier, but that's no help to you today.)
regards, tom lane
On 2019-Apr-03, Tom Lane wrote: > I wrote: > > Steven Lembark <lembark@wrkhors.com> writes: > >> 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. > > Actually, thinking about that a bit harder: there's one aspect of > what pg_upgrade does that's really hard to control from userspace, > and that's forcing tables to have the same OIDs as before. In this > context, that means you're probably out of luck if the table has a > TOAST table, unless the TOAST table is empty. There wouldn't be > any good way to keep TOAST pointers valid across the move. Hmm, couldn't you use the binary-upgrade support functions just prior to creating the table in the target database? If the OID is not already being used in the target database, it should be possible to reserve it just like pg_upgrade does it. This is a one-shot for a single table, so there's no need to automate it or anything too sophisticated. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2019-Apr-03, Tom Lane wrote:
>> Actually, thinking about that a bit harder: there's one aspect of
>> what pg_upgrade does that's really hard to control from userspace,
>> and that's forcing tables to have the same OIDs as before. In this
>> context, that means you're probably out of luck if the table has a
>> TOAST table, unless the TOAST table is empty. There wouldn't be
>> any good way to keep TOAST pointers valid across the move.
> Hmm, couldn't you use the binary-upgrade support functions just prior to
> creating the table in the target database?
Yeah, in theory you could do that, if the OID isn't already in use for
a table. But that just added several more steps and more ways to
shoot yourself in the foot. I'm starting to think this wouldn't really
be worth the risk.
regards, tom lane
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@momjian.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 +