Fiel Cabral <e4696wyoa63emq6w3250kiw60i45e1 at gmail.com> writes:
> Thanks for the tip Vivek but I have a lot of primary key columns
> that were generated by Slony.

"Best practices" involve putting primary keys on yourself, as various
grief is possible if you leave some other system responsible for
primary keys.

If you can avoid having Slony-I do that, it would be a good thing.

> 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.

The notices are not a problem.  What I would be concerned about is the
handling of OIDs; the table IDs will change when you recover the
database, and Slony-I _does_ care about that.

Could you elaborate on what you are doing when you restore the
database?  

I find it pretty surprising that you are using pg_restore and
expecting to use the resultant database for replication straight away.

In fact, it seems curious that you would need to do so; are you
running into such severe failures in your environment that it makes
sense to routinely recover nodes in this manner?

What I would have expected you to do is to indicate that the node had
failed, perhaps doing a FAILOVER to let another node take over, and
then reinitialize the failed node.  That's the usual "use" for
Slony-I...

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

Yes, indeed, that is the case.  If there are any incoming connections,
you can't drop a database.
-- 
"cbbrowne","@","ca.afilias.info"
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 673-4124 (land)