Re: Monitoring Replication - Postgres 9.2

On Wed, Nov 30, 2016 at 8:04 AM, Cachique <cachique(at)gmail(dot)com> wrote:

> You can try pg_cron.
> https://github.com/citusdata/pg_cron
> "pg_cron is a simple cron-based job scheduler for PostgreSQL (9.5 or
> higher) that runs inside the database as an extension. It uses the same
> syntax as regular cron, but it allows you to schedule PostgreSQL commands
> directly from the database"
>
> It looks like what you want.
>
> Walter.
>
> On Tue, Nov 29, 2016 at 10:40 PM, Patrick B <patrickbakerbr(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 2016-11-30 14:21 GMT+13:00 John R Pierce <pierce(at)hogranch(dot)com>:
>>
>>> On 11/29/2016 5:10 PM, Patrick B wrote:
>>>
>>>
>>> Yep.. once a minute or so. And yes, I need to store a history with
>>> timestamp.
>>>
>>> Any idea? :)
>>>
>>>
>>> so create a table with a timestamptz, plus all the fields you want, have
>>> a script (perl? python? whatever your favorite poison is with database
>>> access) that once a minute executes those two queries (you'll need two
>>> database connections since only the slave knows how far behind it is), and
>>> inserts the data into your table.
>>>
>>>
>>> --
>>> john r pierce, recycling bits in santa cruz
>>>
>>>
>>
>> Can't I do it on the DB size? Using a trigger maybe? instead of using
>> Cron?
>>
>> Patrick
>>
>>
>
>The OP wants to run queries on the master and the slave, and combine them.

Another option, although a bit convoluted, would be to extract the data to
a csv file, scp it to destination server, and then copy in from there
eg:
Contents of bash script
===================
#!/bin/bash
psql -U postgres
\t
\f c
\o results.csv
select now() as time_pk,
client_addr,
state,
sent_location,
write_location,
flush_location,
replay_location,
sync_priority
from pg_stat_replication;
\q

scp results.csv destination_server/tmp/.

psql -U postgres -h destination_server/tmp/.
COPY data_table
FROM '\tmp\results.csv'
WITH csv;
\q

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.