Database Update 9.0
For the purposes of this article:
- "VERSION" indicates the old version number, e.g. 8.3
- "NEW" is used to indicate the version being upgraded to, e.g. 9.0
For any given Debian version, the latest PostgreSQL available can be listed via the command
apt-cache search postgresql | grep "^postgres"
1) Stop CanIt: If you have a Cluster, stop CanIt on all members,
Mail will be tempfailed for the duration.
2) If you have an existing database failover setup, disable any automatic failover in /etc/cron.d/canit-failover by commenting-out the line
* * * * * root /usr/share/canit/failover/libexec/canit-failover-check-master.pl
by inserting a "#" at the beginning of the line so it reads:
#* * * * * root /usr/share/canit/failover/libexec/canit-failover-check-master.pl
3) Make a fresh DataBase dump file:
pg_dump -U postgres spam > spam-dump-file.txt
How long that will take can be guesstimated from the timestamps from:
ls -l /var/spool/Canit-Spam-DB-Backup/
Look for the files:
The difference in times between START and STOP will give an indication of the time to dump the database.
4) Make a copy of /etc/postgresql/VERSION/, Just In Case, and to use for configuration reference:
cp -r /etc/postgresql/VERSION/ /etc/postgresql/VERSION-old
5) Install the new PostgreSQL version, while at the same time removing the VERSION one (Note the minus sign after the old version number):
apt-get update apt-get install postgresql-NEW postgresql-client-NEW postgresql-VERSION- postgresql-client-VERSION-
e.g. apt-get install postgresql-9.0 postgresql-client-9.0 postgresql-8.3- postgresql-client-8.3-
6) Review the old pg_hba.conf, and edit /etc/postgresql/NEW/main/pg_hba.conf to have the same (if any) "host" lines.
ALSO watch out for
local all postgres ident
local all postgres trust
7) Edit the /etc/postgresql/NEW/main/postgresql.conf so the values for
shared_buffers wal_level archive_mode
Match the values set in the old config file. The listen_address might have been automatically changed by the installer...
Note that max_fsm_pages has gone away for version 9...
8) Restart PostgreSQL (later versions have removed the '-NEW' from the init script name):
9) Create the spam user to own the spam database when it exists (It's mentioned in the dumpfile is why)
createuser -U postgres -S -D -R spam
10) Create an empty database:
createdb -U postgres -E sql-ascii -l C -T template0 spam
11) Then restore the database dump:
psql -U postgres spam < spam-dump-file.txt
This assumes your postgres root user is named 'postgres'. Adjust as necessary.
The restore takes significantly longer than the dump and is by far the longest part of the process. Typically, it takes twice as long to restore as to do the dump, possibly longer.
12) After a restore, you MUST analyze the database, or PostgreSQL will be extremely slow.
To do the analyze on your database:
psql -U postgres spam -c 'ANALYZE VERBOSE'
13) Finally, restart the stopped CanIt processes on all machines via:
Mail now is processed.