Database Update 9.0

From Roaring Penguin
Revision as of 13:31, 15 August 2017 by JohnMertz (talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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/

by inserting a "#" at the beginning of the line so it reads:

  #* * * * * root /usr/share/canit/failover/libexec/

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

you want

  local   all  postgres   trust

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


and perhaps


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.