[postgis-users] Moving database to another tablespace

[Kevin Neufeld]

Unfortunately, PostgreSQL does not yet have the command "ALTER DATABASE 
SET TABLESPACE new_tablespace" (I don't see it in v.8.3 either).

The easiest solution I think is to simply create a new database using 
your current database as a template in your new tablespace.
 From the command line:
createdb -T my_db -D new_tablespace

Alternatively, you can move individual tables or indexes to your new 
tablespace.  I often do this to increase I/O - split the most heavily 
used tables/indexes over separate raid stacks.
ALTER TABLE <tbl> SET TABLESPACE <new_tablespace>;
You would have to do this for every table/index you plan on moving, but 
this can be easily accomplished by using SQL to generate a SQL script.
ie.
-- send query results to a file.
/o script.sql
/t
SELECT 'ALTER TABLE ' || schemaname || '.' || tablename || ' SET 
TABLESPACE new_tablespace;'
  FROM pg_tables
  WHERE schemaname = 'foo';
\o
\i script.sql
\t

Cheers,
Kevin

Thorsten Kraus wrote:
>
> Hi,
>
> I have installed a database on the standard tablespace, but the 
> database grows fast and there's no more space on the partition available.
>
> So I've created another tablespace on another partition. Is it 
> possible to move my existing database to this new tablespace? If yes, 
> what steps have to be done?
>
> Bye Thorsten
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>