Monitoring hot standby replication for failure

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

Monitoring hot standby replication for failure

Mark Steben-3
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 | Autobase 
  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





Reply | Threaded
Open this post in threaded view
|

Re: Monitoring hot standby replication for failure

Frank Alberto Rodriguez Solana

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 (<[hidden email]>) 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 | Autobase 
  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





Reply | Threaded
Open this post in threaded view
|

Re: Monitoring hot standby replication for failure

Jehan-Guillaume (ioguix) de Rorthais
In reply to this post by Mark Steben-3
On Mon, 25 Mar 2019 13:50:42 -0400
Mark Steben <[hidden email]> 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.

Reply | Threaded
Open this post in threaded view
|

Re: Monitoring hot standby replication for failure

Shreeyansh dba
In reply to this post by Mark Steben-3
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 <[hidden email]> 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 | Autobase 
  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





Reply | Threaded
Open this post in threaded view
|

Re: Monitoring hot standby replication for failure

"CONVERS Yann - DREAL Auvergne-Rhône-Alpes/CIDDAE/SIG"
Hello,

I use a shell script which ask primary wal value and compare to slave wal  value and flag it on web management page.





-------------------------start of script---------------------------------

BASE=( base_to_test )
journal=/home/sigdreal/log/journal_suivi_replication.log
ST=${journal:0:${#journal}-3}"st"
tp=0
usr="youruser"

pg_source="psql -d ${BASE[i]} -U $usr -h localhost -p 5432"
pg_destination="psql -d ${BASE[i]} -U $usr -h remotehost -p 5432"
if [ -e $journal ]; then mv $journal /home/user/log/sauv_n1/
fi

if [ -e $ST ]; then mv $ST /home/user/log/sauv_n1/
fi
date>>$journal
date>>$ST
valeur_wal_source=`$pg_source -t -c "select pg_current_xlog_location();"`
valeur_wal_destination=`$pg_destination -t -c "select pg_last_xlog_receive_location();"`

echo "test du wal emis dans le maitre à Lyon  : $valeur_wal_source">>$journal
echo "test du wal recu dans l'esclave à Clermont-ferrand  : $valeur_wal_destination">>$journal
echo " ">>$journal

echo "taille de  postgres a lyon">>$journal
df -h /var/lib/postgresql/9.6/main/>>$journal

echo " ">>$journal


if [[ $valeur_wal_source == *$valeur_wal_destination ]] ; then echo "la replication fonctionne">>$journal
else  tp=$((tp+`echo $?`))+1
      echo "la replication est interrompue"
fi

tp=$((tp+`echo $?`))

echo $tp>>$ST
date>>$ST
date>>$journal

--------------------------end of script-----------------------------

regards


Yann Convers
Gestionnaire de l'infrastructure, des référentiels et des outils




DREAL Auvergne-Rhône-Alpes

Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : [hidden email]
adresse de la boite d’unité : [hidden email]

Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new

Adresse postale : DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG 69453 LYON CEDEX 06 Adresse physique : 5 place Jules Ferry (immeuble Lugdunum - métro Brotteaux) - 69006 Lyon Standard : 04 26 28 60 00 Liens :Site Intranet  Site Internet

Yann Convers
Gestionnaire de l'infrastructure, des référentiels et des outils

DREAL Auvergne-Rhône-Alpes

Service Connaissance, Information, Développement Durable, Autorité Environnementale - Pole Système d Information Géographique
Tél : 04 26 28 67 89
courriel : [hidden email]
adresse de la boite d’unité : [hidden email]

Pour toute commande interne de prestations, merci d utilisez le formulaire suivant : http://postgis-aura.projets.appli.i2/projects/demandes-de-prestations/issues/new

Adresse postale : DREAL Auvergne-Rhône-Alpes, CIDDAE - SIG 69453 LYON CEDEX 06 Adresse physique : 5 place Jules Ferry (immeuble Lugdunum - métro Brotteaux) - 69006 Lyon Standard : 04 26 28 60 00 Liens :Site Intranet  Site Internet

Le 26/03/2019 à 03:14, > Shreeyansh Dba (par Internet, dépôt [hidden email]) a écrit :
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 <[hidden email]> 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 | Autobase 
  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