Useful additional information - thanks!

Just checking but are the listener entries really correct?

Shouldn't they be

store listen (origin = 1, receiver = 2, provider = 1);
store listen (origin = 2, receiver = 1, provider = 2);
store listen (origin = 2, receiver = 3, provider = 2);
store listen (origin = 3, receiver = 2, provider = 3);

instead of

store listen (origin = 1, receiver = 2, provider = 1);
store listen (origin = 2, receiver = 1, provider = 2);
store listen (origin = 2, receiver = 3, provider = 1);
store listen (origin = 3, receiver = 2, provider = 1);

Or am I getting more confused than normal?

Many thanks to you both for your help.

John Sidney-Woollett

Christopher Browne wrote:

> John Sidney-Woollett wrote:
> 
>> Our live/production master db (master node) [node 2] server crashed 
>> yesterday, and we had to failover to the slave [node 1]. We're using 
>> pg 7.4.6 and slony 1.0.5 on linux.
>>
>> We had to failover (not switchover) to [node 1] because of the crash, 
>> and accept missing data (should be minimal at 3:45 am in our case), 
>> plus we were syncing at 10 sec intervals with almost no load at the time.
>>
>> We have a full dump (prior to the crash) from 3am (45 mins prior to 
>> the crash) and have loaded that into a new database. We intend to 
>> export each table in turn from both databases and diff the results to 
>> check for identical data up to the time of the last successful full 
>> dump to reassure ourselves.
>>
>> Question 1) Is a psql script writing the ordered table data to a file 
>> and then diff the files from the two databases the best way to do this?
>>
> That sounds about right.
> 
>> Question 2) We have noticed one sequence that wasn't being replicated 
>> (our mistake) and we want to add that to one of the existing sets. Is 
>> SET ADD SEQUENCE (to a new set), MERGE SET (new set, existing set) the 
>> correct way to do this? Can the new set ID be an existing one? And can 
>> the new ID be re-used after the merge command?
>>
> You can't add anything to a set that is being replicated.  If it's a set 
> that has no origin and no subscribers, then it ought to work.  That 
> pretty much means that if the set existed before, it needs to have been 
> dropped.
> 
> Once you MERGE SET, the merged set goes away; the ID could be reused for 
> some other purpose.
> 
>> Question 3) We want to check that all sequence values are >= max PK 
>> value for the tables in which they are used. Does anyone know how to 
>> check this without calling nextval() - is it possible to query the 
>> system tables (and if so how)?
>>
> /* cbbrowne@[local]/dba2 vc=*/ select * from domain_seq;
> sequence_name | last_value | increment_by | max_value  | min_value | 
> cache_value | log_cnt | is_cycled | is_called
> ---------------+------------+--------------+------------+-----------+-------------+---------+-----------+----------- 
> 
> domain_seq    |        100 |            1 | 2147483647 |         1 
> |           1 |       0 | f         | t
> (1 row)
> 
> Try:
>    select last_value from my_sequence;
> 
>> We then uninstalled the cluster and configured a new cluster where 
>> [node 1] is the master and [node 3] the new slave.
>>
>> Question 4) We now want to replicate from [node 3] to [node 2] when 
>> [node 2] comes back online. Therefore we want to change [node 2] to be 
>> a forwarder (which it currently is not), is this possible? Do we have 
>> to unregister, and re-register [node 3], before giving the subscribe 
>> commands for [node 2] to use [node 3] as its origin?
>>
> You should just need to submit some SUBSCRIBE SET requests to 
> reconfigure how those nodes are subscribing to the sets.  There is no 
> separate "MODIFY SUBSCRIPTION" command; it so happens that SUBSCRIBE SET 
> does revisions as well as doing brand new subscriptions.
> 
>> Question 5) When adding the new [node 2], is it sufficient to define 4 
>> new store and listener paths for the existing cluster, eg
>>
>> store path (server=1, client=2, conninfo='dbname=db1 host=dba user=pg');
>> store path (server=2, client=1, conninfo='dbname=db1 host=dbb user=pg');
>> store path (server=2, client=3, conninfo='dbname=db1 host=dbb user=pg');
>> store path (server=3, client=2, conninfo='dbname=db1 host=dbc user=pg');
>>
>> store listen (origin = 1, receiver = 2, provider = 1);
>> store listen (origin = 2, receiver = 1, provider = 2);
>> store listen (origin = 2, receiver = 3, provider = 1);
>> store listen (origin = 3, receiver = 2, provider = 1);
>>
> You always need to have n(n-1) listener paths, so that moving from 2 
> nodes to 3 means needing 6 sl_listen entries rather than 4.
> 
> Your additions look sensible.
> 
>> Apologies for the stupied questions, but we only installed slony two 
>> days before the crash so we're very green with it and a little worried 
>> about damaging our database...
>>
>> Any help or answers to the above questions would be gratefully 
>> appreciated.
>>
>> Thanks
>>
>> John Sidney-Woollett
> 
> 
> 
> As always, you can find the "admin guide" at the URL below.
> 
> New questions are welcome :-)
>