Saturday, November 5, 2011

Tablespaces in postgresql

Managing tablespaces in postgresql is a really straightforward process.
Tablespaces in PostgreSQL allow database administrators to define locations in the file system where the files representing database objects can be stored. Once created, a tablespace can be referred to by name when creating database objects.

http://www.postgresql.org/docs/9.0/static/manage-ag-tablespaces.html
The location must be an existing, empty directory that is owned by the PostgreSQL operating system user.

Regarding the use of symbolic links I thought windows didn't have them, but it turns out that I was wrong.

http://en.wikipedia.org/wiki/NTFS_symbolic_link

Anyway,  it's a known fact that windows does not perform well with databases overall compared to other OS, so...who cares about windows ? =-).

Moving a tablespace

I found this piece of advice:

http://postgresql.1045698.n5.nabble.com/How-to-move-a-tablespace-td2085289.html

which leads to the question: how to move database objects to a different tablespace?

http://blog.lodeblomme.be/2008/03/15/move-a-postgresql-database-to-a-different-tablespace/

Lots of Manual Work


Moving a complete database to a different tablespace involves 3 steps :

  1. You have to tell PostgreSQLto create new tables and indexes for the database in the new tablespace. This can be done with :









    1


    ALTER DATABASE "[database_name]" SET default_tablespace = [new_tablespace];



  2. Then you have to move each table in that database to the new tablespace with :









    1


    ALTER TABLE "[table_name]" SET TABLESPACE "[new_tablespace]";



    This line of SQL code is needed for each table in the database.

  3. And that’s not all, because the indexes also have to be moved. That’s done with :









    1


    ALTER INDEX "[index_name]" SET TABLESPACE "[new_tablespace]";



    This line of SQL code is needed for each index in the database.



Not so smooth, but hey it ain't that difficult =-P.

No comments:

Post a Comment