pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feeddump from 8.x restore to 7.3.x
10+ messages / 8 participants
[nested] [flat]
* dump from 8.x restore to 7.3.x
@ 2006-11-29 14:28 Edoardo Ceccarelli <eddy@axa.it>
0 siblings, 2 replies; 10+ messages in thread
From: Edoardo Ceccarelli @ 2006-11-29 14:28 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-29 15:02 Richard Broersma Jr <rabroersma@yahoo.com>
parent: Edoardo Ceccarelli <eddy@axa.it>
1 sibling, 3 replies; 10+ messages in thread
From: Richard Broersma Jr @ 2006-11-29 15:02 UTC (permalink / raw)
To: eddy@axa.it; pgsql-admin
> 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.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-29 15:50 Szabolcs BALLA <szballa@confinsystems.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Szabolcs BALLA @ 2006-11-29 15:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-29 19:12 Adam Radłowski <adamr@informatyka.gdansk.pl>
parent: Edoardo Ceccarelli <eddy@axa.it>
1 sibling, 0 replies; 10+ messages in thread
From: Adam Radłowski @ 2006-11-29 19:12 UTC (permalink / raw)
To: eddy@axa.it; +Cc: pgsql-admin
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
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-29 19:14 Edoardo Ceccarelli <eddy@axa.it>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 0 replies; 10+ messages in thread
From: Edoardo Ceccarelli @ 2006-11-29 19:14 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; pgsql-admin
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 :)
>
> 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.
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-30 11:09 Shane Ambler <pgsql@007Marketing.com>
parent: Richard Broersma Jr <rabroersma@yahoo.com>
2 siblings, 1 reply; 10+ messages in thread
From: Shane Ambler @ 2006-11-30 11:09 UTC (permalink / raw)
To: Richard Broersma Jr <rabroersma@yahoo.com>; +Cc: eddy@axa.it; pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-11-30 13:52 Peter Eisentraut <peter_e@gmx.net>
parent: Shane Ambler <pgsql@007Marketing.com>
0 siblings, 1 reply; 10+ messages in thread
From: Peter Eisentraut @ 2006-11-30 13:52 UTC (permalink / raw)
To: pgsql-admin; +Cc: Shane Ambler <pgsql@007marketing.com>; Richard Broersma Jr <rabroersma@yahoo.com>; eddy@axa.it
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/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-12-04 22:25 Jim Nasby <decibel@decibel.org>
parent: Peter Eisentraut <peter_e@gmx.net>
0 siblings, 2 replies; 10+ messages in thread
From: Jim Nasby @ 2006-12-04 22:25 UTC (permalink / raw)
To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-admin@postgresql.org, Shane Ambler <pgsql@007marketing.com>; Richard Broersma Jr <rabroersma@yahoo.com>; eddy@axa.it
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)
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-12-05 12:33 Edoardo Ceccarelli <eddy@axa.it>
parent: Jim Nasby <decibel@decibel.org>
1 sibling, 0 replies; 10+ messages in thread
From: Edoardo Ceccarelli @ 2006-12-05 12:33 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-admin@postgresql.org, Shane Ambler <pgsql@007marketing.com>; Richard Broersma Jr <rabroersma@yahoo.com>; adamr@informatyka.gdansk.pl
>
> 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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: dump from 8.x restore to 7.3.x
@ 2006-12-05 16:00 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim Nasby <decibel@decibel.org>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2006-12-05 16:00 UTC (permalink / raw)
To: Jim Nasby <decibel@decibel.org>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-admin@postgresql.org, Shane Ambler <pgsql@007marketing.com>; Richard Broersma Jr <rabroersma@yahoo.com>; eddy@axa.it
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2006-12-05 16:00 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-29 14:28 dump from 8.x restore to 7.3.x Edoardo Ceccarelli <eddy@axa.it>
2006-11-29 15:02 ` Richard Broersma Jr <rabroersma@yahoo.com>
2006-11-29 15:50 ` Szabolcs BALLA <szballa@confinsystems.com>
2006-11-29 19:14 ` Edoardo Ceccarelli <eddy@axa.it>
2006-11-30 11:09 ` Shane Ambler <pgsql@007Marketing.com>
2006-11-30 13:52 ` Peter Eisentraut <peter_e@gmx.net>
2006-12-04 22:25 ` Jim Nasby <decibel@decibel.org>
2006-12-05 12:33 ` Edoardo Ceccarelli <eddy@axa.it>
2006-12-05 16:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-29 19:12 ` Adam Radłowski <adamr@informatyka.gdansk.pl>
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