Playing with Elephants ~PostgreSQL and Pgpool-II~

Logging and debugging help to monitor and  identify issues or problems occurring in your program. Sometimes we need to log debug information to figure out the problems during software development and testing. However, i f debug is enabled,  a large number of debug messages are generated and it is hard to read. Proper logging and debugging configurations are important. There are a number of ways to retrieve debug information from Pgpool-II. In this post, I will describe the various ways for logging and debugging Pgpool-II.  Logging Before Pgpool-II 4.1, some log processing tools (e.g. rsyslog) are required to store Pgpool-II logs and rotate them.   For example, b elow are the relevant configuration parameters for logging to syslog. log_destination = 'syslog' syslog_facility = 'LOCAL1' syslog_ident = 'pgpool'  Since Pgpool-II 4.2, logging collector process has been implemented. The logging collector process collects log messages sent to stderr and redirects t...

Logging is an important topic and particularly useful for troubleshooting, debugging and monitoring. Many applications have their own built-in logging mechanism. Pgpool-II logging mechanism is similar to PostgreSQL logging mechanism. Pgpool-II log management system supports two ways for logging messages (stderr and syslog) and the logging collector process collects the logs sent to stderr and redirects them into log files. However, how can we manage the logging system on Kubernetes? In this blog, I will describe how to manage Pgpool-II container logs on Kubernetes. Logging on Kubernetes  Kubernetes supports the functionality to view the container logs by using “ kubectl logs ” command.  kubectl logs < pod name > However, logging on Kubernetes is complicated because the logs will be deleted when containers are terminated or recreated. Fluentd helps you to collect container logs and send the logs to desired destinations such as Amazon S3, MySQL, MongoDB, etc. How to use...

Pgpool-II has been released for more than 15 years. When the version 1.0 was released, Pgpool-II had only replication mode. With the increase of new features (e.g. automatic failover, support for logical replication, etc.), the parameters for configuring cluster mode have increased.  Clustering mode Pgpool-II has six clustering modes available:  Streaming replication mode Native replication mode Snapshot isolation mode (Since Pgpool-II 4.2) Logical replication mode Slony mode Raw mode It is important to specify the correct clustering mode to make Pgpool-II work as expected, because the clustering mode is used to determine how the data is replicated. Since Pgpool-II 4.2, the multiple parameters for configuring cluster mode have been merged into a single parameter backend_clustering_mode . clustering mode Pgpool-II 4.1 or previous Pgpool-II 4.2 or later master_slave_mode master_slave_sub_mode replication_mode backend_clustering_mode Strea...

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

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