During the upgrade of a machine from Debian 3.1 to Ubuntu 8.10 I did run into a pitfall that was quite surprising to me. One aspect of this was the upgrade from PostgreSQL 7.4 to PostgreSQL 8.3. The problem is that PostgreSQL backups have a dependency on the machine where the backup was created.
Because I wanted to re-partition the machine, I created a full backup of the machine and wiped out all the existing partitions. For two existing PostgreSQL databases I created backups with pg_dump. In the online PostgreSQL documentation I read the section 24.5. Migration Between Releases, and there it says: "It is recommended that you use the pg_dump and pg_dumpall programs from the newer version of PostgreSQL, to take advantage of any enhancements that might have been made in these programs." In order to comply with that recommendation, I downloaded a current 8.3 binary package from the EnterpriseDB download site, installed it on the machine and used the 8.3 pg_dump program to create backups of the two databases from the 7.4 database server.
Then the machine was installed from scratch with Ubuntu 8.10, and with that came the Ubuntu version of PostgreSQL 8.3. This is what happened when I tried to restore one of the databases with pg_restore:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 9; 1255 18035 FUNCTION
plpgsql_call_handler() postgres
pg_restore: [archiver (db)] could not execute query: ERROR: stat failed on
file »/usr/lib/postgresql/lib/plpgsql.so«: No such file or directory
As it turns out, in the Ubuntu version of PostgreSQL 8.3 the file "plpgsql.so" is installed under the pathname "/usr/lib/postgresql/8.3/lib/plpgsql.so". This is the relevant section from the backup file created by pg_dump:
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '/usr/lib/postgresql/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE c;
The workaround was to replace '/usr/lib/postgresql/lib/plpgsql.so' with '/usr/lib/postgresql/8.3/lib/plpgsql.so' with vi, while preserving the offsets in the file because these obviously matter to pg_restore. This is the corrected section (note the four missing blanks in the line starting with "AS"):
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '/usr/lib/postgresql/8.3/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE c;
When on my first attempt I just replaced one string with the other (which made the file four bytes longer), pg_restore crashed with a segmentation fault.
The lesson learned is that one should create the backups with pg_dump on the machine where the backups will potentially be restored, not necessarily on the machine where the PostgreSQL database server is running. As it is not always clear upfront on what machine with what PostgreSQL installation a backup will be restored, this problem can come as a surprise in the worst moment...
Disclaimer: I'm not a PostgreSQL expert. There might be ways I'm unaware of to prevent the creation of the machine dependency when creating the backup with pg_dump or to override the pathname of the library when doing the restore with pg_restore. At least there is no hint about the potential problem in the PostgreSQL in the section about migrating between releases.
