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.

Add comment

New comments can no longer be posted on this entry.

Conferences

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

Upcoming

PGDay Chicago 2024
Apr 26, 2024
Chicago, USA
PGConf.DEV 2024
May 28-31, 2024
Vancouver, Canada

Past

SCaLE 2024
Mar 14-17, 2024
Pasadena, USA
Nordic PGDay 2024
Mar 12, 2024
Oslo, Norway
FOSDEM PGDay 2024
Feb 2-4, 2024
Brussels, Belgium
PGConf.EU 2023
Dec 12-15, 2023
Prague, Czechia
PGConf.NYC 2023
Oct 3-5, 2023
New York, USA
More past conferences