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.
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.

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:

which leads to the question: how to move database objects 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 :


    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 :


    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 :


    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