Обсуждение: Indexes on separate disk ?
I've looked around a lot and don't think this is possible with Postgres, but figured I would ask in case I missed something. Is it possible to configure things so that an index resides on a separate disk ? It doesn't look like it, since they both have to reside in the same database, and the entire contents of the database would reside underneath a single directory, and therefore on a single disk. Thanks Charlie
On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote: > I've looked around a lot and don't think this is possible with Postgres, but > figured I would ask in case I missed something. Is it possible to configure > things so that an index resides on a separate disk ? It doesn't look like it, > since they both have to reside in the same database, and the entire contents > of the database would reside underneath a single directory, and therefore on > a single disk. Maybe you can just move it to a different filesystem and use a symlink? It should be transparent to postgresql unless it ever replaces the file. -- Ragnar Kjørstad Big Storage
=?iso-8859-1?Q?Ragnar_Kj=F8rstad?= <postgres@ragnark.vestdata.no> writes:
> On Thu, Jun 13, 2002 at 12:49:39PM -0700, Charlie Toohey wrote:
>> I've looked around a lot and don't think this is possible with Postgres, but
>> figured I would ask in case I missed something. Is it possible to configure
>> things so that an index resides on a separate disk ?
> Maybe you can just move it to a different filesystem and use a symlink?
That's pretty much the standard hack: shut down the postmaster, move the
file, create a symlink. However this is a fairly labor-intensive kluge.
Especially so if the file exceeds 1Gb, because then you will need to
deal with symlinking multiple segments --- and perhaps re-symlinking
them, if the size dips below a Gb boundary and then grows again.
Still, for sub-Gb-sized indexes it's certainly doable.
We hope to have a cleaner tablespace-based approach in a release or two.
BTW, I'd certainly recommend getting the WAL files (pg_xlog directory)
moved to their own drive long before you worry about separating indexes
from data. That's a lot simpler (you only need a symlink for the
directory).
regards, tom lane
You sure can, Charlie. We have our indexes, lightly used/smaller tables and heavily used/larger tables split across several disks. In a nutshell, 1. You just have to create the objects. 2. Then identify the objects using oid2name. 3. Shut down your database and move the objects. 4. Then put symlinks in their place pointing to their new destination. 5. Fire it back up. (creating a reliable backup before this procedure is highly recommended) Charlie Toohey wrote: > > I've looked around a lot and don't think this is possible with Postgres, but > figured I would ask in case I missed something. Is it possible to configure > things so that an index resides on a separate disk ? It doesn't look like it, > since they both have to reside in the same database, and the entire contents > of the database would reside underneath a single directory, and therefore on > a single disk. > > Thanks > Charlie > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Bill MacArthur Webmaster DHS Club
I have a perl script that I have written which handles moving indexes from one filesystem to another. It currently only moves indexes, but could be easily extended to include tables. The syntax of the command looks like: ./moveindex.pl -s /db1/185204209 -d /db2/185204209 -i foo_pkey bar It looks up the 'relfilenode' for 'foo_pkey' (-i parameter) in 'pg_class' for the 'bar' database. It then checks to make sure that the file does exist in the source directory (-s parameter) and does not exist in the desination directory (-d paramater). If all looks good, it does a: pg_ctl stop -s -m fast and if there is no error shutting down, does the move/link/restart thing. CAVEATS: 1) long running queries/open cursors can prevent shutdown. the script terminates correctly, but the postmaster will eventually shutdown since it has been told to do so. (you only forget this once ;). 2) The script does not currently check permissions in the source and destination. This shouldn't be a problem because if it can't move the file it just leaves it where it was. Then the symlink fails because the file exists. 3) It might be possible to really confuse the postmaster if you move an index to a directory, then move it to another directory, then move it again. You might be able to create a loop in your symlinks if you do this just right. I haven't done it, but when I get nervous I still move the files by hand, JIC :). Anyway, if people are interested in seeing what I have, gimme a holler and I will put it up for download somewhere, or post it if people prefer. - brian On Thu, 13 Jun 2002, DHSC Webmaster wrote: > > You sure can, Charlie. > We have our indexes, lightly used/smaller tables and heavily used/larger > tables split across several disks. > In a nutshell, > 1. You just have to create the objects. > 2. Then identify the objects using oid2name. > 3. Shut down your database and move the objects. > 4. Then put symlinks in their place pointing to their new destination. > 5. Fire it back up. > > (creating a reliable backup before this procedure is highly recommended) > > Charlie Toohey wrote: > > > > I've looked around a lot and don't think this is possible with Postgres, but > > figured I would ask in case I missed something. Is it possible to configure > > things so that an index resides on a separate disk ? It doesn't look like it, > > since they both have to reside in the same database, and the entire contents > > of the database would reside underneath a single directory, and therefore on > > a single disk. > > > > Thanks > > Charlie > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- > Bill MacArthur > Webmaster > DHS Club > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > Wm. Brian McCane | Life is full of doors that won't open Search http://recall.maxbaud.net/ | when you knock, equally spaced amid those Usenet http://freenews.maxbaud.net/ | that open when you don't want them to. Auction http://www.sellit-here.com/ | - Roger Zelazny "Blood of Amber"
On 6/13/02 10:22 PM, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: > BTW, I'd certainly recommend getting the WAL files (pg_xlog directory) > moved to their own drive long before you worry about separating indexes > from data. That's a lot simpler (you only need a symlink for the > directory). tom: Could you elaborate a bit on what the benefits of this approach would be? I'm configuring a new server for PgSQL use shortly and would like to take this into account if it will benefit me. -- sgl