During the weekend (on friday) one of my slaves suddenly stop syncing with the primary. I have 1 master + 2 standbys. When I arrived this morning to work I saw that one of the slaved has a big lag :
postgres=# SELECT CASE WHEN pg_last_xlog_receive_location() = pg_last_xlog_replay_location() THEN 0 ELSE EXTRACT (EPOCH FROM NOW() - pg_last_xact_replay_timestamp()) END AS log_delay;
log_delay
---------------
173781.573457
(1 row)
The only rows in the repmgr.log :
2018-06-23 19:02:48] [WARNING] unable to connect to upstream node "ptkpl-psgsqldb1" (node ID: 5)
[2018-06-23 19:02:48] [NOTICE] node has recovered, reconnecting
[2018-06-23 19:02:48] [NOTICE] reconnected to upstream node after 0 seconds
Now, How can I further investigate it ? my wal_keep_segment is assigned to 100 but since friday 261 wals were generated so I guess I dont have another option but to sync the node again. However, I want to understand why it happened. What can you advice me to check ?
Moreover, I have 2 further questions :
1)Since friday 260 wals were generated, it doesnt sounds like too much ? I didnt have any unusual work. A new wal is generated everytime the older one is getting full or every switch right ? Is there a way to set the size of the wal ?
2)Any recommendation regarding the wal settings ? My current settings :
