pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSymbolic Links to Tablespaces
7+ messages / 5 participants
[nested] [flat]
* Symbolic Links to Tablespaces
@ 2008-05-26 14:11 Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Campbell, Lance @ 2008-05-26 14:11 UTC (permalink / raw)
To: pgsql-performance
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
http://webservices.uiuc.edu
My e-mail address has changed to lance@illinois.edu
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
@ 2008-05-26 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Re: Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2008-05-26 15:08 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
@ 2008-05-26 17:10 ` Campbell, Lance <lance@illinois.edu>
2008-05-26 17:32 ` Re: Symbolic Links to Tablespaces A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-05-26 18:26 ` Re: Symbolic Links to Tablespaces Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 2 replies; 7+ messages in thread
From: Campbell, Lance @ 2008-05-26 17:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Re: Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
@ 2008-05-26 17:32 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
1 sibling, 0 replies; 7+ messages in thread
From: A. Kretschmer @ 2008-05-26 17:32 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Re: Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
@ 2008-05-26 18:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-05-26 19:19 ` Re: Symbolic Links to Tablespaces Tino Wildenhain <tino@wildenhain.de>
1 sibling, 1 reply; 7+ messages in thread
From: Heikki Linnakangas @ 2008-05-26 18:26 UTC (permalink / raw)
To: Campbell, Lance <lance@illinois.edu>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Re: Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 18:26 ` Re: Symbolic Links to Tablespaces Heikki Linnakangas <heikki@enterprisedb.com>
@ 2008-05-26 19:19 ` Tino Wildenhain <tino@wildenhain.de>
2008-05-26 20:26 ` Re: Symbolic Links to Tablespaces Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tino Wildenhain @ 2008-05-26 19:19 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Campbell, Lance <lance@illinois.edu>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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.
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Symbolic Links to Tablespaces
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Re: Symbolic Links to Tablespaces Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Re: Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 18:26 ` Re: Symbolic Links to Tablespaces Heikki Linnakangas <heikki@enterprisedb.com>
2008-05-26 19:19 ` Re: Symbolic Links to Tablespaces Tino Wildenhain <tino@wildenhain.de>
@ 2008-05-26 20:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 7+ messages in thread
From: Heikki Linnakangas @ 2008-05-26 20:26 UTC (permalink / raw)
To: Tino Wildenhain <tino@wildenhain.de>; +Cc: Campbell, Lance <lance@illinois.edu>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-05-26 20:26 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-05-26 14:11 Symbolic Links to Tablespaces Campbell, Lance <lance@illinois.edu>
2008-05-26 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-05-26 17:10 ` Campbell, Lance <lance@illinois.edu>
2008-05-26 17:32 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-05-26 18:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
2008-05-26 19:19 ` Tino Wildenhain <tino@wildenhain.de>
2008-05-26 20:26 ` Heikki Linnakangas <heikki@enterprisedb.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox