> Hi,
> I am working on failover. But I am having problem to restore original
> master as
> a subscriber.
> Eventually once I restore original master as a subscriber I will do
> switchover
> from new master to
> original master so that I will have initial slony-I replication set up.
>
> Here is what I am doing
> S1. dropdb and createdb on all node1(master),node2,node3
> S2. store DB from dump.sql
> S4. copy schema from master to slaves
> ./pg_dump -s -U enterprisedb -h $MASTERHOST -p $MASTERDBPORT $MASTERDBNAME
> |
> ./edb-psql -U $REPLICATIONUSER -h $SLAVEHOST1 -p $SLAVEDBPORT1
> $SLAVEDBNAME1
> ./pg_dump -s -U enterprisedb -h $MASTERHOST -p $MASTERDBPORT $MASTERDBNAME
> |
> ./edb-psql -U $REPLICATIONUSER -h $SLAVEHOST2 -p $SLAVEDBPORT2
> $SLAVEDBNAME2
> S5. initalize cluster/tables on master
> S6. start relication on master
>
> S7. start replication on node2
> S8. subscribe on node2
>
> S9. start replication on node3
> S10.subscribe on node3
>
> S11.test replication works fine where node1 is master and node2 and node3
> are slaves
>
> S12.shutdwon postgres(edb) on node1(master) as if crashed
> S13.run failover on node2(new master) (I have to re-run setsequences for
> all
> tables for insertions)
>
> S14.test replication works fine where node2 is new master and node3 is the
> only
> slave
>
> S15.drop node 1 (original master)
>
> S16.CANNOT RESTORE ORIGINAL MASTER (node1) from scratch as a subscriber
> here.
> (Here I am trying to same thing when I do to store slaves for intial set
> up,
> copying the schema from new master(node2) to node1 and start replication
> so that I can start subscribe but
>
> 1.when I try to copy schema from node2 to node1 I get some ERRORs
> (taking dump to copy schema(only) from new master node2 to node1)
> ./pg_dump -s -U enterprisedb -h $SLAVEHOST2 -p $SLAVEDBPORT2 $SLAVEDBNAME2
> |
> ./edb-psql -U $REPLICATIONUSER -h $MASTERHOST -p $MASTERDBPORT
> $MASTERDBNAME
> ....
> CREATE FUNCTION
> ERROR:  syntax error at or near "IS" at character 34
> LINE 1: COMMENT ON FUNCTION cleanupevent IS 'cleaning old data out o...
> ...
> ....
> CREATE FUNCTION
> ERROR:  syntax error at or near "IS" at character 32
> LINE 1: COMMENT ON FUNCTION denyaccess IS 'Trigger function to preve...
> ...
> and for other functions too.

That is fairly unsurprising.  If you dump just the public schema, you'll
not get the functions defined in the namespace used by Slony-I.  And so
various triggers/functions that SLony-I uses will fail to load.

> Here should I be taking dump with (-n public) option?
> ./pg_dump -s -U enterprisedb -h $SLAVEHOST2 -p $SLAVEDBPORT2 $SLAVEDBNAME2
> -n
> public | ./edb-psql -U $REPLICATIONUSER -h $MASTERHOST -p $MASTERDBPORT
> $MASTERDBNAME
> Or thoses errors no concern?

They are a problem; you shouldn't be using any of this to extract the
"master" schema.

Look at the docs for how to add a node.
<http://linuxfinances.info/info/addthings.html>

You should NOT be using pg_dump; you should be using
slony1_extract_schema.sh, run against the origin node, to get the proper
schema.

> 2. Then I start replication but gives the mesage below
> [enterprisedb at baba1 bin]$ ./edb-replication $CLUSTERNAME
> "dbname=$MASTERDBNAME
> user=$REPLICATIONUSER host=$MASTERHOST port=$MASTERDBPORT"
> 2006-08-05 12:36:12 CDT CONFIG main: edb-replication version 1.1.5
> starting up
> 2006-08-05 12:36:12 CDT FATAL  main: Node is not initialized properly

If all you have done is to restore part of the schema, then you have not
got a properly initialized node.

The steps are as described in 12.4 in "addthings.html"...

- Get a proper dump of the origin node's schema using the provided script
in /tools/

- Use DROP NODE to clean out old configuration for the busted node.

- Create the new database, load the schema dump into it

- Use STORE NODE to tell Slony-I about it

- Use STORE PATH to reconfigure paths for this node

- Start up a slon for the new node

- Use SUBSCRIBE SET to resubscribe the node to whatever sets you want
replicated

You mention some tools that seem to be specific to EnterpriseDB; there may
be variations that will require that you contact the vendor...