pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedIs there an easy and safe way to migrate a database to a new tablespace?
3+ messages / 3 participants
[nested] [flat]
* Is there an easy and safe way to migrate a database to a new tablespace?
@ 2007-11-26 21:05 David Young <randomfire@gmail.com>
2007-12-03 21:22 ` Re: Is there an easy and safe way to migrate a database to a new tablespace? Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Young @ 2007-11-26 21:05 UTC (permalink / raw)
To: pgsql-admin
Hi,
I recently converted to using a postgresql engine that support tablespaces.
There's a database that I would like to move to a new tablespace for
performance reasons. What is the safe and correct way of doing this? The
documentation is a bit sketchy on this.
Thanks in advance,
David
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there an easy and safe way to migrate a database to a new tablespace?
2007-11-26 21:05 Is there an easy and safe way to migrate a database to a new tablespace? David Young <randomfire@gmail.com>
@ 2007-12-03 21:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-04 11:16 ` Re: Is there an easy and safe way to migrate a database to a new tablespace? Aldor <aldnin@yahoo.de>
0 siblings, 1 reply; 3+ messages in thread
From: Scott Marlowe @ 2007-12-03 21:22 UTC (permalink / raw)
To: David Young <randomfire@gmail.com>; +Cc: pgsql-admin
On Nov 26, 2007 3:05 PM, David Young <randomfire@gmail.com> wrote:
> Hi,
>
> I recently converted to using a postgresql engine that support tablespaces.
> There's a database that I would like to move to a new tablespace for
> performance reasons. What is the safe and correct way of doing this? The
> documentation is a bit sketchy on this.
I think you'd have to first set a new default_tablespace for the db,
then you'd have to write a script to go through each object and move
it with an alter (table|index) to the new tablespace.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Is there an easy and safe way to migrate a database to a new tablespace?
2007-11-26 21:05 Is there an easy and safe way to migrate a database to a new tablespace? David Young <randomfire@gmail.com>
2007-12-03 21:22 ` Re: Is there an easy and safe way to migrate a database to a new tablespace? Scott Marlowe <scott.marlowe@gmail.com>
@ 2007-12-04 11:16 ` Aldor <aldnin@yahoo.de>
0 siblings, 0 replies; 3+ messages in thread
From: Aldor @ 2007-12-04 11:16 UTC (permalink / raw)
To: pgsql-admin
Get the oid of your old tablespace:
select oid
from pg_tablespace
where spcname = '[OLDTABLESPACE]';
Get your tables from the old tablespace:
select select relname
from pg_class
where pc.reltablespace = [TABLESPACEOID]
----optional----
and relkind = 'r' ;
With "ALTER TABLE [TABLE] SET TABLESPACE [TABLESPACENAME];" you can
move your tables to the new destination.
Scott Marlowe wrote:
> On Nov 26, 2007 3:05 PM, David Young <randomfire@gmail.com> wrote:
>> Hi,
>>
>> I recently converted to using a postgresql engine that support tablespaces.
>> There's a database that I would like to move to a new tablespace for
>> performance reasons. What is the safe and correct way of doing this? The
>> documentation is a bit sketchy on this.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-12-04 11:16 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-26 21:05 Is there an easy and safe way to migrate a database to a new tablespace? David Young <randomfire@gmail.com>
2007-12-03 21:22 ` Scott Marlowe <scott.marlowe@gmail.com>
2007-12-04 11:16 ` Aldor <aldnin@yahoo.de>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox