Problems upgrading from 7.1.3

>>>>> "Geoffrey" == Geoffrey Wossum <geoffrey@pager.net> writes:

Geoffrey> Hi all, I have a database cluster running on PostgreSQL
Geoffrey> 7.1.3 compiled from source, on Debian Linux. I want to
Geoffrey> upgrade the cluster to PostgreSQL 7.3.x.

Geoffrey> In order to get the data over, I ran: PGUSER=postgres
Geoffrey> /usr/local/pgsql/bin/pg_dumpall > survey1.sql on the
Geoffrey> production machine running 7.1.3.

Geoffrey> I then took that file over to test machine running
Geoffrey> PostgreSQL 7.3.1, and ran: PGUSER=postgres
Geoffrey> /usr/local/pgsql-7.3.1/bin/psql -d template1 -f survey1.sql

Geoffrey> I got lots of errors about "Invalid command \N" in the COPY
Geoffrey> xxx FROM statements, and most of the tables were completely
Geoffrey> empty.

I find that the whole database dumps do not have 'create user' and
'create group' commands.  Since I only have one group (admin) and many
users, I:

for i in `cat survey1.sql | egrep ^.connect\|^GRANT\|^REVOKE | rev |
    cut -d\  -f1 | tr -d \;\" | rev | sort -u | grep -v PUBLIC`; do
psql -c "create user $i" template1
done

... add grep -v's to taste.  Now... this doesn't recreate users with
'createuser' or 'createdatabase' permissions.  You may want to adjust
this.

Dave.

--
============================================================================
|David Gilbert, Velocet Communications.       | Two things can only be     |
|Mail:       dgilbert@velocet.net             |  equal if and only if they |
|http://daveg.ca                              |   are precisely opposite.  |
=========================================================GLO================