[Fwd: Backporting parts of databases from a 7.3 server to 7.2 : How ?]

Posted about 2 weeks to the "General" and "Questions" lists. Got no answers
and found no workaround (yet !).

Any ideas ?

Emmanuel Charpentier

PS : If possible, Please Cc: to charpent(at)bacbuc(dot)dyndns(dot)org : I'm reading
the list through the news server, and nor very often ...

-------- Original Message --------
Subject: Backporting parts of databases from a 7.3 server to 7.2 : How ?
Date: Mon, 20 Jan 2003 19:31:53 +0100
From: Emmanuel Charpentier <charpent(at)bacbuc(dot)dyndns(dot)org>
Organization: Hub.Org Networking Services (http://www.hub.org)
Newsgroups:
comp.databases.postgresql.general,comp.databases.postgresql.questions

Dear list(s),

I have a "test" machine, which I recently upgraded to PostgreSQL 7.3, and a
"production" machine, which for now will stay at 7.2 (I have some issues
with ODBC access to 7.3 with the current driver).

I have no problem exporting databases (or parts of them) from the 7.2 to
the 7.3 machine. For example

production$ pg_dump -F c -f mybase.dump mybase
test$ pg_restore -c mybase.dump

or even

test$ pg_restore -l mybase.dump | grep VIEW > mybase.views
test$ pg_restore -L mybase.views mybase.dump

The latter one is not really useful. However, the reverse operation (i.e.
restoring on the production system a set of views created on the test
machine) is actually a useful one : it allows me to get from the production
database a snapshot of data, work on it on the test machine, creating
useful views in the process, and restoring them without cloberring
(possibly updated) data. The same could be said of function, indexes,
triggers, rules and so on ...

However, this does *not* work between a 7.3-generated dump and a 7.2
production server. The archiver complaints of an 'unknown archive format :
"0"' (I'm quoting this from the top of my head : my production server is
not reachable from the place I'm writing this).

The only workaround I could come up with so far was to (watch it !) !
1) create a -F c dump
2) pg_restore -l to get a list of the objects
3) looping through this list, pg_dump -F p -t each and every view,
appending the proceeds to a single SQL file, which can be played back to
the pproduction server.

Not fun, and hardly generalisable ...

Questions :

1) Is that a bug or a feature ?
2) Is there a workaround (e. g. by telling the 7.3 pg_dump to use a
7.2-recognized format ) ?
3) Do you have other suggestions (short of upgrading the production server
to 7.3, which I plan to do when my issues with ODBC access will be solved).

Sincerely,

Emmanuel Charpentier

PS : Would you be so kind as to Cc: me your answers : I'm on the lists in a
"no-mail" mode and read it through the news interface.