Thread: [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@bacbuc.dyndns.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@bacbuc.dyndns.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
stayat 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.

Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> 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).

In general, dumps from newer versions make use of SQL features that are
not in older versions; so it's more or less hopeless to expect backwards
compatibility of dumps.  I'm not sure why pg_dump's archive header
format changed, but even without that you'd be facing SQL-level
compatibility issues.

You could perhaps have some success by dumping as a text-format dump
(not -Fc or -Ft) and then editing the resulting file to dumb the SQL
down to 7.2's level.
        regards, tom lane

Tom Lane wrote:
> Emmanuel Charpentier <charpent@bacbuc.dyndns.org> writes:
> 
>>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).
> 
> 
> In general, dumps from newer versions make use of SQL features that are
> not in older versions; so it's more or less hopeless to expect backwards
> compatibility of dumps.  I'm not sure why pg_dump's archive header
> format changed, but even without that you'd be facing SQL-level
> compatibility issues.
> 
> You could perhaps have some success by dumping as a text-format dump
> (not -Fc or -Ft) and then editing the resulting file to dumb the SQL
> down to 7.2's level.

That's what I did ... I had little to no issues with the resulting SQL, but 
emacs'ing my way in a database dump was ... strange !

Thanks a lot !
                Emmanuel Charpentier

PS : Tom, I'm Cc'ing you, but I'm not sure that this answer will reach you 
directly. Your spam filter asininely believes that anybody running SMTP on 
a dynamically assigned IP is a spammer !

-- 
Emmanuel Charpentier