pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Is 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>
  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-12-03 21:22  Scott Marlowe <scott.marlowe@gmail.com>
  parent: David Young <randomfire@gmail.com>
  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-12-04 11:16  Aldor <aldnin@yahoo.de>
  parent: Scott Marlowe <scott.marlowe@gmail.com>
  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