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 semaphoresCause
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.sqlAlso 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.
