Forum OpenACS Q&A: Upgrade PG 7.3.4 to 7.4.1

Collapse
1: Upgrade PG 7.3.4 to 7.4.1
Posted by Malte Sussdorff on 03/11/04 08:45 AM
As a lot of you most likely have already done the upgrade to the latest PG version, here is a question for you.

How did you do it ?

I tried the commands outlined in the backup guide, but it failed with "invalid command \N", which is due to a change in the copy functionality of pg_dump from 7.3.4 to 7.4.1.

Next try was "pg_dump -d", but it breaks somewhere in between (and getting 160MB of data via inserts into the database is not my ideal day).

My last idea had been to install PG 7.4 on the same machine as 7.3.4 and use the PG 7.4 pg_dump. But this did not work either.

Any other ideas (short of getting rid of all \N via perl and replace it with something different) ? How is the experience with pg_dump/pg_restore ?

Collapse
2: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 1)
Posted by Bruce Spear on 03/11/04 11:42 AM
Malte, I can't help, only sympathize: I could not find a way to import my 7.3 dump into a new 7.4 database, either, so I'd love to hear a success story and read the documentation informing it!
Collapse
3: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 2)
Posted by Joel Aufrecht on 03/11/04 12:38 PM
I think the debian upgrade script includes the convolutions necessary to do this - ie, preserving old binaries, etc.  It seems like the PostGreSQL upgrade team did it to us again.
Collapse
4: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 1)
Posted by Dave Bauer on 03/11/04 12:54 PM
Malte,

Did you run pg_dump from 7.3.4 before attempting to upgrade? I used this method and it worked for me. I wonder what the different might be.

Collapse
5: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 4)
Posted by Malte Sussdorff on 03/11/04 01:35 PM
Steps tried:

7.3.4. pg_dump, 7.4.1 psql
7.3.4. pg_dump, 7.4.1 pg_restore
7.3.4. pg_dump, 7.3.4 pgsql (on 7.4 database)
7.4.1. pg_dump, 7.4.1 psql
7.3.4. pg_dump, 7.3.6 pgsql/pg_dump, 7.4.1 psql

All failed with "invalid command \N". I'm curious as well if it worked for you....

Collapse
6: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 5)
Posted by Steve Manning on 03/11/04 02:08 PM
Is it something in your data thats being inteprepted incorrectly? Are you dumping blobs as well? Can you see the \N in the file?

If your not dumping blobs you might also want to try a different --format.

I did an upgrade from 7.3 to 7.4 by dumping plaintext in 7.3 then upgrading to 7.4 and importing and that worked fine (it wasn't an OpenACS instance tho).

You might also want to try two dumps one for data (-a) and one for schema (-s) to see if you can narrow it down.


    - Steve

Collapse
7: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 5)
Posted by Bruno Mattarollo on 03/11/04 02:54 PM
I did a pg_dump from 7.3.4 (server running RH 9) and then psql into 7.4 (on Panther -Mac OS X 10.3-) and it worked fine, no errors.

PG 7.4.2 was released today, compiling it and will let you know when I have time to try that as well.

/B

Collapse
8: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 7)
Posted by Tilmann Singer on 03/11/04 03:26 PM
I did a restore of a pg 7.4.1 dump on a pg 7.4.1 database recently and saw the same error. It turned out that it works when omitting the manual loading of postgresql.sql before restoring the dump, maybe the same applies to upgrades.
Collapse
9: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 7)
Posted by C. R. Oldham on 03/11/04 04:56 PM
<blockquote> PG 7.4.2 was released today
</blockquote>

Note this is already in Debian Unstable.  I upgraded to it today.

Collapse
10: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 8)
Posted by Malte Sussdorff on 03/11/04 05:46 PM
Tilmann, you are hereby declared saviour of the day :). It works.

Now someone has to make a note on that in the documentation....

Collapse
11: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 9)
Posted by C. R. Oldham on 03/12/04 04:40 PM
When browsing the HISTORY file for 7.4.2 I discovered the following:

[...] A dump/restore is not required for those running 7.4.X.  However, it may be advisable as the easiest method of incorporating fixes for two errors that have been found in the initial contents of 7.4.X system catalogs. A dump/initdb/reload sequence using 7.4.2's initdb will automatically correct these problems. [...]

The HISTORY file documents how to manually fix the problems without a dump/initdb/reload.

Oliver Elphick, the Debian Pg package maintainer informs me that if you were running the 7.4.1 Debian package before, the 7.4.2 package does NOT automatically install these fixes.

Collapse
12: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 10)
Posted by Mark McI on 09/17/04 03:43 AM
Hello Malte,

I am experiencing this EXACT problem.  I see that Tillman suggested omitting the manual loading of postgresql.sql. Can you briefly explain what that is?

I am dumping with pg_dump and restoring with the following command:
psql -e dbname < db_dump.sql

Any help you can provide will be MUCH appreciated as I'm inexperienced with postgres and totally stumped.

Collapse
13: Re: Upgrade PG 7.3.4 to 7.4.1 (response to 12)
Posted by Rocael Hernández Rizzardini on 09/17/04 05:49 AM
do
psql -f packages/acs-kernel/sql/postgresql/postgresql.sql dbname

(that should do the trick)

when I restore a dump, usually do something like:
psql -f dump.file dbname