Thread: 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

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