Repmgr : It automatically promotes to new master but other standby stopped

I’m trying to setup postgre with repmgr and here is the rsult

enter image description here

The scenario is,

  1. i tried to spinup 1 primary and 2 standby.
  2. then i stop the primary so the postgres-2 got promoted.
  3. Unfortunately the postgres-3 got disconnected due to some reason but here is the error log enter image description here

It looks like it was able to connect but the postgres restared and didn’t come back. 4. I spin up another standby but the master it was pointing is the old one which is postgres-1 That might be the reason why it is saying !running and still primary even if the actual primary is postgres-2.

My question is how can i make other standby not disconnected every time i promoted (automatically due to failure etc.) a new primary?

here is my repmgr.conf

NET_IF=`netstat -rn | awk '/^0.0.0.0/ {thif=substr($  0,74,10); print thif;} /^default.*UG/ {thif=substr($  0,65,10); print thif;}'` NET_IP=`ifconfig $  {NET_IF} | grep -Eo 'inet (addr:)?([0-9]*\.){3}[0-9]*' | grep -Eo '([0-9]*\.){3}[0-9]*' | grep -v '127.0.0.1'`   HOSTNAME='postgres-'$  {my_node}  cat<<EOF > /etc/repmgr.conf     node_id=$  {my_node}     node_name=$  HOSTNAME     conninfo='host=$  {NET_IP} user=repmgr password=repmgr dbname=repmgr connect_timeout=2'     data_directory='$  {PGDATA}'      log_level=INFO     log_facility=STDERR     log_status_interval=300          pg_bindir='/usr/lib/postgresql/10/bin'     use_replication_slots=1          failover=automatic     promote_command='repmgr standby promote'     follow_command='repmgr standby follow -W' EOF 

Also, I’m running using docker extending the official postgres docker image

FROM postgres:10  RUN echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main 10" \           >> /etc/apt/sources.list.d/pgdg.list  # RUN ln -s /home/postgres/repmgr.conf /etc/repmgr.conf  RUN apt-get update && apt-get install wget -y RUN apt-get install net-tools -y RUN apt-get update; apt-get install -y git make postgresql-server-dev-10 libpq-dev postgresql-10-repmgr repmgr-common  #RUN wget -c https://repmgr.org/download/repmgr-5.1.tar.gz -O - | tar -xz  RUN touch /etc/repmgr.conf; \     chown postgres:postgres /etc/repmgr.conf  ENV PRIMARY_NAME=localhost ENV REPMGR_USER=repmgr ENV REPMGR_DB=repmgr ENV REPMGR_PASSWORD=repmgr  COPY postgresql.replication.conf /tmp/postgresql.replication.conf  COPY scripts/*.sh /docker-entrypoint-initdb.d/ 

Lastly, here is how i check if should be register as primary or standby

PGHOST=$  {PRIMARY_NAME}  installed=$  (psql -qAt -h $  {PGHOST} repmgr -c "SELECT 1 FROM pg_tables WHERE tablename='nodes'")  if [ "$  {installed}" != "1" ]; then     echo "Registering as PRIMARY SERVER"     repmgr primary register else     my_node=$  (grep node_id /etc/repmgr.conf | cut -d= -f 2)     is_reg=$  (psql -qAt -h $  {PGHOST} repmgr -c "SELECT 1 FROM repmgr.nodes WHERE node_id=$  {my_node}")      if [ "$  {is_reg}" != "1" ] && [ $  {my_node} -gt 1 ]; then         echo "Registering as STANDBY SERVER"          pg_ctl -D $  {PGDATA} stop -m fast         rm -Rf $  {PGDATA}/*         repmgr -h $  {PRIMARY_NAME} -d repmgr standby clone --fast-checkpoint         pg_ctl -D $  {PGDATA} start &         sleep 1         repmgr -h $  {PRIMARY_NAME} -d repmgr standby register         fi fi 

here is my update for the postgres.conf

 sed -i "s/#*\(shared_preload_libraries\).*/='repmgr'/;" $  {PGDATA}/postgresql.conf sed -i "s/#port = 5432/port = 5432/g" $  {PGDATA}/postgresql.conf sed -i "s/#max_wal_senders/max_wal_senders/g"  $  {PGDATA}/postgresql.conf sed -i "s/#wal_level/wal_level/g"  $  {PGDATA}/postgresql.conf sed -i "s/#max_replication_slots/max_replication_slots/g"  $  {PGDATA}/postgresql.conf sed -i "s/#hot_standby/hot_standby/g"  $  {PGDATA}/postgresql.conf  sed -i "s/#archive_mode = off/archive_mode = on/g"  $  {PGDATA}/postgresql.conf  echo "archive_command = '/bin/true'" >>  $  {PGDATA}/postgresql.conf 

I’m using postgres:10 and repmgr-5.0

hope someone could help me on this. Thanks,

install postgresql without creating instance (for use with repmgr)

I’m trying to get REPMGR setup, and I’m following the steps at https://repmgr.org/docs/current/quickstart-standby-preparation.html to get the standby setup.

I noticed it warns On the standby, do not create a PostgreSQL instance. However I believe this happens automatically with how I installed Postgres

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" |sudo tee  /etc/apt/sources.list.d/pgdg.list sudo apt update sudo apt -y install postgresql-12 postgresql-client-12 

because when I try to clone the primary onto the standby as mentioned in https://repmgr.org/docs/current/quickstart-standby-clone.html
$ repmgr -h node1 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run

I get

postgres@empty2:~$   repmgr -h 192.168.1.102 -U repmgr -d repmgr -f /etc/repmgr.conf standby clone --dry-run NOTICE: destination directory "/var/lib/postgresql/12/main" provided ERROR: specified data directory "/var/lib/postgresql/12/main" appears to contain a running PostgreSQL instance HINT: ensure the target data directory does not contain a running PostgreSQL instance 

Now I’m just ASSUMING that this is because the database instance was created when I installed postgres on the standby. and I’m also ASSUMING that I can just delete everything in the data directory on the standby, and everything will work ok…

But (assuming my assumptions are correct….) what is the correct way to install postgres12 without creating an instance and the corresponding data files?

pg_dump does not return schema for repmgr objects

While trying to browse repmgr.monitoring_history, I noticed it has many tens of millions of records. Wanting to get a peak at the indexes that (may or may not) already exist on the table, I try a quickie pg_dump

# pg_dump -U postgres -d repmgr --table=repmgr.monitoring_history --schema-only 

…which frustratingly returns only the boilerplate header/footer info…

-- -- PostgreSQL database dump --  -- Dumped from database version 10.8 -- Dumped by pg_dump version 10.8  SET statement_timeout = 0; SET lock_timeout = 0; SET idle_in_transaction_session_timeout = 0; SET client_encoding = 'UTF8'; SET standard_conforming_strings = on; SELECT pg_catalog.set_config('search_path', '', false); SET check_function_bodies = false; SET xmloption = content; SET client_min_messages = warning; SET row_security = off;  -- -- PostgreSQL database dump complete -- 

…well darnit! Did I typo something? 🤔

# pg_dump -U postgres -d repmgr --table=foo.bar --schema-only pg_dump: no matching tables were found 

…no… I am connected to the host I though I was, right?…

# psql -U postgres -d repmgr -c 'select count(*) from repmgr.monitoring_history;'   count    ----------  43597932 (1 row) 

… so what gives? Why can’t I dump the schema definition for this table?


Possibly also worth noting that pg_dump -s -d repmgr returns only the CREATE EXTENSION… & CREATE SCHEMA… commands and none of the tables. The behaviour is consistent across a few different repmgr installations. Perhaps I’m having the same issue as this guy?