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

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

From
Steven Lembark
Date:
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.



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

From
Steven Lembark
Date:
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



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

From
Tom Lane
Date:
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.



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

From
Steven Lembark
Date:
> 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



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

From
Adrian Klaver
Date:
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



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

From
Tom Lane
Date:
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



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

From
Alvaro Herrera
Date:
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



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

From
Tom Lane
Date:
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



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

From
Bruce Momjian
Date:
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 +