Postgres:Upgrading

FreeBSD

• Upgrade the clients utilities from the ports in /usr/ports/databases/postgresql-version-client
• Shutdown the server and restart it.

pg_ctl -m fast stop && pg_ctl start

You might have to do a

postmaster -D /usr/local/pgsql/data

if there's a problem with the new client utilities when you're starting the server.

• Do a dump of all databases as the pgsql user:

pg_dumpall > pg.dump

• Backup all the configuration files. If you are the pgsql user:

cp $HOME/data/*.{conf,key,crt} $HOME

• Upgrade the server from the port in /usr/ports/databases/postgresql-version-server
• Delete the old database directory:

rm -fr $HOME/data

• Remake it:

initdb && cp $HOME/*.{conf,crt,key} $HOME/data

• Start the server as the pgsql user:

pg_ctl start

• Start the restore as the pgsql user:

createdb pgsql && psql < pg.dump