In a Chef Automate high availability (HA) setup, PostgreSQL uses replication to maintain data consistency across multiple nodes.
Proper sequencing ensures that during the patching process, the database remains available, and any role changes (like the election of a new leader) happen smoothly without data loss

We need to patch to addresses vulnerabilities and enhances security, bug fixes and improvements that stabilize the database environment and upgrading to new versions for performance.

Patching Sequence:

• Verify the health of the PostgreSQL server and check the cluster status by running the chef-automate status command, which will also allow you to identify the leader and follower nodes within the cluster.
• Patch Followers First: This ensures that the leader node, which is crucial for maintaining database consistency and availability, remains operational until the followers are updated and synchronized.
• Leader Node: Patch the leader last to minimize the risk of database downtime.

Expect approximately 1-minute outage per machine.

Restart Process During OS Patching:
On Follower 01:

1. Stop the supervisor: systemctl stop hab-sup
2. Start the supervisor: systemctl start hab-sup
3. Monitor rejoining: journalctl -fu hab-sup to check if the follower has rejoined the cluster.
4. Verify 'journalctl'  logs on follower 01 and make sure LAG comes to 0 byte(wait for LAG to be 0), this ensures your follower is now in sync with the leader node.
5. Run 'chef-automate status' to check the status of nodes and then proceed further on follower 02.

On Follower 02:

1. Stop the supervisor: systemctl stop hab-sup
2. Start the supervisor: systemctl start hab-sup
3. Monitor rejoining: journalctl -fu hab-sup to check if the follower has rejoined the cluster.
4. Verify 'journalctl'  logs on follower 02 and make sure LAG comes to 0 byte(wait for LAG to be 0), this ensures your follower is now in sync with the leader node.
5. Run 'chef-automate status' to check the status of nodes and then proceed further on follower 02.

Note: Once you see the following log, proceed with the leader reboot:

automate-ha-postgresql.default hook[health-check]:(HK): Local replica 172.31.64.187 is 0 bytes and <ERR_LAST_REPLAY_UNKNOWN> seconds behind the primary

This confirms that followers are in sync with the leader. Check the logs on both followers before rebooting the leader.

On Leader node:

1. Stop the supervisor: systemctl stop hab-sup
2. Wait for new leader election.
3. Start the supervisor: systemctl start hab-sup

Common questions regarding Postgresql patching;

1. How to identify the leader node in Postgresql?

>> You can run chef-automate status to get details such as which nodes are leader or follower. The output will look something like this:

| postgresql | <IP>| OK | up (pid: 4467) | 00h 08m 52s | Follower |

| postgresql | <IP> | OK | up (pid: 691) | 00h 08m 57s | Leader |


2. As part of OS patching, we would reboot the VM as well if the patch desires. As a result, the 'hab-sup' systemd service would anyway get restarted.
Do we still need to perform below activities post VM reboot of both the followers..
- systemctl stop hab-sup
- systemctl start hab-sup

&

3. As part of patching leader VM, when we perform Leader VM reboot, new leader will get elected and 'hab-sup' systemd service would anyway get restarted. Do we still need to perform above 3 steps post Leader VM reboot?

>> When you reboot the VM, the hab-sup systemd service will indeed get restarted automatically as part of the boot process. However, it is generally good practice to ensure that services are running correctly after the reboot. The steps mentioned (systemctl stop hab-sup and systemctl start hab-sup) can be useful to:

- Ensure that the service restarts cleanly.
- Confirm that the followers rejoin the cluster correctly after the reboot.

Note: The steps mentioned (stopping and starting the hab-sup service) can be seen as manual verification and corrective steps rather than necessary procedures, it's more like a precautionary procedures.

Please be advised: If only patching 1 node, only the node being patched needs to be restarted(a reboot is fine, otherwise recommend a hab stop/start), the other 2 nodes do not need to be touched. But you must wait till the rebooted node(leader or follower) has rejoined the cluster and it is healthy(ie the db cluster is healthy, 3 nodes),before you can patch/reboot another node in the db cluster.

DB cluster requires at least 2 nodes to be healthy at all times for the db to operate i.e you cannot reboot 2 db nodes simultaneously for patching. only one at a time, and wait till the db cluster is fully healthy before patching another node. The quorum count in other words is 2 or 3 db servers. If there are less than 2 healthy db servers, the db does not work.

4. When I had applied patch on the leader node ({IP}) and rebooted it, it did become a follower but saw below value in the LAG column for the {IP} and the LAG came to 0 bytes after 5 to 6 mins. Could you let me know if this is expected & what things the value of bytes would depend on?

>>  The behavior observed with the LAG value increasing temporarily after a node reboot and then decreasing to 0 bytes is normal and expected. It reflects the time required for the follower to catch up with the leader's transactions.

This process ensures that all nodes in the PostgreSQL cluster are synchronized and up-to-date, maintaining the consistency and reliability of the database system.

Below are some expected behaviour during node reboot and role change :

Reboot and role change: When the leader node  is patched and rebooted, it may rejoin the cluster as a follower initially. This role change is part of the PostgreSQL replication and failover mechanism.

Replication Lag: The "LAG" value represents the amount of data (in bytes) and time (in seconds) that the follower is behind the leader. It is expected to see a non-zero LAG value immediately after the follower comes back online because it takes some time to synchronize.

LAG convergence: The LAG value decreasing to 0 bytes over 5 to 6 minutes indicates that the follower has successfully caught up with the leader.

The duration and amount of data in the LAG depend on several factors including the volume of transactions processed by the leader during the downtime of the follower, the network bandwidth, and the efficiency of the replication process.