Posts

Various Ways to Retrieve Pgpool-II's Statistics

Image
  Pgpool-II is a PostgreSQL cluster management tool. In this post I will explain how to retrieve the cluster statistics from Pgpool-II. There are 3 ways to retrieve cluster statistics: use SHOW SQL commands          The SHOW commands can be issued inside a SQL session. They are not forwarded to PostgreSQL and only processed by Pgpool-II.  use PCP commands          Since PCP commands can be executed via network, you are able to operate Pgpool-II from remote servers using PCP commands. use pgpool_adm extension            Pgpool_adm is an extension to allow to access to PCP commands. The advantage of using pgpool_adm is that you can specify specific conditions to retrieve the required data. To use pgpool_adm you need to install pgpool-II-pg*-extensions package and execute " CREATE EXTENSION pgpool_adm " on PostgreSQL servers.   Next, let's see how to retrieve Pgpool-II's statistics. Backend nodes statistics To retrieve backend nodes statistics, you can execute the fol

Query Load Balancing in Pgpool-II

Image
Pgpool-II is a PostgreSQL cluster management tool. The major features of Pgpool-II are: Connection pooling Query load balancing Automated failover Watchdog (High availability of Pgpool-II) Replication In memory query cache In this post, I will describe the query load balancing mechanism in Pgpool-II and the relevant configuration parameters. Why use query load balancing? Query load balancing can distribute database server workloads across multiple PostgreSQL servers. Nowadays, most database systems use multiple replicated database servers to achieve high availability for PostgreSQL. Pgpool-II takes the advantage of the replication feature in order to distribute the workloads across multiple PostgreSQL servers. If there is a PostgreSQL cluster with multiple PostgreSQL servers, Pgpool-II is able to distribute READ queries across those PostgreSQL servers. The benefit of READ queries load balancing is, improve the system's throughput reduce the load on each PostgreSQL server optimize

Deploy Pgpool-II on Kubernetes to Achieve Query Load Balancing and Monitoring

Image
In my previous blog post , I have introduced how to monitor Pgpool-II and PostgreSQL cluster using Pgpool-II Exporter. As I mentioned in the previous post, Pgpool-II Exporter exposes metrics that Prometheus can collect. Prometheus is one of the most popular monitoring tools used on Kubernetes. In this post, I would like to introduce how to deploy Pgpool-II and Pgpool-II Exporter on Kubernetes. Architecture A PostgreSQL operator is required to manage PostgreSQL clusters on Kubernetes. Therefore, you need to combine Pgpool-II with a PostgreSQL operator. Below is the architecture: Prerequisites Before you start deploying Pgpool-II, please check the following prerequisites: Make sure you have a Kubernetes cluster, and kubectl is installed. PostgreSQL Operator and a PostgreSQL cluster are installed. In the blog, I used the PostgreSQL Operator Crunchy PostgreSQL Operator to create a PostgreSQL cluster. Deploy Pgpool-II and Pgpool-II Exporter We deploy the Pgpool-II pod that contains a P

Monitoring PostgreSQL Cluster via Pgpool-II with Prometheus

Image
Database monitoring is important in production. Effective database monitoring helps you to visualize database activities and detect errors. Pgpool-II Exporter can expose Pgpool-II and PostgreSQL cluster's metrics which can be collected by Prometheus . In this post, I will describe how to use Pgpool-II Exporter to monitor Pgpool-II and PostgreSQL cluster's statistics and visualize them with Prometheus and Grafana . What is Prometheus? Prometheus is a leading cloud-native monitoring solution. Prometheus collects application metrics from monitored targets which expose Prometheus-formatted metrics. Prometheus metrics exporter provides the endpoint to scrape. The most popular one is node_exporter which collects system metrics such as CPU, memory and disk space usage for Linux servers. Pgpool-II Exporter Pgpool-II Exporter uses SHOW command to collect Pgpool-II and PostgreSQL Cluster's metrics, including: name Description pgpool2_frontend_total Number

How to Configure SCRAM and MD5 Authentication in Pgpool-II

Image
This post refers to my previous one  Authentication in Pgpool-II . In the post I introduced how Pgpool-II authentication mechanism works. In this post I will describe how to configure SCRAM and MD5 authentication methods in details. scram-sha-256 Authentication Configuration To perform the password-based authentication, Pgpool-II requires a password file which contains a list of database users and passwords in format username:password . The default password file name is pool_passwd . If scram-sha-256 is specified as the authentication method in pool_hba.conf or pg_hba.conf , to use scram-sha-256 authentication, the decryption key to decrypt the passwords is required. We create the .pgpoolkey file in the home directory of Pgpool-II start user. Here we assume that Pgpool-II is started using postgres user.   # su - postgres $ echo 'some string' > ~/.pgpoolkey $ chmod 600 ~/.pgpoolkey   Assuming you've already created a database user pgpool . Execute the pg_enc to regis

Authentication in Pgpool-II

Image
    Pgpool-II is a proxy software for PostgreSQL cluster and it supports several authentication methods. How does Pgpool-II perform user authentication? In this post, I will introduce the authentication methods which Pgpool-II supports and how Pgpool-II authentication mechanism works. Authentication methods in Pgpool-II Pgpool-II supports several authentication methods: Trust Authentication MD5 Password Authentication   scram-sha-256 Authentication (Pgpool-II 4.0 or later) Certificate Authentication   PAM Authentication LDAP Authentication (from the next major release 4.2)  Starting with Pgpool-II 4.0, Pgpool-II supports scram-sha-256 authentication. scram-sha-256 authentication method is strongly recommended because it is the most secure password-based authentication method. How does Pgpool-II authentication mechanism work? Since Pgpool-II is a PostgreSQL proxy that works between clients and PostgreSQL servers, the authentication comprises two steps: Authentication between client

Connection Pooling in Pgpool-II

Image
Pgpool-II is a cluster management tool for PostgreSQL that can cache connections to PostgreSQL servers. This blog introduces Pgpool-II connection pooling feature and shows how to configure connection pooling in Pgpool-II. What is connection pooling? Establishing and maintaining Database connections are expensive. The reason is that you have to establish a network connection, perform authentication and so on. Database connection pooling is a cache of database connections to keep database connections open so that the connection can be reused when a connection is required by a future request. Reusing an active connection rather than establishing a new connection each time a connection is requested can improve performance and save system resources. How does connection pooling work in Pgpool-II? Pgpool-II caches established connections to the PostgreSQL servers and reuses them whenever a new request with the same properties (i.e. user name, database, protocol version, and other con