Tuesday, January 30, 2007

Moving a live database to a different directory

i want to move the running postgresql to a different directory any idea on how 2 do it
as it is running outta space
only 200mb left in 5gig
and in a day that ll be filled

well,
a database can be created to use a tablespace
where the tablespace can be another mounted volume

you want to make sure the volume is never not mounted , such as have it mounted in /etc/fstab

well, lets say you go by a nice new 80gb drive
and format it with ext3 or what ever you like to do
and hook it up in /etc/fstab so that it is /u01 or some path you like a lot
and then in postgresql
create tablespace something path '/u01/myfolder/mytablespace'
then you have to do something to alter database set tablespace , so that it uses that
hmm, iv never actually migrated an existing database to a differetn tablespace,
but i think it must be possible to move things from one tablespace to another one, so that it will physically sit on the new drive for us.
http://www.postgresql.org/docs/8.1/interactive/sql-altertable.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. See also CREATE TABLESPACE.

1 comment:

Unknown said...

I just wrote a blog post about moving a complete database to a different tablespace. It describes a way to generate the necessary SQL to move all tables and indexes.