Thanks for the tip Vivek but I have a lot of primary key columns that
were generated by Slony.

In addition, I always have to drop and create the database before
doing a restore (I can't use -c or -C).

After some tests, I found that this works (added -r option for pg_restore).

sudo -u postgres dropdb mydatabase
sudo -u postgres createdb mydatabase -O dbowner
sudo -u postgres pg_restore -Fc -d mydatabase -r -v mydatabase.bak

But the Cons of this approach are:
1. I get a bunch of messages that look like this:

pg_restore: NOTICE:  type "xxid" is not yet defined
DETAIL:  Creating a shell type definition.
pg_restore: creating FUNCTION xxidout(xxid)
pg_restore: NOTICE:  argument type xxid is only a shell
pg_restore: creating TYPE xxid
pg_restore: creating CAST CAST (xid AS _cg_configuration.xxid)
pg_restore: creating CAST CAST (_cg_configuration.xxid AS xid)

What is a shell type? Are these messages harmless?

I run slon and it seems to be happy.

2. I have to kill all the slon processes on the slaves otherwise the
dropdb will fail.





On Mon, 24 Jan 2005 13:37:00 -0500, Vivek Khera <vivek at khera.org> wrote:
> 
> On Jan 24, 2005, at 1:23 PM, Fiel Cabral wrote:
> 
> > Has anyone backed up and restored a PostgreSQL database that's being
> > replicated using Slony?
> 
> I just had the need to restore a pg_dump into a development box from
> the live server to do some extensive statistics.  Here's what I did:
> 
> create dump:
> 
>   pg_dump -v -U postgres -Fc -f mm.dump database
> 
> from the dump, generate a catalog:
> 
>   pg_restore -l mm.dump > mm.list
> 
> edit mm.list to eliminate any slony-related stuff: the schemas, the
> functions, the triggers, etc.  Basically, if it was not part of my
> application, out it went.
> 
> Restore using the resulting catalog:
> 
>   pg_restore -v -U postgres -L mm.list -d database mm.dump
> 
> This assumes that slony did not have to add any primary key columns to
> your tables.  It is better to have them already defined in your schema
> before setting up slony.
> 
> _______________________________________________
> Slony1-general mailing list
> Slony1-general at gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/slony1-general
>