| From: | CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG <yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr> |
|---|---|
| To: | pgsql-admin(at)lists(dot)postgresql(dot)org |
| Subject: | Re: Monitoring hot standby replication for failure |
| Date: | 2019-03-26 07:33:22 |
| Message-ID: | 7a2e4633-a72f-8256-018a-53d4958f18ae@developpement-durable.gouv.fr |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-admin |
<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(dot)convers(at)developpement-durable(dot)gouv(dot)fr">yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig(dot)dreal-ara(at)developpement-durable(dot)gouv(dot)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://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new</a></p>
<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(dot)convers(at)developpement-durable(dot)gouv(dot)fr">yann(dot)convers(at)developpement-durable(dot)gouv(dot)fr</a></pre>adresse de la boite d’unité : <a href="mailto:sig(dot)dreal-ara(at)developpement-durable(dot)gouv(dot)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://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new</a></p>
<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(at)lists(dot)postgresql(dot)org">pgsql-admin-owner+m63745-136297(at)lists(dot)postgresql(dot)org</a>) a écrit :<br>
</div>
<blockquote
cite="mid:CAGDYbUO6tKN3UwG3XKyrb4-nQycaS=CdRPkPxnxoDpG1_Z2Fyg(at)mail(dot)gmail(dot)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(dot)steben(at)drivedominion(dot)com">mark(dot)steben(at)drivedominion(dot)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>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 13.0 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Mahmoud Moharam | 2019-03-27 06:36:56 | Postgres Stream Replication Failover Questions |
| Previous Message | Keith | 2019-03-26 06:43:54 | Re: Upgrading from Postgresql 9.3.8 to 9.6.10 |