From: | John R Pierce <pierce(at)hogranch(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Monitoring Replication - Postgres 9.2 |
Date: | 2016-11-30 20:10:56 |
Message-ID: | 32157438-7b9a-e75b-48d7-76b9bab6dc9f@hogranch.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 11/30/2016 11:57 AM, Patrick B wrote:
>
> but there is queries like this:
>
> select now() - pg_last_xact_replay_timestamp() AS replication_delay;
>
>
> that need to be ran into a slave.. how can I insert that data into a
> table on the slave?
you would insert that data into a table on the MASTER, as the slave
can't be written directly to.
I would configure the slave to allow the master to connect to it for
monitoring purposes, then on the master, run a monitoring script that
looks something like...
connect to master as mdb
connect to slave as sdb
do forever
sql.query mdb, 'select now() as
time_pk,client_addr,state,sent_location,write_location,flush_location,replay_location,sync_priority
from pg_stat_replication'
sql.query sdb, 'select now() - pg_last_xact_replay_timestamp()
AS replication_delay'
sql.query mdb, 'insert into monitortable values (?,?,?,?...)',
time_pk,client_addr,state,sent_location,write_location,flush_location,replay_location,sync_priority,replication_delay'
sleep 1 minute
end
I've left out error handling, of course. and thats pseudocode, I'd
probably use perl, but python, php, java, even C++ could be used for
this, pretty much any language that can connect to the database and do
queries. I would NOT do this in a shell script as each interation would
involve multiple forks.
--
john r pierce, recycling bits in santa cruz
From | Date | Subject | |
---|---|---|---|
Next Message | John R Pierce | 2016-11-30 20:11:39 | Re: How to migrate from PGSQL 9.3 to 9.6 |
Previous Message | George | 2016-11-30 20:08:36 | Re: Index is not used for "IN (non-correlated subquery)" |