Search Postgresql Archives

Re: Monitoring Replication - Postgres 9.2

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux