pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedMonitoring hot standby replication for failure
5+ messages / 5 participants
[nested] [flat]
* Monitoring hot standby replication for failure
@ 2019-03-25 17:50 Mark Steben <mark.steben@drivedominion.com>
2019-03-25 18:05 ` Re: Monitoring hot standby replication for failure Frank Alberto Rodriguez Solana <franknigth@gmail.com>
2019-03-25 20:24 ` Re: Monitoring hot standby replication for failure Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2019-03-26 02:14 ` Re: Monitoring hot standby replication for failure Shreeyansh Dba <shreeyansh2014@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Mark Steben @ 2019-03-25 17:50 UTC (permalink / raw)
To: pgsql-admin
Good afternoon,
We are running postgres 9.4. We are outsourcing our databases to another
datacenter owned by a third party company. The methodology we are using to
move the database is hot standby log shipping replication. We will run
streaming replication for several days then, when the time comes to pull
the trigger and make the move, I will complete recovery, bring in a
'production' version of postgresql.conf, restart the database, and hand it
over to the apps.
We want to make sure that during the several days of replication we are
immediately notified if a problem arises - a missing log, a production
outage, etc. Is there a monitor or an 'in-the-box' procedure, or perhaps a
condition to check during hot standby or streaming replication to ensure
things are still running as planned, or if there is a problem?
Thanks for any insights.
--
*Mark Steben*
Database Administrator
@utoRevenue <http://www.autorevenue.com/; | Autobase
<http://www.autobase.net/;
CRM division of Dominion Dealer Solutions
95D Ashley Ave.
West Springfield, MA 01089
t: 413.327-3045
f: 413.383-9567
www.fb.com/DominionDealerSolutions
www.twitter.com/DominionDealer
www.drivedominion.com <http://www.autorevenue.com/;
<http://autobasedigital.net/marketing/DD12_sig.jpg;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Monitoring hot standby replication for failure
2019-03-25 17:50 Monitoring hot standby replication for failure Mark Steben <mark.steben@drivedominion.com>
@ 2019-03-25 18:05 ` Frank Alberto Rodriguez Solana <franknigth@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Frank Alberto Rodriguez Solana @ 2019-03-25 18:05 UTC (permalink / raw)
To: Mark Steben <mark.steben@drivedominion.com>; +Cc: pgsql-admin
Hi, you could use repmgr tool, developed by 2ndQuadrant. This work with
POtgreSQL 9.3 or higher.
In the configuration file you could use "event_notification_command" to
execute an script to monitoring and sending notifications when you needs.
This is the site:
https://repmgr.org/
And this is the documentation:
https://repmgr.org/docs/4.1/bdr-monitoring-failover.html
Greetings
El lun., 25 mar. 2019 a las 11:51, Mark Steben (<
mark.steben@drivedominion.com>) escribió:
> Good afternoon,
>
> We are running postgres 9.4. We are outsourcing our databases to another
> datacenter owned by a third party company. The methodology we are using to
> move the database is hot standby log shipping replication. We will run
> streaming replication for several days then, when the time comes to pull
> the trigger and make the move, I will complete recovery, bring in a
> 'production' version of postgresql.conf, restart the database, and hand it
> over to the apps.
>
> We want to make sure that during the several days of replication we are
> immediately notified if a problem arises - a missing log, a production
> outage, etc. Is there a monitor or an 'in-the-box' procedure, or perhaps a
> condition to check during hot standby or streaming replication to ensure
> things are still running as planned, or if there is a problem?
>
> Thanks for any insights.
>
> --
> *Mark Steben*
> Database Administrator
> @utoRevenue <http://www.autorevenue.com/; | Autobase
> <http://www.autobase.net/;
> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions
> www.twitter.com/DominionDealer
> www.drivedominion.com <http://www.autorevenue.com/;
>
> <http://autobasedigital.net/marketing/DD12_sig.jpg;
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Monitoring hot standby replication for failure
2019-03-25 17:50 Monitoring hot standby replication for failure Mark Steben <mark.steben@drivedominion.com>
@ 2019-03-25 20:24 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2 siblings, 0 replies; 5+ messages in thread
From: Jehan-Guillaume (ioguix) de Rorthais @ 2019-03-25 20:24 UTC (permalink / raw)
To: Mark Steben <mark.steben@drivedominion.com>; +Cc: pgsql-admin
On Mon, 25 Mar 2019 13:50:42 -0400
Mark Steben <mark.steben@drivedominion.com> wrote:
> Good afternoon,
>
> We are running postgres 9.4. We are outsourcing our databases to another
> datacenter owned by a third party company. The methodology we are using to
> move the database is hot standby log shipping replication. We will run
> streaming replication for several days then, when the time comes to pull
> the trigger and make the move, I will complete recovery, bring in a
> 'production' version of postgresql.conf, restart the database, and hand it
> over to the apps.
>
> We want to make sure that during the several days of replication we are
> immediately notified if a problem arises - a missing log, a production
> outage, etc. Is there a monitor or an 'in-the-box' procedure, or perhaps a
> condition to check during hot standby or streaming replication to ensure
> things are still running as planned, or if there is a problem?
You could simply use check_pgactivity to check the streaming replication delta.
Moreover, you'll be able to monitor the archiving process if you set it up.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Monitoring hot standby replication for failure
2019-03-25 17:50 Monitoring hot standby replication for failure Mark Steben <mark.steben@drivedominion.com>
@ 2019-03-26 02:14 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-03-26 07:33 ` Re: Monitoring hot standby replication for failure CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
2 siblings, 1 reply; 5+ messages in thread
From: Shreeyansh Dba @ 2019-03-26 02:14 UTC (permalink / raw)
To: Mark Steben <mark.steben@drivedominion.com>; +Cc: pgsql-admin
Hi Mark,
You can used pglookout for PostgreSQL replication monitoring and failover
daemon. go with below link hopes this will help you.
https://github.com/aiven/pglookout
Thanks & Regards,
*Shreeyansh DBA Team*
www.shreeyansh.com
On Mon, Mar 25, 2019 at 11:21 PM Mark Steben <mark.steben@drivedominion.com>
wrote:
> Good afternoon,
>
> We are running postgres 9.4. We are outsourcing our databases to another
> datacenter owned by a third party company. The methodology we are using to
> move the database is hot standby log shipping replication. We will run
> streaming replication for several days then, when the time comes to pull
> the trigger and make the move, I will complete recovery, bring in a
> 'production' version of postgresql.conf, restart the database, and hand it
> over to the apps.
>
> We want to make sure that during the several days of replication we are
> immediately notified if a problem arises - a missing log, a production
> outage, etc. Is there a monitor or an 'in-the-box' procedure, or perhaps a
> condition to check during hot standby or streaming replication to ensure
> things are still running as planned, or if there is a problem?
>
> Thanks for any insights.
>
> --
> *Mark Steben*
> Database Administrator
> @utoRevenue <http://www.autorevenue.com/; | Autobase
> <http://www.autobase.net/;
> CRM division of Dominion Dealer Solutions
> 95D Ashley Ave.
> West Springfield, MA 01089
> t: 413.327-3045
> f: 413.383-9567
>
> www.fb.com/DominionDealerSolutions
> www.twitter.com/DominionDealer
> www.drivedominion.com <http://www.autorevenue.com/;
>
> <http://autobasedigital.net/marketing/DD12_sig.jpg;
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Monitoring hot standby replication for failure
2019-03-25 17:50 Monitoring hot standby replication for failure Mark Steben <mark.steben@drivedominion.com>
2019-03-26 02:14 ` Re: Monitoring hot standby replication for failure Shreeyansh Dba <shreeyansh2014@gmail.com>
@ 2019-03-26 07:33 ` CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
0 siblings, 0 replies; 5+ messages in thread
From: CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG @ 2019-03-26 07:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hello,<br>
<br>
I use a shell script which ask primary wal value and compare to
slave wal value and flag it on web management page.<br>
<br>
<br>
<br>
<br>
<br>
-------------------------start of
script---------------------------------<br>
<br>
BASE=( base_to_test )<br>
journal=/home/sigdreal/log/journal_suivi_replication.log<br>
ST=${journal:0:${#journal}-3}"st"<br>
tp=0<br>
usr="youruser"<br>
<br>
pg_source="psql -d ${BASE[i]} -U $usr -h localhost -p 5432"<br>
pg_destination="psql -d ${BASE[i]} -U $usr -h remotehost -p 5432"<br>
if [ -e $journal ]; then mv $journal /home/user/log/sauv_n1/<br>
fi<br>
<br>
if [ -e $ST ]; then mv $ST /home/user/log/sauv_n1/<br>
fi<br>
date>>$journal<br>
date>>$ST<br>
valeur_wal_source=`$pg_source -t -c "select
pg_current_xlog_location();"`<br>
valeur_wal_destination=`$pg_destination -t -c "select
pg_last_xlog_receive_location();"`<br>
<br>
echo "test du wal emis dans le maitre à Lyon :
$valeur_wal_source">>$journal<br>
echo "test du wal recu dans l'esclave à Clermont-ferrand :
$valeur_wal_destination">>$journal<br>
echo " ">>$journal<br>
<br>
echo "taille de postgres a lyon">>$journal<br>
df -h /var/lib/postgresql/9.6/main/>>$journal<br>
<br>
echo " ">>$journal<br>
<br>
<br>
if [[ $valeur_wal_source == *$valeur_wal_destination ]] ; then echo
"la replication fonctionne">>$journal<br>
else tp=$((tp+`echo $?`))+1<br>
echo "la replication est interrompue"<br>
fi<br>
<br>
tp=$((tp+`echo $?`))<br>
<br>
echo $tp>>$ST<br>
date>>$ST<br>
date>>$journal<br>
<br>
--------------------------end of script-----------------------------<br>
<br>
regards<br>
<br>
<br>
<div class="moz-signature">
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils
<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann.convers@developpement-durable.gouv.fr">yann.convers@developpement-durable.gouv.fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig.dreal-ara@developpement-durable.gouv.fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://post...;
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-signature">
<meta http-equiv="CONTENT-TYPE" content="text/html; charset=utf-8">
<title></title>
<meta name="GENERATOR" content="OpenOffice.org 2.3 (Win32)">
<meta name="AUTHOR" content="dreal-ra">
<meta name="CREATED" content="20110608;16534339">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGED" content="20120220;8494106">
<meta name="CHANGEDBY" content="Yann CONVERS">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<meta name="CHANGEDBY" content="dreal-aura">
<style type="text/css">
<!--
@page { size: 21cm 29.7cm; margin: 2cm }
P { margin-bottom: 0.21cm }
-->
</style>
<pre><b>Yann Convers</b>
Gestionnaire de l'infrastructure, des référentiels et des outils<p>
<font color="#000000"><b>DREAL Auvergne-Rhône-Alpes</b></font></p>
<pre>Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : <a href="mailto:yann.convers@developpement-durable.gouv.fr">yann.convers@developpement-durable.gouv.fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig.dreal-ara@developpement-durable.gouv.fr">boite unité</a>
<p>Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : <a href="http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new">http://post...;
<b>Adresse postale :</b> DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG
69453 LYON CEDEX 06
<b>Adresse physique :</b> 5
place Jules Ferry (immeuble Lugdunum - métro Brotteaux) -
69006 Lyon
<b>Standard :</b> 04 26 28 60 00
<b>Liens :</b><a href="http://intra.dreal-rhone-alpes.i2/">Site
Intranet</a> <a href="http://www.auvergne-rhone-alpes.developpement-durable.gouv.fr/">Site
Internet</a>
<p style="margin-bottom: 0cm">
</p>
</pre>
</div>
<div class="moz-cite-prefix">Le 26/03/2019 à 03:14, > Shreeyansh
Dba (par Internet, dépôt
<a class="moz-txt-link-abbreviated" href="mailto:pgsql-admin-owner+m63745-136297@lists.postgresql.org">pgsql-admin-owner+m63745-136297@lists.postgresql.org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:CAGDYbUO6tKN3UwG3XKyrb4-nQycaS=CdRPkPxnxoDpG1_Z2Fyg@mail.gmail.com"
type="cite">
<div dir="ltr">
<div dir="ltr">
<div class="gmail_default">
<div class="gmail_default">Hi Mark,</div>
<div class="gmail_default"><br>
</div>
<div class="gmail_default">You can used pglookout for
PostgreSQL replication monitoring and failover daemon. go
with below link hopes this will help you.<br>
<a moz-do-not-send="true"
href="https://github.com/aiven/pglookout">https://github.com/aiven/pglookout</a><br;
</div>
</div>
<div>
<div dir="ltr" class="gmail_signature">
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div>
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div dir="ltr">
<div>
<div><br>
Thanks & Regards,<br>
<b>Shreeyansh DBA Team</b><br>
<font size="1"><a
moz-do-not-send="true"
href="http://www.shreeyansh.com";
target="_blank">www.shreeyansh.com</a></font></div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
</div>
</div>
<br>
<div class="gmail_quote">
<div dir="ltr" class="gmail_attr">On Mon, Mar 25, 2019 at 11:21
PM Mark Steben <<a moz-do-not-send="true"
href="mailto:mark.steben@drivedominion.com">mark.steben@drivedominion.com</a>>
wrote:<br>
</div>
<blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div dir="ltr">Good afternoon,
<div><br>
</div>
<div>We are running postgres 9.4. We are outsourcing our
databases to another datacenter owned by a third party
company. The methodology we are using to move the
database is hot standby log shipping replication. We will
run streaming replication for several days then, when the
time comes to pull the trigger and make the move, I will
complete recovery, bring in a 'production' version of
postgresql.conf, restart the database, and hand it over to
the apps. </div>
<div><br>
</div>
<div>We want to make sure that during the several days of
replication we are immediately notified if a problem
arises - a missing log, a production outage, etc. Is
there a monitor or an 'in-the-box' procedure, or perhaps a
condition to check during hot standby or streaming
replication to ensure things are still running as planned,
or if there is a problem? </div>
<div><br>
</div>
<div>Thanks for any insights. <br clear="all">
<div><br>
</div>
-- <br>
<div dir="ltr"
class="gmail-m_-7516531854317242472gmail_signature">
<div><span><font color="#888888"><span
style="font-family:Calibri;font-size:11pt"><span
style="color:rgb(51,51,51)"><b>Mark Steben</b></span>
</span><br>
Database Administrator<span
style="font-family:"Century
Gothic";font-size:8pt"><span
style="color:rgb(51,51,51)"></span><br>
<a moz-do-not-send="true"
style="color:rgb(79,129,189);font-size:8pt;font-weight:bold;text-decoration:none"
href="http://www.autorevenue.com/";
target="_blank">@utoRevenue</a> <span
style="color:rgb(79,129,189);font-size:10pt">|</span> <a
moz-do-not-send="true"
style="color:rgb(79,129,189);font-size:8pt;font-weight:bold;text-decoration:none"
href="http://www.autobase.net/";
target="_blank">Autobase</a> <span
style="color:rgb(79,129,189);font-size:10pt"></span>
<br>
<span
style="color:rgb(79,129,189);font-size:8pt;font-weight:bold;text-decoration:none">CRM
division of Dominion Dealer Solutions</span> <br>
<span style="color:gray;font-size:8pt">95D
Ashley Ave.<br>
West Springfield, MA 01089</span> <br>
<span style="color:gray;font-size:8pt">t: <a
moz-do-not-send="true" value="+14132434800">413.327-3045</a></span>
<br>
<span style="color:gray;font-size:8pt">f: <a
moz-do-not-send="true" value="+14132434800">413.383-9567</a></span>
</span></font></span></div>
<p><span><font color="#888888"><span
style="font-family:"Century
Gothic";font-size:8pt"><a
moz-do-not-send="true"
href="http://www.fb.com/DominionDealerSolutions";
target="_blank">www.fb.com/DominionDealerSolutions</a><br>
<a moz-do-not-send="true"
href="http://www.twitter.com/DominionDealer";
target="_blank">www.twitter.com/DominionDealer</a><br>
<span style="color:gray;font-size:8pt"> <a
moz-do-not-send="true"
href="http://www.autorevenue.com/";
target="_blank">www.drivedominion.com</a><br>
<br>
<br>
<br>
</span></span></font></span><br>
<span><font color="#888888"><span
style="font-family:"Century
Gothic";font-size:8pt"><span
style="color:gray;font-size:8pt"><br>
</span></span></font></span></p>
</div>
</div>
</div>
</blockquote>
</div>
</blockquote>
<br>
</body>
</html>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-03-26 07:33 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-03-25 17:50 Monitoring hot standby replication for failure Mark Steben <mark.steben@drivedominion.com>
2019-03-25 18:05 ` Frank Alberto Rodriguez Solana <franknigth@gmail.com>
2019-03-25 20:24 ` Jehan-Guillaume (ioguix) de Rorthais <ioguix@free.fr>
2019-03-26 02:14 ` Shreeyansh Dba <shreeyansh2014@gmail.com>
2019-03-26 07:33 ` CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann.convers@developpement-durable.gouv.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox