Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id A3E769FA017 for ; Thu, 18 Oct 2007 12:49:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 69891-01 for ; Thu, 18 Oct 2007 12:49:49 -0300 (ADT) X-Greylist: delayed 00:53:00.583757 by SQLgrey-1.7.5 Received: from cluster-h.mailcontrol.com (cluster-h.mailcontrol.com [85.115.42.190]) by postgresql.org (Postfix) with ESMTP id 7874D9FA195 for ; Thu, 18 Oct 2007 12:49:49 -0300 (ADT) Received: from cluster-e.mailcontrol.com (rly02e.srv.mailcontrol.com [10.69.1.112]) by rly15h.srv.mailcontrol.com (MailControl) with ESMTP id l9IEujZV008098 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Thu, 18 Oct 2007 15:56:47 +0100 Received: from jupiter.egsgroup.com (jupiter.egsgroup.com [217.199.182.198]) by rly02e.srv.mailcontrol.com (MailControl) with ESMTP id l9IEudqm008977 for ; Thu, 18 Oct 2007 15:56:44 +0100 Received: by jupiter.egsgroup.com (Postfix, from userid 65) id 329B370E3; Thu, 18 Oct 2007 15:56:37 +0100 (BST) X-Scanned-By: MailControl A-07-08-10 (www.mailcontrol.com) on 10.69.0.112 X-Scanned-By: AMaViS-ng at egsgroup.com Received: from [10.20.0.98] (unknown [217.150.127.117]) by jupiter.egsgroup.com (Postfix) with ESMTP id 4C710AF for ; Thu, 18 Oct 2007 15:56:36 +0100 (BST) Message-ID: <47177426.1050801@egsgroup.com> Date: Thu, 18 Oct 2007 15:56:38 +0100 From: Alexander Stanier Organization: EGS Ltd User-Agent: Thunderbird 2.0.0.6 (Windows/20070728) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Moving multiple schemas when upgrading from 8.1 to 8.2 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200710/174 X-Sequence-Number: 27088 Hello All, I am try to move a database from an 8.1.5 cluster (on OS X 10.3) to an 8.2.4 cluster (on OS X 10.4). In the source database on 8.1.5, I have 3 schemas: public - contains the majority of my transactional data jbpm - contains JBoss jBPM contrib - contains 5 contrib modules (tsearch2, fuzzystrmatch, intarray, intaggregate and pg_trgm) The public and jbpm schemas are dependent on one another (each refers to the other) so they need to be restored together. Ideally, I would only dump out these two schemas on the 8.1.5 cluster (and create my contrib schema anew on 8.2.4), however the option to dump 2 of the 3 schemas is only available from 8.2.x (using multiple -n switches). So my only option is to dump out all schemas at once. When it comes to the restore, even in 8.2.x you can't specify multiple schemas; it's either one or all. This option is only available on pg_dump. Restoring one schema at a time is no good as they depend on each other. Restoring all means bringing in the old contrib stuff from 8.1.5. Not only is it out of date but you get an error trying to restore function snb_ru_init which does not exist in the tsearch2.so from 8.2.4. Even if you ignore error and let the restore continue to completion, you still want to replace the contrib schema for a new one based on 8.2.4 scripts, but you can't drop this schema without cascading to the other schemas. So what to do? I thought possibly I could do a plain dump from the 8.1.5 schema and then trawl through it to remove anything relating to the contrib modules, but this seems like a lot of hard work. I also thought maybe I could run the 8.2.x pg_dump binary against the 8.1.5 cluster? But I wasn't sure if this was 'allowed' and if it would produce a valid data dump? Am I missing a really obvious way to accomplish this? Regards, Alex Stanier. This message has been scanned for malware by SurfControl plc. www.surfcontrol.com