pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sam Mortimer <sam.mortimer@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-admin@postgresql.org
Subject: Re: robust way of moving tables to a different disk??
Date: Thu, 16 Dec 2004 17:07:01 +0000
Message-ID: <e0992c55041216090720f7041e@mail.gmail.com> (raw)
In-Reply-To: <25213.1103213962@sss.pgh.pa.us>
References: <e0992c5504121602064851bc7@mail.gmail.com>
	<25213.1103213962@sss.pgh.pa.us>

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.



view thread (4+ messages)

Message-ID: <e0992c55041216090720f7041e@mail.gmail.com>
Permalink:  ../e0992c55041216090720f7041e@mail.gmail.com/
Also on:    postgresql.org/message-id/e0992c55041216090720f7041e@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: sam.mortimer@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: robust way of moving tables to a different disk??
  In-Reply-To: <e0992c55041216090720f7041e@mail.gmail.com>
  References: <e0992c5504121602064851bc7@mail.gmail.com> <25213.1103213962@sss.pgh.pa.us> <e0992c55041216090720f7041e@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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