Обсуждение: dump from 8.x restore to 7.3.x

Hi to all,

We have a pretty big database that is going for an upgrade (PG7 -> PG8)
in the next few days, we have tested all the features of our application
but we cannot be sure that everything will work out perfectly (db is
managing several blob's only tables that have proven to be pretty hard
to configure).
I am currently investigating a worst-case-scenario strategy that would
be to downgrade the database from PG8 to 7 if anything goes wrong;00
this implies that we:
1) dump from a running PG8
2) remove PG8 and reinstall PG7
3) restore the dump into the running PG7

unfortunately pg_restore complains about the dump header and doesn't
restore the db at all, having tried to dump with both -Ft and -Fc flags

shall I try the "--ignore-version" option in pg_restore?
is there going to be any problem in using this option? (corrupted data,
tables, etc.)

Thanks, any help appreciated
Edoardo

> We have a pretty big database that is going for an upgrade (PG7 -> PG8)
> in the next few days, we have tested all the features of our application
> but we cannot be sure that everything will work out perfectly (db is
> managing several blob's only tables that have proven to be pretty hard
> to configure).
> I am currently investigating a worst-case-scenario strategy that would
> be to downgrade the database from PG8 to 7 if anything goes wrong;00
> this implies that we:
> 1) dump from a running PG8
> 2) remove PG8 and reinstall PG7
> 3) restore the dump into the running PG7
>
> unfortunately pg_restore complains about the dump header and doesn't
> restore the db at all, having tried to dump with both -Ft and -Fc flags
>
> shall I try the "--ignore-version" option in pg_restore?
> is there going to be any problem in using this option? (corrupted data,
> tables, etc.)

My understanding is that dumps are not backwards compatible.  Any dumps created with a Version 8.*
pg_dump cannot be used for any older versions.  If you want "play it safe" you should create a new
server for the postgresql version 8.1 to preform validation tests with your front-ends.  After you
are satisfied that everything works well you can replace the version 7 production server with
version 8.

If you would like a safety net beyond this you could contact one of the PostgreSQL consulting
companies to walk you through the integration of V.8.1 into your projection system.

Regards,

Richard Broersma Jr.

Hi,

I've  already made almost same upgrade (7.4->8.1) under Debian linux.
On Debian the 8.1 database structure will be installed into
/var/lib/postgresql/ by default and on 7.4 it's /var/lib/postgres/. So
if you remove the packages and you (or your package manager) do not
remove the databases physically you only need to reinstall the old
package (without owewrite the old sturcture, (move the directory to
somewhere if you want to be sure)) and the fallback will fast and safe.

Szabek

I wrote to Peter Wilson some day ago:
I had problems, like that. I've omitted them using "plain" format (SQL
script file) of dump file. I restore data from "plain"  format with psql
- using command "\i FILE". It work always. If You gzip this "plain" file
- You become simillar volume of file using binary dump format.

...And in Your case (downgrade) You can do this way. When in the script
will be something incopatbile with PostgreSQL 7.3 - You can simply edit
the SQL script.
I've imported for expample in this way MySQL scripts made in MySQL into
PostgreSQL.

Greetings
Adam

Edoardo Ceccarelli wrote:
> Hi to all,
>
> We have a pretty big database that is going for an upgrade (PG7 -> PG8)
> in the next few days, we have tested all the features of our application
> but we cannot be sure that everything will work out perfectly (db is
> managing several blob's only tables that have proven to be pretty hard
> to configure).
> I am currently investigating a worst-case-scenario strategy that would
> be to downgrade the database from PG8 to 7 if anything goes wrong;00
> this implies that we:
> 1) dump from a running PG8
> 2) remove PG8 and reinstall PG7
> 3) restore the dump into the running PG7
>
> unfortunately pg_restore complains about the dump header and doesn't
> restore the db at all, having tried to dump with both -Ft and -Fc flags
>
> shall I try the "--ignore-version" option in pg_restore?
> is there going to be any problem in using this option? (corrupted data,
> tables, etc.)
>
> Thanks, any help appreciated
> Edoardo
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>
>

Thank you for this infos, I wasn't sure about this, now, at least, I know that this is not possible.
On the other hand, even the most experienced database programmer / administrator  when upgrading a production environment can't be 100% sure about whether the application,will fail or not.
Of course, testing is the only way to ensure that everything will be as smooth as possible but a real safety net would be to establish a common dump file format across different PG versions.
I will keep searching ... and testing of course :)

Richard Broersma Jr wrote:

> My understanding is that dumps are not backwards compatible.  Any dumps created with a Version 8.*
> pg_dump cannot be used for any older versions.  If you want "play it safe" you should create a new
> server for the postgresql version 8.1 to preform validation tests with your front-ends.  After you
> are satisfied that everything works well you can replace the version 7 production server with
> version 8.
>

I don't see that as being the case - there will be exceptions where you
have new features in an 8.x database that won't work in a 7.x db but
otherwise it shouldn't matter, the dumps can be used to import into
other databases than postgres as long as you use dump with inserts
instead of copy and use common datatypes.

I would expect that if you dump from 7.x into 8.x and have issues then a
dump from 8.x into 7.x would work as long as you haven't changed your
schema to add 8.x only features.

If you have any concerns and want a fall back safety net, then you will
want to either not add any 8.x features or document the changes so that
the structure can be changed back to be 7.x compatible.

Of course not making any schema changes until you are happy to stay with
8.x will help and you could just dump data only to go back.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz

Shane Ambler wrote:
> I don't see that as being the case - there will be exceptions where
> you have new features in an 8.x database that won't work in a 7.x db
> but otherwise it shouldn't matter, the dumps can be used to import
> into other databases than postgres as long as you use dump with
> inserts instead of copy and use common datatypes.

Restoring dumps from new versions into old versions doesn't work
(without a lot of manual work).  Example cases:

8.2 dump won't install into 8.1 because it sets
standard_conforming_stings, which can't be set in 8.1

8.1 dump won't install into 8.0 because it dumps users as roles, but
roles don't exist in 8.0.

8.0 dump won't install into 7.4 because it dumps tablespaces, which
don't exist in 7.4.

etc. etc.

Obviously you can see that getting pretty bad if you want to go from 8.1
all the way to 7.3.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Nov 30, 2006, at 5:52 AM, Peter Eisentraut wrote:
> Shane Ambler wrote:
>> I don't see that as being the case - there will be exceptions where
>> you have new features in an 8.x database that won't work in a 7.x db
>> but otherwise it shouldn't matter, the dumps can be used to import
>> into other databases than postgres as long as you use dump with
>> inserts instead of copy and use common datatypes.
>
> Restoring dumps from new versions into old versions doesn't work
> (without a lot of manual work).  Example cases:

On the other hand, dumping a newer version database with an older
version of *pg_dump* is much more likely to succeed. It's not a
guarantee, but it should get you pretty close. And as someone else
mentioned, doing a plain text dump is probably your best bet in this
case.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

>
> On the other hand, dumping a newer version database with an older
> version of *pg_dump* is much more likely to succeed. It's not a
> guarantee, but it should get you pretty close. And as someone else
> mentioned, doing a plain text dump is probably your best bet in this case.

No, that's not an option, I have tried several combinations like using
old pg_dump on a new db but the results are always the same: no way to
restore a newer db on an older pg (my test were only about PG8 - PG7)
The best solution is the one that Adam was suggesting (btw Thank you) in
this same thread, doing a "pg_dump -Fp" on the new db and restoring it
with a
\i FILENAME from the old PG

The only issue about this is that it doesn't treat LO's at all, but it
was enough in my "disaster recovery" scenario.

I started investigating about dumping and restoring LO's and I found a
pretty interesting project under the 7.4 contrib dir, but it seems it's
not maintained any more and I didn't manage to compile it.
Using the lo_export - import function seems doesn't work for "ALL" the
LO's of the database.. maybe it's possible to write a query to do that
but - again - it wasn't so important to save blobs

Thank you again to everybody for your help
Edoardo

Jim Nasby <decibel@decibel.org> writes:
> On the other hand, dumping a newer version database with an older
> version of *pg_dump* is much more likely to succeed.

You meant to say that the other way around, no?  Certainly for a jump
this big I'd rather expect the older pg_dump to fail altogether.
[ experiments a bit... ]  Both 7.3 and 7.4 pg_dump fail to talk to an
8.1 server; they expect pg_database.datpath to exist.  8.0 pg_dump
seems to do OK with 8.1 ... has some problems with 8.2 though.

            regards, tom lane