Date: 19-12-07 05:53
This applies mostly to upgrading PostgreSQL when you need to do a dump and a restore. In this case, I was upgrading from 8.1 to 8.2. This approach requires downtime. I did this tonight. I am repeating the steps from memory, so I hope I didn't miss anything.
Upgrade postgresql-client first. This will give you the pg_dumpall for the target database.
pg_dump -U pgsql > db.out
Stop the server: /usr/local/etc/rc.d/postgresql stop
mv the existing data directory out of the way:
mv data data.8.1
You will need files from there. For me, it was server.crt, server.key, pg_hba.conf, and postgresql.conf.
Upgrade the server.
Init the db: /usr/local/etc/rc.d/postgresql stop
copy over your files that you saved (see above). You may wish to copy/paste the bits you need.
Start the server: /usr/local/etc/rc.d/postgresql start
Upgrade docs + contrib if you need/want them
load the data: psql -U postgres < db.out