Changing a table space?

[MrFlibble]

Has anyone ever changed the table space of a PT table to a table space on another drive? It it as simple as running the command to move it and postgres will then copy over the table and you continue life as normal?

[_dave_]

I can't remember if it is possible to re-assign tablespace after creation... is it? if so, what is the command?

I've certainly changed the tablespaces of various tables before I created the database - you just edit the schema.sql file, adding tablespace commands after all the create table sections.

I've yet to figure out "optimal", but I do like moving the lookup_* tables on to a little ramdisk, that's gotta speed things up (never actually benchmarked to see how much) and since they never change those are easily rebuilt on boot :)

[MrFlibble]

I think its just
ALTER TABLE my_table SET TABLESPACE new_tablespace
I haven't tried it yet...

[APerfect10]

ALTER TABLESPACE

Best regards,

Derek

[MrFlibble]

ALTER TABLESPACE
Best regards,
Derek

I think that pertains to changing the details of an existing table space. I'm looking to make some or all of the PT DB tables use a different tablespace. I think the command in my last post will do it.

[_dave_]

I think its just
ALTER TABLE my_table SET TABLESPACE new_tablespace
I haven't tried it yet...

Looks like the job :)
http://www.postgresql.org/docs/8.3/inte ... table.html

SET TABLESPACE

This form changes the table's tablespace to the specified tablespace and moves the data file(s) associated with the table to the new tablespace. Indexes on the table, if any, are not moved; but they can be moved separately with additional SET TABLESPACE commands.

Still looks a bit of a pain if you are planning to move a whole database - much easier to use the "CREATE DATABASE xxx TABLESPACE xxx" method I posted here, import the schema.sql file, then all tables / indexes etc are on the alternate tablespace in two commands.

The ALTER DATABASE command doesn't seem to be able to set tablespaces after a quick read :(

[MrFlibble]

- much easier to use the "CREATE DATABASE xxx TABLESPACE xxx" method I posted here, import the schema.sql file, then all tables / indexes etc are on the alternate tablespace in two commands.

The ALTER DATABASE command doesn't seem to be able to set tablespaces after a quick read :(

Ah excellent. I couldn't think of a way to create a PT DB on a non default namespace.