pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Moving multiple schemas when upgrading from 8.1 to 8.2 2+ messages / 2 participants [nested] [flat]
* Moving multiple schemas when upgrading from 8.1 to 8.2 @ 2007-10-18 14:56 Alexander Stanier <alexander.stanier@egsgroup.com> 0 siblings, 1 reply; 2+ messages in thread From: Alexander Stanier @ 2007-10-18 14:56 UTC (permalink / raw) To: 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 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Moving multiple schemas when upgrading from 8.1 to 8.2 @ 2007-10-18 19:29 Tom Lane <tgl@sss.pgh.pa.us> parent: Alexander Stanier <alexander.stanier@egsgroup.com> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2007-10-18 19:29 UTC (permalink / raw) To: Alexander Stanier <alexander.stanier@egsgroup.com>; +Cc: pgsql-admin Alexander Stanier <alexander.stanier@egsgroup.com> writes: > 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? Not only is that allowed, it's recommended. The case that won't work (or at least doesn't work in some combinations, I'm not sure about 8.1 vs 8.2 specifically) is trying to load the output of 8.2 pg_dump back into 8.1, since the newer pg_dump may make use of commands that didn't exist before. But for an upgrade, using the newer pg_dump is considered good practice. > Am I missing a really obvious way to accomplish this? For the record, you could also have fixed it by using pg_restore's -l and -L switches --- that is, create a table-of-contents listing, edit out the unwanted objects, and use that to select what to restore. regards, tom lane ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-10-18 19:29 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2007-10-18 14:56 Moving multiple schemas when upgrading from 8.1 to 8.2 Alexander Stanier <alexander.stanier@egsgroup.com> 2007-10-18 19:29 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox