On 10/3/2010 1:34 AM, Guillaume Lelarge wrote: > Le 03/10/2010 07:07, Karl Denninger a écrit : >> On 10/2/2010 11:40 PM, Rajesh Kumar Mallah wrote: >>> I hope u checked point #11 >>> http://wiki.postgresql.org/wiki/Streaming_Replication#How_to_Use >>> >>> * *11.* You can calculate the replication lag by comparing the >>> current WAL write location on the primary with the last WAL >>> location received/replayed by the standby. They can be retrieved >>> using /pg_current_xlog_location/ on the primary and the >>> /pg_last_xlog_receive_location///pg_last_xlog_replay_location/ >>> on the standby, respectively. >>> >>> $ psql -c "SELECT pg_current_xlog_location()" -h192.168.0.10 (primary host) >>> pg_current_xlog_location >>> -------------------------- >>> 0/2000000 >>> (1 row) >>> >>> $ psql -c "select pg_last_xlog_receive_location()" -h192.168.0.20 (standby host) >>> pg_last_xlog_receive_location >>> ------------------------------- >>> 0/2000000 >>> (1 row) >>> >>> $ psql -c "select pg_last_xlog_replay_location()" -h192.168.0.20 (standby host) >>> pg_last_xlog_replay_location >>> ------------------------------ >>> 0/2000000 >>> (1 row) >>> >>> Regds >>> Rajesh Kumar Mallah. >> Yes, I did. >> >> Now how do I get an arithmetic difference between the two? There will >> (usually) be a small difference between the master and slave on a busy >> system - what I want to do is query both and if the difference in their >> locations is greater than some defined size, start raising hell (e.g. >> sending SMS to people, etc) >> >> I can SEE the difference, but I don't see a way to COMPUTE a difference, >> and there does not appear to be a function that will accept the log file >> location as an argument for conversion - the one documented for offsets >> (which might otherwise work) does not work on the slave as I noted. >> >> With Slony there was a set of system tables that would tell me how many >> unapplied changes were in the queue. From this I could determine health >> - if the number was more than some reasonably-small amount, something >> was broken and alarms were to be raised. >> >> I'm looking for a way to implement the same sort of functionality here. >> >> ticker=# select pg_last_xlog_replay_location(); >> pg_last_xlog_replay_location >> ------------------------------ >> 37A/327D1888 >> (1 row) >> >> ticker=# select pg_current_xlog_location(); >> pg_current_xlog_location >> -------------------------- >> 37A/3280DCB8 >> (1 row) >> >> How do I get an arithmetic difference between these two >> programmatically, and will such always be monoatomically increasing >> (that is, will they ever roll over, thereby giving me a potential >> NEGATIVE difference?) >> > pgPool-II does that computation. You should check that in its source > code. File pool_worker_child.c, function check_replication_time_lag(). > It creates a long value from the LSN returned by these functions. Here > is the computation: > > lsn = xlogid * 16 * 1024 * 1024 * 255 + xrecoff; > > In your example, xlogid is 37A and xrecoff is 327D1888 on the slave and > 3280DCB8 on the master. The hexadecimal values were first converted to > their unsigned decimal integer representation (same file, function > text_to_lsn()). > > You should really get a look at this file. It's probably a better > explanation than what I'm trying to do :) > Here's a little program to do it - change "MAX_OFFSET" and modify (particularly the "notify" function) to suit and place a file called "CHECK.cluster" in the following form in the home directory of whatever this runs as (it will look in the home directory of the euid of the process): master-name connection-string-to-connect-to-master slave1-name slave-string-to-connect slave2-name slave-string-to-connect ...... It will emit a notification for each slave that is more than "MAX_OFFSET" behind, or if any slave is not in recovery mode (bad; it thinks it's a master!) or worse, if the MASTER is in recovery mode, or if it can't talk to any of the declared nodes. Note that "CHECK.cluster" contains connection information so make sure you run this as someone and with a home directory for that someone appropriately permitted to prevent leakage of the connection credentials. Ain't gonna claim it's elegant, but it was something I was able to quickly bang out in a few minutes and it works. Stuck in the CRON if you have the cron's email going somewhere that will get noticed fast (e.g. a Blackberry email, etc) you can run this on whatever interval you need and it'll do the job. Postgresql's development team can consider this a contribution to the codebase if someone wants to clean it up a bit and include it. You could obviously pick up the config file as an argument rather than using the running euid of the process to grab it; I'm using it in an environment where the latter works better for me - YMMV. /* * check-replication - Checks the replication state of a Postgresql master/slave cluster, with multiple slaves and * one master. Each slave's streaming log location is checked against the master in turn, and if any are out of * sync by more than the allowed amount, or if we can't connect to the slave (or master), we emit a message * to that effect. Suitable for running out of cron as it's silent in the absence of something to report. * * Public domain - created 2010/10/3 by Karl Denninger (karl@xxxxxxxxxxxxx) * * No claim of elegance is made - it's quick, dirty, and it works. */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <syslog.h> #include <unistd.h> #include <sys/types.h> #include <pwd.h> #include <signal.h> #include <libpq-fe.h> #include "defs.h" #define MAX_OFFSET 1024000 /* Can be behind by 1MB */ PGconn *conn; PGconn *conn_slave; unsigned long convert_long(str) char *str; { char tmp[512]; char tmp2[512]; unsigned long offset; unsigned long c1, c2; sscanf(str, "%lx/%lx", &c1, &c2); offset = (c1 * 16 * 1024 * 1024 * 255) + c2; return(offset); } notify(string, user) char *string; { FILE *fid; char tmp[512]; printf("%s\n", string); return; } int main(argc, argv) int argc; char *argv[]; { PGresult *result; PGresult *result2; FILE *fid; struct passwd *pwd; char tmp[512]; char tmp2[512]; char master[512]; char slave[512]; unsigned long posmaster; unsigned long posclient; pwd = getpwuid(geteuid()); chdir(pwd->pw_dir); fid = fopen("CHECK.cluster", "r"); if (!fid) { printf("Error: Cannot open CHECK.cluster in base directory\n"); exit(1); } fscanf(fid, "%[!-~] %[!-~ ]", master, tmp); /* Get master */ conn = PQconnectdb(tmp); if (PQstatus(conn) == CONNECTION_BAD) { sprintf(tmp, "%s - %s", master, PQerrorMessage(conn)); notify(tmp); PQfinish(conn); exit(0); } result = PQexec(conn, "select pg_is_in_recovery()"); if (!PQntuples(result)) { sprintf(tmp, "ERROR: Master [%s] fails to give us recovery status - cannot continue", master); notify(tmp); exit(1); } strcpy(tmp, PQgetvalue(result, 0, 0)); PQclear(result); if (tmp[0] == 't') { sprintf(tmp, "ERROR: Master [%s] is in recovery - cannot continue", master); notify(tmp, pwd->pw_name); notify(tmp); exit(1); } while (fscanf(fid, " %[!-~] %[!-~ ]", slave, tmp) == 2) { conn_slave = PQconnectdb(tmp); if (PQstatus(conn) == CONNECTION_BAD) { sprintf(tmp, "%s - %s", slave, PQerrorMessage(conn)); notify(tmp); PQfinish(conn_slave); exit(0); } result = PQexec(conn_slave, "select pg_is_in_recovery()"); if (!PQntuples(result)) { sprintf(tmp, "ERROR: Slave [%s] fails to give us recovery status - cannot continue", slave); notify(tmp); exit(1); } strcpy(tmp, PQgetvalue(result, 0, 0)); PQclear(result); if (tmp[0] != 't') { sprintf(tmp, "ERROR: Slave [%s] is NOT in recovery - cannot continue", slave); notify(tmp); exit(1); } sprintf(tmp2, "select pg_last_xlog_replay_location()"); result = PQexec(conn_slave, tmp2); if (!PQntuples(result)) { sprintf(tmp, "ERROR: Slave [%s] returned no result on replay location", slave); notify(tmp); exit(1); } strcpy(tmp, PQgetvalue(result, 0, 0)); PQclear(result); posclient = convert_long(tmp); sprintf(tmp2, "select pg_current_xlog_location()"); result = PQexec(conn, tmp2); if (!PQntuples(result)) { sprintf(tmp, "ERROR: Master [%s] returned no result on replay location", master); notify(tmp); exit(1); } strcpy(tmp, PQgetvalue(result, 0, 0)); PQclear(result); posmaster = convert_long(tmp); if (posmaster > (posclient + MAX_OFFSET)) { sprintf(tmp, "ERROR: Client [%s] lags master by %ld", slave, posmaster - posclient); notify(tmp); } PQfinish(conn_slave); } PQfinish(conn); fclose(fid); exit(0); }
begin:vcard fn:Karl Denninger n:Denninger;Karl email;internet:karl@xxxxxxxxxxxxx note:Read The Market Ticker at http://market-ticker.org x-mozilla-html:TRUE version:2.1 end:vcard
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
