View source for 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,

   /etc/init.d/canit-system stop-gracefully

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:
   05-START-BACKUP-TIME
   06-STOP-BACKUP-TIME
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

you want

   local   all  postgres   trust

7) Edit the /etc/postgresql/NEW/main/postgresql.conf so the values for
   listen_address
   max_connections

and perhaps
   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):
   /etc/init.d/postgresql-NEW restart

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:
     /etc/init.d/canit-system start

Mail now is processed.
<div style="float:right; clear:both; margin-right:0.5em">[[Support Wiki | [Home]]]</div>
[[category:All]][[category:Database]][[category:Upgrade]]