postgres upgrade for dummies?

[Andreas Goesele <Goesele@hfph.mwn.de>]

Hi,

Short story: Is there an easy way to upgrade from postgresql 6.5 to
7.1? An easy to follow document explaining how to do it?

Long story: Many tries, no success:

I have a nicely working postgresql 6.5 on Debian woody. When I first
upgraded to woody I too tried to upgrade to postgresql 7.1, but I
couldn't get my databases working. So with some work I went back to
6.5.

I now did a new try, again without any success. The automatic
upgrading process first looks promising but than ends with the
following error message:

You are now connected to database template1.
select datdba into table tmp_pg_shadow       from pg_database where datname = 'template1';
SELECT
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
DELETE 0
drop table tmp_pg_shadow;
DROP
copy pg_shadow from stdin;
You are now connected to database template1 as user user.
create database user;
ERROR:  parser: parse error at or near "user"
\connect: FATAL 1:  Database "user" does not exist in the system catalog.
Reload failed
Restarting PostgreSQL database: postmaster
Stopped /usr/lib/postgresql/bin/postmaster (pid 28000).
Starting PostgreSQL postmaster.
postmaster successfully started
.


dpkg: Fehler beim Bearbeiten von postgresql (--configure):
 Unterprozess post-installation script gab den Fehlerwert 130 zurück
Fehler traten auf beim Bearbeiten von:
 postgresql
E: Sub-process /usr/bin/dpkg returned an error code (1)

Afterwards it's impossible to start postmaster and connect to it.

I turnt successfully back to 6.5 and tried it a second time: No I only
got:

Do you want me to move your old configuration to the new files? [Y/n]
Restarting PostgreSQL database: postmaster
No /usr/lib/postgresql/bin/postmaster found running; none killed.
The database is in an older format that cannot be read by
version 7.1 of PostgreSQL.

Run postgresql-dump to dump the old database and to reload
it in the new format.
*** READ /usr/share/doc/postgresql/README.Debian.migration.gz FIRST! ***

The version 7.1 postmaster cannot be started until
this is done.
.

So I did read README.Debian.migration.gz and then run postgresql-dump
with the options in the README. This is what I got:

Stopping and restarting the postmaster
/usr/lib/postgresql/dumpall/6.5/postmaster -D /var/lib/postgres/data  -p 5431 -o -d0
Dumping the database to db.out
process_hba_record: invalid syntax in pg_hba.conf file
Missing or erroneous pg_hba.conf file, see postmaster log for details
Connection to database 'template1' failed.
Missing or erroneous pg_hba.conf file, see postmaster log for details
process_hba_record: invalid syntax in pg_hba.conf file
Missing or erroneous pg_hba.conf file, see postmaster log for details
Connection to database 'template1' failed.
Missing or erroneous pg_hba.conf file, see postmaster log for details
process_hba_record: invalid syntax in pg_hba.conf file
Missing or erroneous pg_hba.conf file, see postmaster log for details
Connection to database 'template1' failed.
Missing or erroneous pg_hba.conf file, see postmaster log for details
Finding the default encoding
process_hba_record: invalid syntax in pg_hba.conf file
Missing or erroneous pg_hba.conf file, see postmaster log for details
Connection to database 'template1' failed.
Missing or erroneous pg_hba.conf file, see postmaster log for details

Usage: pg_encoding encoding_name | encoding_number

Killing the postmaster
This is the ASCII output of the dump for you to check:

-- postgresql-dump on Sun Feb 17 01:28:21 CET 2002 from version 6.5
\connect template1
select datdba into table tmp_pg_shadow       from pg_database where datname = 'template1';
delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
drop table tmp_pg_shadow;
copy pg_shadow from stdin;
\.
-- postgresql-dump completed on Sun Feb 17 01:28:22 CET 2002
On the basis of this dump, is it OK to delete the old database? [y/n] n

(The last "n" is mine, as the dump didn't look promising at all ...)

Any suggestions?

Andreas Goesele