|
Things look quiet here. But I've been doing a lot of blogging at
dan.langille.org because I prefer WordPress now.
Not all my posts there are FreeBSD related.
I am in the midst of migrating The FreeBSD Diary over to WordPress
(and you can read about that here).
Once the migration is completed, I'll move the FreeBSD posts into the
new FreeBSD Diary website.
|
|
|
|
|
Upgrading PostgreSQL
21 December 2009
|
|
|
From 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 outline
|
|
In 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 client
|
|
When 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 update
Then I upgrade the client, and only the client.
portupgrade -fo databases/postgresql84-client postgresql-client
I 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* data
|
|
I use pg_dumpall to get everything out of the server:
pg_dumpall > dumpall.sql
|
|
|
Shutdown the PostgreSQL server
|
|
Shutting down the server is easy:
/usr/local/etc/rc.d/postgresql stop
|
|
|
Upgrade the PostgreSQL server
|
|
Upgrading is just as easy:
portupgrade -fo databases/postgresql84-server postgresql-server
If installed, you may want to:
portupgrade postgresql-contrib
|
|
|
initdb the PostgreSQL server
|
|
Before 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.9
Now, the initdb:
/usr/local/etc/rc.d/postgresql initdb
NOTE: 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:
/usr/local/etc/rc.d/postgresql start
|
|
|
Load the databases
|
|
I like this approach for loading up the databases:
su pgsql
psql template1 < dumpall.sql
|
|
|
vacuum analyse
|
|
The 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;
|
|
|
Pretty easy
|
|
Upgrading PostgreSQL is fairly straight forward. I particularly like the ease
of the FreeBSD ports tree for this purpose. I can upgrade the client first,
then the server later.
In a perfect world, the dump and load would not be required. One day....
|
|
