X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 15E873B70BD for ; Thu, 16 Dec 2004 17:07:43 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 54545-04 for ; Thu, 16 Dec 2004 17:07:36 +0000 (GMT) Received: from rproxy.gmail.com (rproxy.gmail.com [64.233.170.200]) by svr1.postgresql.org (Postfix) with ESMTP id 313883B6EA8 for ; Thu, 16 Dec 2004 17:07:35 +0000 (GMT) Received: by rproxy.gmail.com with SMTP id a41so1411157rng for ; Thu, 16 Dec 2004 09:07:32 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=GEGBYbsj6+aV9LHv2sxiBO7ZJMoyX1vU/cMJdm4x9U0CdlExbuxrCFa7Eih/XMiRdDUDiwpmp0hJEm1AGPZYjd2pgl26nQ2NJJ3AJd13K4avnJD/H0ow27M/Gw+BK7eSVIlQOeQS/b/DvBuShPvnnP43a1pRcYO9ln2OxMwtrtA= Received: by 10.39.1.5 with SMTP id d5mr1225359rni; Thu, 16 Dec 2004 09:07:02 -0800 (PST) Received: by 10.38.149.60 with HTTP; Thu, 16 Dec 2004 09:07:01 -0800 (PST) Message-ID: Date: Thu, 16 Dec 2004 17:07:01 +0000 From: Sam Mortimer Reply-To: Sam Mortimer To: Tom Lane Subject: Re: robust way of moving tables to a different disk?? Cc: pgsql-admin@postgresql.org In-Reply-To: <25213.1103213962@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <25213.1103213962@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200412/173 X-Sequence-Number: 15759 Hello Tom, On Thu, 16 Dec 2004 11:19:22 -0500, Tom Lane wrote: > Sam Mortimer 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.