Обсуждение: Moving multiple schemas when upgrading from 8.1 to 8.2

Поиск
Список
Период
Сортировка

Moving multiple schemas when upgrading from 8.1 to 8.2

От
Alexander Stanier
Дата:
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

Re: Moving multiple schemas when upgrading from 8.1 to 8.2

От
Tom Lane
Дата:
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