Re: [GENERAL] Monitoring Replication - Postgres 9.2

[John R Pierce]

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

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-12-01 5:54 GMT+13:00 Melvin Davidson :

>
> On Wed, Nov 30, 2016 at 8:04 AM, Cachique  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 
>> wrote:
>>
>>>
>>>
>>> 2016-11-30 14:21 GMT+13:00 John R Pierce :
>>>
 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

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Melvin Davidson]

On Wed, Nov 30, 2016 at 8:04 AM, Cachique  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 
> wrote:
>
>>
>>
>> 2016-11-30 14:21 GMT+13:00 John R Pierce :
>>
>>> 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.

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Cachique]

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

>
>
> 2016-11-30 14:21 GMT+13:00 John R Pierce :
>
>> 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
>
>

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[John R Pierce]

On 11/29/2016 6:01 PM, Melvin Davidson wrote:


There is no reason you can't execute a cron job on production to a 
remote db.


eg:
contents of cron
*/5 * * * *  psql -U postgres -h 123.4.56.789 -d remote_db_name -f 
/path_to/exec.sql

...


The OP wants to run queries on the master and the slave, and combine 
them.Maybe the master could connect to the slave with dblink but I 
hate relying on that.


also, the perl/python script I'm envisioning would have some error 
handling, for instance, if a connection is broken, attempt to 
reconnect.   if the master is up and the slave is down, use NULL for the 
replication_delay since it can't be evaluated.   If the master is down 
after connection retries, panic.


since its using persistent connections, it could execute these queries 
more frequently and track min/max/average sample values over the 
duration of the logging interval.


etc/etc.


--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Melvin Davidson]

On Tue, Nov 29, 2016 at 8:55 PM, John R Pierce  wrote:

> On 11/29/2016 5:40 PM, Patrick B wrote:
>
>>
>>
>> Can't I do it on the DB size? Using a trigger maybe? instead of using
>> Cron?
>>
>
> triggers are only called on database events like insert, update, select.
>  even something like the pgagent scheduler thats frequently bundled with
> pgadmin uses cron to run its master time process, which checks to see if
> there are any pending pgagent jobs and invokes them.
>
>
>
> for a every-minute event, i wouldn't use cron, I would write a little
> script/application in something like perl or python, which keeps persistent
> connections open, samples your data, inserts it, and sleeps til the next
> minute then repeats. running it from cron would require multiple
> process forks every sample, which is fairly expensive.
>
>
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



*There is no reason you can't execute a cron job on production to a remote
db.*

















*eg:contents of cron*/5 * * * *  psql -U postgres -h 123.4.56.789 -d
remote_db_name -f /path_to/exec.sqlcontents of
exec.sql==INSERT INTO your_table   SELECT now(),
 client_addr,  state,  sent_location,
 write_location,  flush_location,  replay_location,
 sync_priority from pg_stat_replication;*--
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[John R Pierce]

On 11/29/2016 5:40 PM, Patrick B wrote:



Can't I do it on the DB size? Using a trigger maybe? instead of using 
Cron?


triggers are only called on database events like insert, update, 
select.   even something like the pgagent scheduler thats frequently 
bundled with pgadmin uses cron to run its master time process, which 
checks to see if there are any pending pgagent jobs and invokes them.




for a every-minute event, i wouldn't use cron, I would write a little 
script/application in something like perl or python, which keeps 
persistent connections open, samples your data, inserts it, and sleeps 
til the next minute then repeats. running it from cron would require 
multiple process forks every sample, which is fairly expensive.





--
john r pierce, recycling bits in santa cruz



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-11-30 14:21 GMT+13:00 John R Pierce :

> 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

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[John R Pierce]

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

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

2016-11-30 14:02 GMT+13:00 John R Pierce :

> On 11/29/2016 3:31 PM, Patrick B wrote:
>
> I use these queries to monitor the streaming replication:
>
> *on master:*
> select client_addr, state, sent_location, write_location, flush_location,
> replay_location, sync_priority from pg_stat_replication;
>
> *On slave:*
> select now() - pg_last_xact_replay_timestamp() AS replication_delay;
>
> Can I create a table to store that data?
>
>
> sure, why not ? do you want this table to just have one row with the
> last value you stored?  or do you want to store a history with timestamp ?
>
> I also need the data is constantly put into this table. How would be the
> best way to do it?
>
>
> um, that data changes continuously, what do you mean, 'constantly'  ?   if
> you mean once a minute or something, use a script that samples the data and
> stores it in your table, and waits a minute, then repeats.if you mean
> literally continously, why not just query the data as you have, thats the
> 'live' value ...   you oculd use a view, I suppose.
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
Yep.. once a minute or so. And yes, I need to store a history with
timestamp.

Any idea? :)

Thanks!

Re: [GENERAL] Monitoring Replication - Postgres 9.2

[John R Pierce]

On 11/29/2016 3:31 PM, Patrick B wrote:

I use these queries to monitor the streaming replication:

*on master:*
select client_addr, state, sent_location, write_location, 
flush_location, replay_location, sync_priority from pg_stat_replication;


*On slave:*
select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Can I create a table to store that data?


sure, why not ? do you want this table to just have one row with the 
last value you stored?  or do you want to store a history with timestamp ?


I also need the data is constantly put into this table. How would be 
the best way to do it?


um, that data changes continuously, what do you mean, 'constantly' ?   
if you mean once a minute or something, use a script that samples the 
data and stores it in your table, and waits a minute, then repeats.
if you mean literally continously, why not just query the data as you 
have, thats the 'live' value ...   you oculd use a view, I suppose.



--
john r pierce, recycling bits in santa cruz

[GENERAL] Monitoring Replication - Postgres 9.2

[Patrick B]

Hi guys,

I use these queries to monitor the streaming replication:

*on master:*
select client_addr, state, sent_location, write_location, flush_location,
replay_location, sync_priority from pg_stat_replication;

*On slave:*
select now() - pg_last_xact_replay_timestamp() AS replication_delay;

Can I create a table to store that data?
I also need the data is constantly put into this table. How would be the
best way to do it?

Cheers
Patrick