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 :-)

-- 
<http://linuxdatabases.info/info/slony.html>