PostgreSQL – Move Database to New Tablespace

postgresql

I'd like to completely move a database (tables, indexes, etc.) to a new tablespace. I know how to move each item individually but is there a way to move it all in one go? I'm using postgresql 9.1.

Best Answer

ALTER DATABASE name SET TABLESPACE new_tablespace

Excerpt from the docs:

... changes the default tablespace of the database. Only the database owner or a superuser can do this; you must also have create privilege for the new tablespace. This command physically moves any tables or indexes in the database's old default tablespace to the new tablespace. Note that tables and indexes in non-default tablespaces are not affected.

Related Solutions

PostgreSQL – How to Create Database on New Partition

CREATE TABLESPACE could be used.

create tablespace dedicated_datastore owner postgres location '/my/mount/point/'

and now the tablespace can be used as parameter when creating a database, (or a table, or an index)

create database my_database tablespace dedicated_datastore

PostgreSQL newbie – how to create objects in a database

I figured it out. I can create an empty table using the "new table.." menu item. Then I can edit the table once created by right clicking on it and choosing "scripts".

Best Answer

Related Solutions

PostgreSQL – How to Create Database on New Partition

PostgreSQL newbie – how to create objects in a database

Related Question