summary and operation
In the end to have a functional HA Postgresql solution, you need:
Postgresql synchrone replication and pg_receivexlog enabled
Replication
postgres@master:~$ pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.5 turtle 5432 online postgres /data/postgresql/9.5/turtle /var/log/postgresql/postgresql-9.5-turtle.log
root@standby:/# pg_lsclusters
Ver Cluster Port Status Owner Data directory Log file
9.5 turtle 5432 online,recovery postgres /data/postgresql/9.5/turtle /var/log/postgresql/postgresql-9.5-turtle.log
Pg_receivexlog
postgres@witness# ps fax
PID TTY STAT TIME COMMAND
411 ? S 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /data/postgresql/9.5/turtle_witness
413 ? Ss 0:00 \_ postgres: checkpointer process
414 ? Ss 0:00 \_ postgres: writer process
415 ? Ss 0:00 \_ postgres: wal writer process
416 ? Ss 0:00 \_ postgres: autovacuum launcher process
417 ? Ss 0:00 \_ postgres: stats collector process
867 ? S 0:00 /usr/lib/postgresql/9.5/bin/pg_receivexlog -d host=repmgr_master port=5432 user=postgres application_name=turtle -D /data/wal_pgrxlog/turtle
visible connections on Master server
root@master# ps fax
PID TTY STAT TIME COMMAND
38 ? Ss 0:00 /bin/bash
568 ? R+ 0:00 \_ ps fax
1 ? Ss+ 0:00 /bin/bash
56 ? S 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /data/postgresql/9.5/turtle -c config_file=/etc/postgresql/9.5/turtle/postgresql.conf
95 ? Ss 0:00 \_ postgres: checkpointer process
96 ? Ss 0:00 \_ postgres: writer process
97 ? Ss 0:00 \_ postgres: wal writer process
98 ? Ss 0:00 \_ postgres: autovacuum launcher process
99 ? Ss 0:00 \_ postgres: stats collector process
565 ? Ss 0:00 \_ postgres: wal sender process postgres 172.31.standby.ip(58288) streaming 0/5000898
567 ? Ss 0:00 \_ postgres: wal sender process postgres 172.31.witness.ip(34408) streaming 0/5000898
Check Synchrone replication
postgres@master# psql -c "select * from pg_settings where name like '%synchronous_standby_names%';"
name | synchronous_standby_names
setting | *
unit |
category | Replication / Master Server
short_desc | List of names of potential synchronous standbys.
extra_desc |
context | sighup
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val |
reset_val | *
sourcefile | /app/postgresql/9.5/turtle/postgresql.auto.conf
sourceline | 3
Repmgr replication enabled
Replication repmgr
postgres@master# psql -d repmgr_db -c "select * from repmgr_turtle.repl_nodes ;"
id | type | upstream_node_id | cluster | name | conninfo | slot_name | priority | active
----+---------+------------------+---------+---------+----------------------------------------------------------------+-----------+----------+--------
1 | master | | turtle | master | host=repmgr_master user=repmgr_usr dbname=repmgr_db port=5432 | | 100 | t
2 | standby | 1 | turtle | standby | host=repmgr_standby user=repmgr_usr dbname=repmgr_db port=5432 | | 100 | t
3 | witness | | turtle | witness | host=repmgr_witness user=repmgr_usr dbname=repmgr_db port=5432 | | 100 | t
Repmgrd started
postgres@standby# ps fax
PID TTY STAT TIME COMMAND
482 ? S 0:01 /usr/lib/postgresql/9.5/bin/repmgrd -p /var/run/repmgrd/repmgrd.sh.pid -f /etc/repmgr.conf --daemonize
In the end
root@master:/# ps fax
PID TTY STAT TIME COMMAND
26 ? S 0:00 /usr/lib/postgresql/9.5/bin/postgres -D /data/postgresql/9.5/turtle -c config_file=/etc/postgresql/9.5/turtle/postgresql.conf
28 ? Ss 0:00 \_ postgres: checkpointer process
29 ? Ss 0:00 \_ postgres: writer process
30 ? Ss 0:00 \_ postgres: wal writer process
31 ? Ss 0:00 \_ postgres: autovacuum launcher process
32 ? Ss 0:00 \_ postgres: stats collector process
43 ? Ss 0:00 \_ postgres: wal sender process postgres 172.31.standby.ip(51104) streaming 0/9001DC8
209 ? Ss 0:00 \_ postgres: wal sender process postgres 172.31.witness.ip(41999) streaming 0/9001DC8
226 ? Ss 0:00 \_ postgres: repmgr_usr repmgr_db 172.31.standby.ip(51166) idle