-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

kevin kempter a écrit :
> 
> On Aug 5, 2008, at 11:41 AM, Shahaf Abileah wrote:
> 
>> I ended up using a different approach...
>>
>> 1. I created a table called replication_heartbeat that has two columns:
>> id, last_modified
>> 2. I added this table to the replication set
>> 3. I pre-populated a single row into this table
>> 4. I set up a cron job on the master that executes the following query
>> once a minute: update replication_heartbeat set last_modified=now()
>> 5. I created Nagios alerts on both the master and the slaves that checks
>> this date.  On the master if the date goes stale then I know that
>> something is wrong with the update command in cron.  On the slaves if
>> the date is stale then I know something is wrong with replication.
>>
>> It's a poor man's approach and it's probably silly to do all this work
>> when there's a built-in mechanism.  The main advantage is that it's dead
>> simple to understand.
>>
>> --S
>>
>> -----Original Message-----
>> From: slony1-general-bounces at lists.slony.info
>> [mailto:slony1-general-bounces at lists.slony.info] On Behalf Of Vivek
>> Khera
>> Sent: Sunday, August 03, 2008 1:18 PM
>> To: kevin kempter
>> Cc: slony1-general at lists.slony.info
>> Subject: Re: [Slony1-general] how to determine if the slave is caught
>> up/insync with the master
>>
>> On Sun, Aug 3, 2008 at 12:35 PM, kevin kempter
>> <kevin at kevinkempterllc.com> wrote:
>>> Hi List;
>>>
>>> How do I know if a slave is up to date with the master ?
>>>
>>
>> On the master, run this SQL query, replacing _CLUSTERNAME with the
>> name you selected for your cluster.
>>
>> select * from _CLUSTERNAME.sl_status;
>> _______________________________________________
>> Slony1-general mailing list
>> Slony1-general at lists.slony.info
>> http://lists.slony.info/mailman/listinfo/slony1-general
>>
>>
> 
> 
> Is there  documentation someone can point me towards that defines with
> some detail all the columns of the sl_status table, or even better all
> the _CLUSTERNAME tables ?

You could have a look at the official documentation :

http://slony.info/documentation/schema.html

I'm afraid though that sl_status which is a view is not documented there...

A \d will tell you more :

cave2=# \d _test.sl_status
                       View "_test.sl_status"
          Column           |            Type             | Modifiers
- ---------------------------+-----------------------------+-----------
 st_origin                 | integer                     |
 st_received               | integer                     |
 st_last_event             | bigint                      |
 st_last_event_ts          | timestamp without time zone |
 st_last_received          | bigint                      |
 st_last_received_ts       | timestamp without time zone |
 st_last_received_event_ts | timestamp without time zone |
 st_lag_num_events         | bigint                      |
 st_lag_time               | interval                    |
View definition:
 SELECT e.ev_origin AS st_origin, c.con_received AS st_received, e.ev_seqno AS
st_last_event, e.ev_timestamp AS st_last_event_ts, c.con_seqno AS
st_last_received, c.con_timestamp AS st_last_received_ts, ce.ev_timestamp AS
st_last_received_event_ts, e.ev_seqno - c.con_seqno AS st_lag_num_events, now()
- - ce.ev_timestamp::timestamp with time zone AS st_lag_time
   FROM _test.sl_event e, _test.sl_confirm c, _test.sl_event ce
  WHERE e.ev_origin = c.con_origin AND ce.ev_origin = e.ev_origin AND
ce.ev_seqno = c.con_seqno AND ((e.ev_origin, e.ev_seqno) IN ( SELECT
sl_event.ev_origin, max(sl_event.ev_seqno) AS max
           FROM _test.sl_event
          WHERE sl_event.ev_origin = _test.getlocalnodeid('_test'::name)
          GROUP BY sl_event.ev_origin)) AND ((c.con_origin, c.con_received,
c.con_seqno) IN ( SELECT sl_confirm.con_origin, sl_confirm.con_received,
max(sl_confirm.con_seqno) AS max
           FROM _test.sl_confirm
          WHERE sl_confirm.con_origin = _test.getlocalnodeid('_test'::name)
          GROUP BY sl_confirm.con_origin, sl_confirm.con_received));

I think column name are self-explanatory.

Best regards,
- --
Stéphane Schildknecht
PostgreSQLFr : http://www.postgresql.fr

Venez nous rencontrer le 4 octobre lors du plus important événement
PostgreSQL francophone : http://www.pgday.fr

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFImJk+A+REPKWGI0ERAnTXAJ9mJcdpC9ImkNVfHwdgw06jE7WAzACeLMKr
tBtK6m6YVvZ/rBzokrp0uq4=
=IxNQ
-----END PGP SIGNATURE-----