Skip site navigation (1) Skip section navigation (2)

Moving multiple schemas when upgrading from 8.1 to 8.2

From: Alexander Stanier <alexander(dot)stanier(at)egsgroup(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Moving multiple schemas when upgrading from 8.1 to 8.2
Date: 2007-10-18 14:56:38
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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 

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 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?

Alex Stanier.

This message has been scanned for malware by SurfControl plc.


pgsql-admin by date

Next:From: Irina SourikovaDate: 2007-10-18 15:25:48
Subject: does vacuumlo removes BLOBs from deleted tables?
Previous:From: Scott MarloweDate: 2007-10-18 14:56:05
Subject: Re: Is my database now too big?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group