Upgrading PostgreSQLFrom time to time, I need to upgrade PostgreSQL. When you are upgrading between minor revisions, you must dump and restore your databases. This article shows how I do this with FreeBSD 8.x and PostgreSQL 8.3, but this should be applicable to most other operating systems and versions of PostgreSQL. NOTE: If, for example, you are moving from PostgreSQL 8.3.7 to 8.3.9, you do not have to dump. But between minor revisions, for example 8.3.x to 8.4.x, you must dump and reload.
The outlineIn short, the process is this:
- Upgrade the PostgreSQL client
- Use the *new* client to dump the *old* data
- Shutdown the PostgreSQL server
- Upgrade the PostgreSQL server
- initdb the PostgreSQL server
- load the databases
- vacuum analyse
Upgrade the PostgreSQL clientWhen you move from one PostgreSQL minor version to another, you must dump and reload using the *new* PostgreSQL client. Why? The new client will know the format expected by the new sever. The old client may not be aware of the specifics. I make sure I have the latest ports tree by running portsnap first:
portsnap fetch && portsnap updateThen I upgrade the client, and only the client.
portupgrade -fo databases/postgresql84-client postgresql-clientI think I added in the -f because my existing client was not the latest version of PostgreSQL 8.3.
Use the *new* client to dump the *old* dataI use pg_dumpall to get everything out of the server:
pg_dumpall > dumpall.sql
Shutdown the PostgreSQL serverShutting down the server is easy:
Upgrade the PostgreSQL serverUpgrading is just as easy:
portupgrade -fo databases/postgresql84-server postgresql-serverIf installed, you may want to:
initdb the PostgreSQL serverBefore I run initdb, I move the old data directory out of the way. I elect to save it and remove it later, rather than delete it now. This allows me to downgrade to the old version of PostgreSQL should I need to. If you choose to remove the data directory, I recommend saving any configuration files first (e.g. postgresql.conf, pg_hba.conf, etc).
cd /usr/local/pgsql mv data data.8.3.9Now, the initdb:
/usr/local/etc/rc.d/postgresql initdbNOTE: you may wish to configure /usr/local/pgsql/data/postgresql.conf before starting PostgreSQL, especially if you had some custom settings for your old version. And then starting PostgreSQL again:
Load the databasesI like this approach for loading up the databases:
su pgsql psql template1 < dumpall.sql
vacuum analyseThe vacuum analyse lets PostgreSQL update statistics used for query planning. This should always be done after a load.
$ psql regress psql (8.4.2) Type "help" for help. regress=# vacuum analyse;