Re: Indexes on separate disk ? - Mailing list pgsql-admin

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"