Miq DB notes

miq-all-about-db.org

Environment having 2 standby(1 on same region, other in cross region)

vmdb
rails db

select * from repl_nodes;
  id  |  type   | upstream_node_id |        cluster        |     name     |                      conninfo                      |    slot_name     | priority | active
------+---------+------------------+-----------------------+--------------+----------------------------------------------------+------------------+----------+--------
 3003 | standby |             3002 | miq_region_30_cluster | 10.222.73.24 | host=10.222.71.24 user=root dbname=vmdb_production | repmgr_slot_3003 |      100 | t
 3002 | master  |                  | miq_region_30_cluster | 10.222.73.31 | host=10.222.71.31 user=root dbname=vmdb_production | repmgr_slot_3002 |      100 | t
 3001 | standby |             3002 | miq_region_30_cluster | 10.222.73.23 | host=10.222.71.23 user=root dbname=vmdb_production | repmgr_slot_3001 |      100 | t

Relication slots of both standby nodes are in active states

vmdb
rails db

select * from pg_replication_slots;
 slot_name        | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn
------------------+--------+-----------+--------+----------+--------+------------+------+--------------+--------------
 repmgr_slot_3001 |        | physical  |        |          | t      |      25166 |      |              | 305/2EE446A0
 repmgr_slot_3003 |        | physical  |        |          | t      |      25408 |      |              | 301/B8740000
(2 rows)

Example showing environment having 1 standby node in failed state

select * from pg_replication_slots;
slot_name      | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmi n | restart_lsn
---------------+--------+-----------+--------+----------+--------+------------+------+------------ --+-------------
 repmgr_slot_2 |        | physical  |        |          | f      |            |      |               | 1/3AB7D7A8
(1 row)

If there is no active replication slot, the WAL[1][2] segments will be retained consuming disk space on primary DB node[4]

Current state of standby nodes(Note that one standby node is still syncing..)

su - postgres
psql -c "select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;"

client_addr  |   state   | sent_location | write_location | flush_location | replay_location
--------------+-----------+---------------+----------------+----------------+-----------------
 10.222.71.24 | streaming | 305/308429F0  | 305/308429F0   | 305/3083FA98   | 305/3083FA98
 10.222.71.23 | catchup   | 304/5B20000   | 304/5700000    | 304/5660000    | 304/4DBE028
(2 rows)

(Synced state)

psql -c "select client_addr, state, sent_location, write_location, flush_location, replay_location from pg_stat_replication;"
 client_addr  |   state   | sent_location | write_location | flush_location | replay_location
--------------+-----------+---------------+----------------+----------------+-----------------
 10.222.71.24 | streaming | 305/34FAC0B0  | 305/34FAC0B0   | 305/34FAC0B0   | 305/34FABF28
 10.222.71.23 | streaming | 305/34FAC0B0  | 305/34FAC0B0   | 305/34FAC0B0   | 305/34FABF28
(2 rows)

Cluster status. All about `repmgr`[3]

repmgr cluster show
[2017-11-08 15:46:09] [NOTICE] Redirecting logging output to '/var/log/repmgr/repmgrd.log'
Role      | Name         | Upstream     | Connection String
----------+--------------|--------------|---------------------------------------------------
  standby | 10.222.71.24 | 10.222.71.31 | host=10.222.71.24 user=root dbname=vmdb_production
* master  | 10.222.71.31 |              | host=10.222.71.31 user=root dbname=vmdb_production
  standby | 10.222.71.23 | 10.222.71.31 | host=10.222.71.23 user=root dbname=vmdb_production

Notes

1. Location: /var/opt/rh/rh-postgresql95/lib/pgsql/data/pg_xlog
2. How to drop inactive replication slot?

select pg_drop_replication_slot('repmgr_slot_2');

Ref

1. https://en.wikibooks.org/wiki/PostgreSQL/WAL
2. https://www.postgresql.org/docs/9.1/static/wal-intro.html
3. https://repmgr.org/docs/4.0/index.html
4. https://www.postgresql.org/docs/9.5/static/warm-standby.html#STREAMING-REPLICATION-SLOTS (Section: 25.2.6. Replication Slots, 2nd paragraph)