pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
dump 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