2016-12-01 5:54 GMT+13:00 Melvin Davidson <melvin6...@gmail.com>: > > On Wed, Nov 30, 2016 at 8:04 AM, Cachique <cachi...@gmail.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 <patrickbake...@gmail.com> >> wrote: >> >>> >>> >>> 2016-11-30 14:21 GMT+13:00 John R Pierce <pie...@hogranch.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
