Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id CA9E92E006D for ; Wed, 2 Apr 2008 16:45:09 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 38134-01 for ; Wed, 2 Apr 2008 16:44:51 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.179]) by developer.postgresql.org (Postfix) with ESMTP id 030562E0059 for ; Wed, 2 Apr 2008 16:44:54 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so3276293waf.5 for ; Wed, 02 Apr 2008 12:44:54 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=LAtuew3sK5R0voMN6E6dZdBeuH24ipJSs6OvsaosiyA=; b=CWSZNNIrtV1vpawQg8OtRmsQuPoIVW3z18BAzscikv23ahWQ94JybDULPW+UdHiNhMCLlYN6KHHw14uXKPVFCITYuvWDgY2hCtfdKHt6O4uugaVpHHFpAtmZPd1TltwzblGgbXv15e6nUU6TiyI+PP8Arvej1v3X7sGJhIu+TTo= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=nzD2x4Wu3vygZo9wRbU00BH/KfYz0IvK3Z3uXGtLaKQ0n1nVcM8SsWljZzc7b9rIFUua5lkso/AzrlMt7GAjl5w8mr2ZOiFP+gioYVOHgESyDM348eKo40HIQTNWjWflz4vNpHt3ov+F/AZRHdMXf5d24gOHt84ETgoM+6SQuD0= Received: by 10.115.14.1 with SMTP id r1mr15534127wai.97.1207165493449; Wed, 02 Apr 2008 12:44:53 -0700 (PDT) Received: by 10.114.199.20 with HTTP; Wed, 2 Apr 2008 12:44:53 -0700 (PDT) Message-ID: <4544e0330804021244o1141c119t74cbd5937490cbce@mail.gmail.com> Date: Wed, 2 Apr 2008 14:44:53 -0500 From: "Peter Koczan" To: "Hyatt, Gordon" Subject: Re: Moving a tablespace Cc: pgsql-admin@postgresql.org In-Reply-To: <8EA62D1B324F0A438A36E8B5F0848EB8F32051@jdcmail1.joslin.harvard.edu> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <8EA62D1B324F0A438A36E8B5F0848EB8F32051@jdcmail1.joslin.harvard.edu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/23 X-Sequence-Number: 28787 On Wed, Apr 2, 2008 at 2:05 PM, Hyatt, Gordon wrote: > I want to move an existing tablespace on an FC8 box. I read (at > http://www.postgresql.org/docs/8.2/static/manage-ag-tablespaces.html) > that one can simply stop the server, update the symbolic link in > $PGDATA/pg_tblspc, start the server, then update the pg_tablespace > catalog to point to the new location. > > Is this all there is to moving a tablespace? It seems too easy. > > The tablespace I want to move is only around 1GB in size so dropping and > reloading during the next maintenance cycle is not that much of an > issue; although, I'd prefer not to. Couldn't you also create a new tablespace elsewhere, and use the SET TABLESPACE subcommand of ALTER TABLE (http://www.postgresql.org/docs/8.2/static/sql-altertable.html) to migrate all the appropriate tables? Granted, it's not exactly the same as moving the tablespace, but there's less manual updating of system catalogs and potentially less downtime. I don't have a whole lot of experience with tablespaces so I may be missing something, but that's something you might want to try out. Peter