Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 2F667B5DBF0 for ; Thu, 8 Sep 2011 15:55:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 27250-09 for ; Thu, 8 Sep 2011 18:55:28 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sd-17937.dedibox.fr (sd-17937.dedibox.fr [88.191.100.74]) by mail.postgresql.org (Postfix) with ESMTP id 9D256B5DBE8 for ; Thu, 8 Sep 2011 15:55:28 -0300 (ADT) Received: from [192.168.10.3] (AMontsouris-552-1-64-104.w92-140.abo.wanadoo.fr [92.140.95.104]) by sd-17937.dedibox.fr (Postfix) with ESMTPA id DBD043B8F5; Thu, 8 Sep 2011 20:55:27 +0200 (CEST) Subject: Re: Moving database and schema to a new tablespace From: Guillaume Lelarge To: Richard Shaw Cc: pgsql-admin@postgresql.org Date: Thu, 08 Sep 2011 20:55:27 +0200 In-Reply-To: <9BD8BDB8-C1D6-4683-8011-95C0E66F3AAB@aggress.net> References: <9BD8BDB8-C1D6-4683-8011-95C0E66F3AAB@aggress.net> Content-Type: text/plain; charset="UTF-8" X-Mailer: Evolution 3.0.2 (3.0.2-3.fc15) Content-Transfer-Encoding: 7bit Message-ID: <1315508127.3556.35.camel@localhost.localdomain> Mime-Version: 1.0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201109/36 X-Sequence-Number: 38273 On Thu, 2011-09-08 at 19:09 +0100, Richard Shaw wrote: > Hi, > > I'm looking to move a database by using ALTER DATABASE $DB SET TABLESPACE and i'd also like to move a schema over to the same new tablespace, googling around, it looks like there was a an ALTER SCHEMA $SCHEMA SET TABLESPACE command in the past, but apparently no longer. I'm using Postgres 9, can anyone confirm if this is still possible, and if not, how I can move the schema over ? > ALTER SCHEMA... SET TABLESPACE never existed in PostgreSQL. As you're using PostgreSQL 9, you can use the DO command to do something like this. You need to find every table in the schema, and launch ALTER TABLE... SET TABLESPACE to them. And then do the same with indexes. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com