Обсуждение: Is there an easy and safe way to migrate a database to a new tablespace?

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.

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.