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}

I know, it's a trivial script, and there are a bunch of pitfalls to it. But in a lot of the simple cases, it will work perfectly fine! If nothing else, it can hopefully serve as a good starting point!


Comments

Well, go check the catalog command of pg_staging.

http://github.com/dimitri/pg_staging

What it does is filter out the schemas you didn't put in the schemas .INI file setup, and it does so by reading through the catalog and filtering objects that belong in the schema.

A two pass filter is needed, because you want to avoid importing triggers that depend on functions that are in the schema you're not restoring, and that's exactly what pg_staging is doing.

Now what you have to do is write a .INI configuration file with a section describing the database you're working on. Then

$ pg_staging catalog ini_section path/to/file.dump > filtered.cat $ pg_restore -L filtered.cat ...

pgstaging is now in debian and comes with man pages for the commands and the setup.

Hope you'll enjoy,

CAVEAT: yeah you have to have been putting Slony aside on its own schema, not in public with your database objects. You did, right?

Posted on Dec 14, 2009 at 22:49 by dim.

There is also a script in the Slony source tree by the name of slony1_extract_schema.sh that attempts to solve this problem. It works by copying the schema into a temporary database, dropping the replication schema, and then re-dumping the schema. Might be worth comparing.

Posted on Jan 25, 2010 at 11:01 by Peter Eisentraut.

I tried running this script but it still didn't solve the issue. Is there any other solution you can recommend?

Posted on Mar 20, 2010 at 10:20 by Leigh.

The slony one or the pg_staging one?

Posted on Mar 27, 2010 at 22:19 by dim.

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
Postgres Vision 2016
Oct 11-Nov 13, 2016
San Francisco, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia
PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan

Past

PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
PGDay UK 2016
Jul 05, 2016
London, UK
Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGCon
May 17-21, 2016
Ottawa, Canada
Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
More past conferences