Обсуждение: Is there an easy and safe way to migrate a database to a new tablespace?
Hi,
I recently converted to using a postgresql engine that support tablespaces. There's a database that I would like to move to a new tablespace for performance reasons. What is the safe and correct way of doing this? The documentation is a bit sketchy on this.
Thanks in advance,
David
Re: Is there an easy and safe way to migrate a database to a new tablespace?
От
"Scott Marlowe"
Дата:
On Nov 26, 2007 3:05 PM, David Young <randomfire@gmail.com> wrote: > Hi, > > I recently converted to using a postgresql engine that support tablespaces. > There's a database that I would like to move to a new tablespace for > performance reasons. What is the safe and correct way of doing this? The > documentation is a bit sketchy on this. I think you'd have to first set a new default_tablespace for the db, then you'd have to write a script to go through each object and move it with an alter (table|index) to the new tablespace.
Get the oid of your old tablespace: select oid from pg_tablespace where spcname = '[OLDTABLESPACE]'; Get your tables from the old tablespace: select select relname from pg_class where pc.reltablespace = [TABLESPACEOID] ----optional---- and relkind = 'r' ; With "ALTER TABLE [TABLE] SET TABLESPACE [TABLESPACENAME];" you can move your tables to the new destination. Scott Marlowe wrote: > On Nov 26, 2007 3:05 PM, David Young <randomfire@gmail.com> wrote: >> Hi, >> >> I recently converted to using a postgresql engine that support tablespaces. >> There's a database that I would like to move to a new tablespace for >> performance reasons. What is the safe and correct way of doing this? The >> documentation is a bit sketchy on this.