Re: Monitoring Replication - Postgres 9.2

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