Re: robust way of moving tables to a different disk??

Hello Tom,

On Thu, 16 Dec 2004 11:19:22 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Sam Mortimer <sam(dot)mortimer(at)gmail(dot)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.