Hello,

I'd like to move some tables from a particular database onto a
different set of disks. However, I can't see any robust way of
achieving this. I've tried using symlinks but these can easily be
broken by a mixture of "vacuum full" and growing the table again
(assuming the table is large enough to span multiple files). We can
handle a solution that breaks when the table is dropped and recreated,
that's a special enough case enough to be managed.

Any ideas welcome!

Regards,
-Sam.

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Sam Mortimer <sam.mortimer@gmail.com> writes:
> I'd like to move some tables from a particular database onto a
> different set of disks. However, I can't see any robust way of
> achieving this.

PG 8.0 can do this using tablespaces. There isn't any particularly
nice solution in earlier versions. If you want to move an entire
*database*, you can do it with the "alternate locations" feature,
but not table-by-table.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Hello Tom,

On Thu, 16 Dec 2004 11:19:22 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Sam Mortimer <sam.mortimer@gmail.com> writes:
> > I'd like to move some tables from a particular database onto a
> > different set of disks. However, I can't see any robust way of
> > achieving this.
>
> PG 8.0 can do this using tablespaces. There isn't any particularly
> nice solution in earlier versions. If you want to move an entire
> *database*, you can do it with the "alternate locations" feature,
> but not table-by-table.

Thankyou for replying, we'll be sure to check out pg 8.0 when it's
released. As it happens for us though, this is our mission critical
main company database so upgrading to 8.0 won't be an option until at
least 6 months or so after it's been released (to allow for sufficient
testing etc).

The best solution I've found to far is to create the table symlinks as
root and then:
chmod 1770 17143
chown root 17143

...on the main database directory so that postmaster (running as user
postgres) can't remove the links. doesn't help with drop/create and
leaves error messages from postmaster when you vacuum full:
Dec 15 16:32:21 netdb04 postgres[9966]: [3-1] LOG: failed to unlink
"/var/lib/pgsql/data/base/17143/17150.2": Operation not permitted

However, it works, but then we're not too keen on implementing this in
a production environment ;-)

We may look at patching the source so that postmaster under linux
deletes the file at the end of the symlink rather than deleting
symlink itself..

Regards,
-Sam.

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster