Transferring slonified databases without slony

If you back up your Slony database with pg_dump, and try to reload it on a different machine (say transfer from a production system to a testing or benchmarking system), you've probably come across this problem more than once.

The dump will include all the Slony objects, with functions and triggers, that you simply cannot reload on a different machine - unless that machine also has Slony installed, and in the same way. A common way to do this is to just do a restore and ignore the errors - but if your database has lots of objects in it, that makes it very hard to spot actual errors - I always prefer to run with -1 and/or -e.

The first step to fix the problem is to exclude the Slony schema when dumping or restoring. That gets rid of most of the problem, but not all. There are still triggers in the main schemas that reference functions in the Slony schema, and now they will fail. Luckily, pg_restore has functionality to generate a table of contents from a dump, and then you can edit this table of contents file to exclude the triggers specifically. If your database isn't too complicated, you can easily script this.

Which brings me to the point of this post. It's actually very simple to script this, as long as the name of your slony schema doesn't conflict with other objects in your database (including the underscore). This is something that I know a lot of people keep doing manually (given the number of questions I hear about it when I say you should always use -e when restoring, for example). So here is a small semi-generic script that will do this for you - sed to the rescue. It simply comments out all the references to your slony schema.

#!/bin/bash

DUMP="full.dump"
DEST="1.2.3.4"
DESTDB="mydatabase"
USER="postgres"
CLUSTER="myclustername"
JOBS=4

echo Generating contents list...
pg_restore -Fc -l ${DUMP} |sed "s/.*_${CLUSTER}.*/;&/g" > list.txt
echo Starting restore...
pg_restore -j${JOBS} -Fc -e -v -L list.txt -h ${DEST} -U ${USER} -d ${MYDATABASE} ${DUMP}