PROBLEM:
After upgrading PostgreSQL from 7.4 to 8.2 it started crashing sporadically with the following entries in the log file:
LOG: server process (pid 21460) was terminated by signal 11 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing shared memory and semaphores
CAUSE:
There were changes between PostgreSQL releases in functions definition used by pgcrypto module.
According to [1] and [3] in PostgreSQL 8.0 the pgcrypto functions were non-strict and checked for NULLs, in PostgreSQL 8.1 they were made STRICT, in PostgreSQL 8.2 the NULL checks were removed from code.
SOLUTION:
In order to correct the problem these functions are needed to be recreated. Required SQL commands are defined in pgcrypto.sql which is usually located in
/usr/local/pgsql/share/contrib when pgcrypto is installed.
psql -d DBNAME -f /usr/local/pgsql/share/contrib/pgcrypto.sql
Also it is a good idea to add functions to template1 database if you use pgcrypto often. template1 is the default source database name when using CREATE DATABASE command.
