For monitoring database cluster, various 3rd party monitoring tools exists that are leverage PostgreSQL logging, events and specific queries. However, this section will not cover any external tool, but will rather give a slight overview upon PostgreSQL built in monitoring mechanisms,

Database logs

By default, database logs are located in data folder (e.g. PGSQL-data/pg_log) and is rolled over based on both log file size and file age. Log configuration could be changed in postgres.conf file. Log files should be checked whenever the recovery and/or fail-over is taking place, as well as when the database had started, to verify the process is behaving as expected.

In the context of Streaming Replications used for synchronizing databases between primary and standby databases, when starting the standby database , the following message should appear in the database log:

It signifies, that all the changes from the primary database has been successfully applied, and the database is started in hot-standby mode, accepting read only connections.

When the primary database fails and the automatic fail-over is setup, the following logs will be recorder on the standby database:

More about database logging could be seen in official PostreSQL documentation here https://www.postgresql.org/docs/current/runtime-config-logging.html

Monitor database role

To check, which database is currently the primary database, execute the following query 

If the result is true, queried database is standby; if false, it is primary.

Optionally, pgmetrics tool could be used to list a handy overview of database current status also with various details not only about replication. 

Queries for primary database

To get list of all standby servers as well as their current replication performance, run the following query:

• sending_lag could indicate heavy load on primary 
• receiving_lag could indicate network issues or replica under heavy load 
• replaying_lag could indicate replica under heavy load

To find out which file is currently used for write ahead log, execute this query:

Queries for standby database

To check delay on the standby, execute the following query:

The result is the number of seconds between the transaction was aborted or committed on primary and the time it was received on the standby. If the replication did not started or failed to start, this query will return NULL.

Please note, that if the system time of these servers differs, the resulted number may be misleading.

Automated monitoring

Automated monitoring is based on checking all the databases from any server on the network (will refer to this server as Monitoring server) in the predefined intervals and automatically log Windows Log Events in the following cases:

• primary server is inactive
• any standby server is inactive
• more than one primary server is active

This is achieved by Windows Scheduler invoking Powershell script which is using the same monitoring tools as described in this section above  - executing SQL queries. Script could be scheduled on any server in the network, if the following preconditions have to be met:

• server has a network visibility to monitored databases, with allowed connection to the database ports
• PostrgreSQL CLI utilities has to be in PATH system environment variable (Dispatcher Paragon with embedded PostreSQL has CLI tools at '<PATH_TO_STANDBY_DATABASE_ROOT_FOLDER>\bin' folder. Default installation path of PostgreSQL CLI utilities is C:\Program Files\PostgreSQL\16\bin ).

1. For executing the script, a new database user with limited privileges has to exists. Its possible to reuse existed user, but its recommended to create a specific one for monitoring purposes only. On the primary database, create new user "cluster_monitor" (user will be replicated to the standby database automatically). 

   CREATE USER cluster_monitor ENCRYPTED PASSWORD '<CLUSTER_MONITOR_USER_PASSWORD>';

2. Add the IP address of the Monitoring server to the pg_hba.conf file on both primary and standby database.  

   host postgres cluster_monitor <IP_ADDRESS_OF_THE_MONITOR_SERVER>/32 md5

3. n the monitoring server add an entry to the pgpass.conf file for each database server. Default location of pgpass.conf  is in %APPDATA%\postgresql\pgpass.conf (where %APPDATA% refers to the Application Data subdirectory in the user's profile). This is standard PostgreSQL security feature - for more details refer to the official documentation: https://www.postgresql.org/docs/current/libpq-pgpass.html

   <PRIMARY_DATABASE_IP_ADDRESS>:<DATABASE_PORT>:postgres:cluster_monitor:<CLUSTER_MONITOR_USER_PASSWORD>

   <STANDBY_DATABASE_IP_ADDRESS>:<DATABASE_PORT>:postgres:cluster_monitor:<CLUSTER_MONITOR_USER_PASSWORD>

   PATH Variable

   Please note, for the script to work properly the System Environment Variable must be set properly. The script needs to access pg_isready.exe and psql.exe, so those files must be on the monitoring server and the proper path must be set in PATH system environment variables. Sometimes a restart of the Windows machine is required in order to force through the changes. 

4. Copy the PowerShell script PgMonitor.ps1 to the Monitoring server
5. On the Monitoring server
   1. create a new scheduled task in the Task Scheduler
      
   2. In "General" tab, fill the name, description, check "Run whether user is logged on or not" and "Run with highest privileges".
      
      
   3. In "Triggers" tab, create new trigger, that will run every 5 minutes and never expires.
      
      
      

   4. In the "Actions" tab, create a new action that will start a script. Use Powershell as program to start and add following arguments. At least 2 IP addresses are required.
      

      -File "<path_to_script>\PgMonitor.ps1" <PRIMARY_DATABASE_IP_ADDRESS>:<DATABASE_PORT>,STANDBY_DATABASE_IP_ADDRESS:<DATABASE_PORT> cluster_monitor

      

6. Save the scheduled task and trigger it manually to ensure it will run in predefined interval. Wait till the task runs at least once and check Windows Event Application log for errors. In the case of properly configured task, no event is listed there.

7. (Optional) if you are not sure if the script is invoked correctly, you can turn on logging by add log parameter to turn logging on. Example: -log "C:\logs\PgMonitor.log"

   -File "<path_to_script>\PgMonitor.ps1" <PRIMARY_DATABASE_IP_ADDRESS>:<DATABASE_PORT>,STANDBY_DATABASE_IP_ADDRESS:<DATABASE_PORT> cluster_monitor "C:\logs\PgMonitor.log"

When automatic monitoring script detects the failure, it logs a Warning Windows event in Event Log.

The following events are logged to the Windows Event Log.