Re: Monitoring Replication - Postgres 9.2

2016-12-01 5:54 GMT+13:00 Melvin Davidson <melvin6925(at)gmail(dot)com>:

>
> 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
>
> --
>

I see...

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?

Patrick