Upgrading a PostgreSQL server version 7.x to version 8.x was relatively painless. It helped that there wasn't a significant amount of data to move, or blob objects to move.
For a Debian server on which I recently ran 'apt-get dist-upgrade', the Debian crew made
PostgreSQL 8.2 run along side version 7.4. The /etc/postgresql/8.2/main/postgresql.conf
file showed the server port as being 5433, by default. Since the service wasn't turned on,
I set it for 5432, and changed the listen_addresses for '*
'.
Relevant lines from the pg_hba.conf files will have to coped from the
/etc/postgresql/7.x/main/ directory to the matching file in the 8.x directory. Some say
that running pgadmin3 will provide messages indicating exactly which paramters need to be
copied over. I havn't tried that.
The commands following are used after 'su - postgres' (logging in as the postgreSQL user
account. There are a number of ways of performing backups and restores. The following
sequence of steps takes a little longer, but I wield a bit more control over what gets done
when.
With 7.4 running and 8.2 off, I did a 'pg_dumpall --schema-only > db.dump'. The top of
the file shows a number of 'create role' lines for regenerating users.
Do a 'pg_dump databasename > databasename.db.dump' for each database (where you
substitute the real name for databasename) to extract the schema and data. A 'psql -l' will
list the databases available.
Use '/etc/init.d/postgresql-7.4 stop' to stop the existing service. Use 'apt-get remove
xxx' to remove the various old version packages. 'apt-file -l list postg' will provide an
indication of what can be removed (I'm sure there is a better way though).
Use '/etc/init.d/postgresql-8.2 start' to start the more recent version of PostgreSQL.
Use 'psql template1' to connect to the server. Run the 'create role' lines that you see
from the pg_dumpall command run earlier. '\q' to exit.
Use 'createdb -T template0 --owner=ownername databasename' to create each of the
databases.
Use 'psql databasename < databasename.db.dump to restore the data.
Data and schema are now ready to be used as if nothing happened.