Monitor the performance of your PostgreSQL High Availability architecture with Datasentinel

post-thumb
You deploy a PostgreSQL HA architecture and want to have an overview of it?

Get the new feature now, to easily and accurately monitor the workload of your instances, including your Read Replicas

This article aims to present the possibilities offered by Datasentinel on a High-Availability PostgresQL Architecture

It takes as an example an architecture with 3 instances managed by Patroni with etcd

  • 1 Master
  • 2 Read Replicas

HaProxy is used for load-balancing and PgBouncer as connection pooler.

Find the official documentation concerning High Availability with PostgreSQL and its administration.

The HA architecture in this article was deployed via ansible with the playbook of the Postgresql Cluster project hosted on github.

Pgbench is used for the benchmark with 2 sets of tests: 1 read/write + 1 read only



Datasentinel configuration

Configure the 3 clusters in direct connection (without going through the load balancer)

2 possibilities:

  1. Install and configure a local agent on each server hosting an instance (See Agent Installation )

or

  1. Configure a connection for each cluster in AgentLess mode from the console.

It is important to specify a common property (a TAG) for the 3 instances (See How to use TAGs efficiently with Datasentinel)

The ha_cluster Tag is chosen to identify the high availability architecture. Each instance will therefore be assigned the tag ha_cluster = sales-app

Configuration example in Agentless mode

AgentLess configuration



Visualization of the HA architecture

From the home page, find your 3-instance architecture by applying the ha_cluster = sales-app filter

You also have activity metrics for each cluster.

PostgreSQL HA cluster Home Page with patroni and etcd



Explore the consolidated activity of your instances

Modules Sessions Workload (aka Active Session History), Top Queries and Top Tables & Indexes allow a consolidated or detailed view by instance.


Module Sessions Workload allows you to visualize at a glance the overall activity of our High-Availability architecture as well as the queries and/or sessions having the greatest impact

PostgreSQL HA cluster Active Session History with patroni and etcd



The module offers many possibilities to visualize the activity, filtered by instance or consolidated.
The examples below give a small overview.

PostgreSQL HA cluster Workload Read Replica with patroni and etcd




Module Top Queries allows in a few clicks to identify the most consuming sqls on a particular dimension. (metrics collected from pg_stat_statements extension)

For example, displaying the queries that require the most disk reads or those that have a high execution time, etc … with the possibility to drill-down into a particular query.

2 examples:

PostgreSQL HA cluster Top queries with patroni and etcd


On the same principle, it is possible to display the Top Tables & indexes on a particular dimension (disk reads, cache access, etc.), filtered by instance or consolidated

1 example:

PostgreSQL HA cluster Top tables with patroni and etcd



Viewing replication statistics

Datasentinel logs replication statistics, allowing you to quickly view for each replica of a cluster the WAL delta size to replicate as well as the replication delay.

PostgreSQL HA cluster Replication stats with patroni and etcd

Datasentinel is compatible with read replicas of PostgreSQL managed clusters from major public cloud providers:
Amazon RDS, Microsoft Azure, Google Cloud, …

Conclusion


Find this example of HA architecture on our Demo Site where you can try the described features and much more

Datasentinel allows the performance monitoring of your most critical PostgreSQL HA instances with precision.

Datasentinel unique features and the simplexity of its GUI allow a quick start to easily and precisely analyze the workload of all your Postgres instances, HA or not


Stay connected!

If you are an Amazon Web Services customer, Datasentinel is also present on the AWS marketplace allowing you to have a platform available in a few clicks