0

Now using these servers:

  • Node1: pgbouncer1
  • Node2: pgbouncer2
  • Node3: repmgr primary
  • Node4: repmgr standby
  • Node5: repgmr standby
  • Node6: repmgr standby
  • Node7: barman primary
  • Node8: barman passive

pgbouncer for connection pool, repmgr for repliation and automatic failover, barman for backup and recovery.

Now want to add shell script to all the repmgr servers to automatic change a new primary if current(Node3) down. But maybe it's random select a new one(One of Node4, Node5, Node6).

If I provide promote script in all the repmgr servers, update self host IP and copy it to pgbouncer servers(Node1, Node2) and barman server(Node7). Which script will be ran ?

The repmgr.conf like:

# ...

service_promote_command = '/usr/local/bin/promote.sh'
promote_check_timeout = 15

promote_command='/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file'
follow_command='/usr/bin/repmgr standby follow -f /etc/repmgr.conf --log-to-file --upstream-node-id=%n'

promote.sh like:

#!/usr/bin/env bash
set -e
set -u

PGBOUNCER_DATABASE_INI_NEW="/tmp/pgbouncer.database.ini"
PGBOUNCER_HOSTS="Node1_IP Node2_IP"
DATABASES="db1 db2"

# Pause pgbouncer
for h in ${PGBOUNCER_HOSTS}
do
  for d in ${DATABASES}
  do
    ┆ psql -U postgres -h ${h} -p 5432 pgbouncer -tc "pause ${d}"
  done
done

# Promote server
/usr/bin/repmgr standby promote -f /etc/repmgr.conf --log-to-file # Here possible?

# Generate new config file for pgbouncer
echo -e "[databases]\n" > ${PGBOUNCER_DATABASE_INI_NEW}
for d in ${DATABASES}
do
  echo -e "${d}= host=$(hostname -f)\n" >> ${PGBOUNCER_DATABASE_INI_NEW}
done

# Copy new config file, reload and resume pgbouncer
for h in ${PGBOUNCER_HOSTS}
do
  for d in ${DATABASES}
  do
    rsync -a ${PGBOUNCER_DATABASE_INI_NEW} ${h}:/etc/pgbouncer/pgbouncer.database.ini
    psql -U postgres -h ${h} -p 5432 pgbouncer -tc "reload"
    psql -U postgres -h ${h} -p 5432 pgbouncer -tc "resume ${d}"
  done
done

rm ${PGBOUNCER_DATABASE_INI_NEW}

# Copy new config file, reload and resume barman
# TODO

If deploy it as all the repmgr servers, can they run together, or select only one promoted primary server if current primary down?

  • 1
    Unrelated but you should use standby_promote event whether repmgr standby promote succeeds or not. Then you can change pgbouncer settings – Sahap Asci Mar 9 at 10:45
  • @SahapAsci Even can realize the automatic failover by this way, it will take several seconds to change to another primary, right? At that time, user can't access the database by pending? – rawmain Mar 9 at 13:18
  • sure, you should read Event Notifications chapter. – Sahap Asci Mar 9 at 18:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.