Обсуждение: Symbolic Links to Tablespaces
PostgreSQL: 8.2.X
OS: Red Hat Linux 4.X
I have started to define tablespaces on different disks. Is there any performance issues related to referencing tablespaces on different disks with symbolic links? By using symbolic links to tablespaces can I then stop the database and move a particular tablespace from one location to another without causing a problem? This would seem to give a lot of flexibility to the location of tablespaces.
Thanks,
Lance Campbell
Project Manager/Software Architect
Web Services at Public Affairs
University of Illinois
217.333.0382
My e-mail address has changed to lance@illinois.edu
"Campbell, Lance" <lance@illinois.edu> writes: > I have started to define tablespaces on different disks. Is there any > performance issues related to referencing tablespaces on different disks > with symbolic links? By using symbolic links to tablespaces can I then > stop the database and move a particular tablespace from one location to > another without causing a problem? This would seem to give a lot of > flexibility to the location of tablespaces. A tablespace already is a symbolic link --- read http://www.postgresql.org/docs/8.2/static/storage.html Putting another one into the path will eat cycles and doesn't seem like it could buy anything. regards, tom lane
Once I have assigned tables and indexes to a particular tablespace that points to a particular location on disk is there a simple way to move the files to a new location? Example: Table xyz is using tablespace xyz_tbl which is located at /somedir/xyz_tbl on the disk. If I want to move it to a new disk located at /someotherdir/xyz_tbl/ how can I do that easily? Do I have to backup all of the tables using the tablespace xyz_tbl, drop the tables, drop the tablespace, recreate the tablespace with a different disk location and then finally reload the tables and data? Or is there an easier way? Is there a move tablespace disk location command? Thanks, Lance Campbell Project Manager/Software Architect Web Services at Public Affairs University of Illinois 217.333.0382 http://webservices.uiuc.edu My e-mail address has changed to lance@illinois.edu -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, May 26, 2008 10:09 AM To: Campbell, Lance Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Symbolic Links to Tablespaces "Campbell, Lance" <lance@illinois.edu> writes: > I have started to define tablespaces on different disks. Is there any > performance issues related to referencing tablespaces on different disks > with symbolic links? By using symbolic links to tablespaces can I then > stop the database and move a particular tablespace from one location to > another without causing a problem? This would seem to give a lot of > flexibility to the location of tablespaces. A tablespace already is a symbolic link --- read http://www.postgresql.org/docs/8.2/static/storage.html Putting another one into the path will eat cycles and doesn't seem like it could buy anything. regards, tom lane
am Mon, dem 26.05.2008, um 12:10:07 -0500 mailte Campbell, Lance folgendes: > Once I have assigned tables and indexes to a particular tablespace that > points to a particular location on disk is there a simple way to move > the files to a new location? > > Example: > Table xyz is using tablespace xyz_tbl which is located at > /somedir/xyz_tbl on the disk. If I want to move it to a new disk > located at /someotherdir/xyz_tbl/ how can I do that easily? ALTER TABLE SET TABLESPACE http://www.postgresql.org/docs/8.3/static/sql-altertable.html PS.: please no top-posting. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Campbell, Lance wrote: > Once I have assigned tables and indexes to a particular tablespace that > points to a particular location on disk is there a simple way to move > the files to a new location? > > Example: > Table xyz is using tablespace xyz_tbl which is located at > /somedir/xyz_tbl on the disk. If I want to move it to a new disk > located at /someotherdir/xyz_tbl/ how can I do that easily? Shut down the database server, replace the symbolic link in data/pg_tblspc to the new location, and start the server again. The location is also stored in pg_tablespace catalog; you'll need to fix it with "UPDATE pg_tablespace SET spclocation ='/someotherdir/xyz_tbl' WHERE spcname='xyz_tbl'", or pg_dumpall will still show the old location. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: ... > Shut down the database server, replace the symbolic link in > data/pg_tblspc to the new location, and start the server again. The > location is also stored in pg_tablespace catalog; you'll need to fix it > with "UPDATE pg_tablespace SET spclocation ='/someotherdir/xyz_tbl' > WHERE spcname='xyz_tbl'", or pg_dumpall will still show the old location. > wouldn't alter tablespace be not more easy and less fragile? T.
Вложения
Tino Wildenhain wrote: > Heikki Linnakangas wrote: > ... >> Shut down the database server, replace the symbolic link in >> data/pg_tblspc to the new location, and start the server again. The >> location is also stored in pg_tablespace catalog; you'll need to fix >> it with "UPDATE pg_tablespace SET spclocation ='/someotherdir/xyz_tbl' >> WHERE spcname='xyz_tbl'", or pg_dumpall will still show the old location. >> > wouldn't alter tablespace be not more easy and less fragile? Yes. But it requires copying all the data. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com