Re: Monitoring Replication - Postgres 9.2

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