Re: Is there an easy and safe way to migrate a database to a new tablespace? - Mailing list 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.