How does eG Enterprise Monitor PostgreSQL Cluster?
PostgreSQL is an Open-source Relational Database System. Relational (SQL) and non-relational (JSON) querying are both supported by PostgreSQL. A database cluster is a collection of databases that are managed by a single instance of a running database server. Using replication, PostgreSQL databases can be formed into a highly available and fault-tolerant cluster. The PostgreSQL cluster consists of a master node and a standby node. Primary-Standby may be the most basic HA architecture you can set up and, oftentimes, the easiest to implement and maintain. It’s based on one primary database with one or more standby servers. These standby databases will remain synchronized (or almost synchronized) with the primary node, depending on whether the replication is synchronous or asynchronous. If the primary server fails, the standby server contains almost all of the primary server’s data and can quickly be turned into the new primary database server.
Figure 1 : PostgreSQL Cluster architecture
Also, multi-master (active-active/primary-primary) distributed PostgreSQL allows you to have multiple master databases spread across different locations (multiple nodes), each capable of handling read and write traffic simultaneously. One of the key features of multi-master (active-active/primary-primary) distributed Postgres is bi-directional replication. This allows you to replicate data between multiple instances in real-time, ensuring that data is always up-to-date across all instances. Another most significant advantage of multi-master PostgreSQL is its ability to ensure high availability and fault tolerance. With multiple active nodes, the database can continue to function even if one or more nodes fail. In the event of a node failure, the remaining healthy nodes take over the workload, ensuring continuous data access and minimal downtime. Load balancing is a key feature of multi-master PostgreSQL that distributes read and write operations across all participating nodes and ensures that the database workload is evenly balanced.
PostgreSQL itself does not include an automatic failover mechanism, so this will require some custom script or third-party tools for this automation. Hence, PostgreSQL Cluster makes use of Pacemaker and Corosync to ensure high availability and Pgpool-II or HAProxy as load balancers.
How to monitor PostgreSQL Cluster using eG Enterprise?
eG Enterprise offers a 100% web-based PostgreSQL cluster monitoring model using which you can monitor any PostgreSQL Cluster architecture, such as primary-standby and multi-master (active-active/primary-primary). eG uses the same monitoring approach, as in Figure 2, to monitor both primary-standby and multi-master (active-active/primary-primary) PostgreSQL clusters. To monitor PostgreSQL cluster eG Enterprise offers the following component model and monitoring licenses:
-
The PostgreSQL cluster has to be added manually by providing the IP address of the Master node.
-
To collect clustering-related metrics such as Cluster availability, Failover, Node status, etc., a separate eG component, which is the PostgreSQL Cluster component, can be added and monitored using an “external agent”. The external agent does not consume any separate license. This external agent communicates with the cluster through JDBC drivers using SQL queries.
-
The individual instances in the cluster will have their own storage and individual IP. Hence, the applications use individual IPs to connect to each node in the cluster. Therefore, each node will be monitored as a PostgreSQL component using an “internal agent” deployed on the node. This agent collects application and operating system metrics.
-
Every internal agent deployed on each node of the cluster consumes a Premium Monitor license. Hence, the number of premium monitor license required is determined based on the number of nodes in the cluster. For example, in Figure 2, there are three nodes in the cluster, which hence requires three premium monitor licenses.
