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 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?
This message has been scanned for malware by SurfControl plc. www.surfcontrol.com
pgsql-admin by date
|Next:||From: Irina Sourikova||Date: 2007-10-18 15:25:48|
|Subject: does vacuumlo removes BLOBs from deleted tables?|
|Previous:||From: Scott Marlowe||Date: 2007-10-18 14:56:05|
|Subject: Re: Is my database now too big?|