Playing with Elephants ~PostgreSQL and Pgpool-II~

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...

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...

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 ...

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 reg...

    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: Authen...

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...