Deploying Pgpool-II Exporter with Docker

-

Pgpool-II Exporter is an open source exporter which exposes Pgpool-II and PostgreSQL cluster's metrics which can be collected by Prometheus. In this previous post, I described how to build and run Pgpool-II Exporter on a local machine. PgPool Global Development Group provides Docker images for Pgpool-II Exporter to simply and speed up the deployment process. In this post, I'll show how to get Pgpool-II Exporter running using Docker. 

To deploy and verify Pgpool-II Exporter, you need:

  • Two PostgreSQL containers configured with streaming replication (1 primary and 1 replica)
  • A running Pgpool-II container. The Pgpool-II container will direct write queries to the primary and read queries to the primary or replica.

Let's get started.

Running a PostgreSQL streaming replication cluster

In this post, we use Crunchy Data Container to deploy a PostgreSQL streaming replication cluster manually. In a production environment, PostgreSQL Operator for Kubernetes is the recommended way to deploy and manage PostgreSQL containers.

The following commands and configurations are for testing only. For more advanced usage, please refer to Crunchy Data Container.

Create a network

Create a network which allows your application containers to communicate with each other by using their container name as a hostname.

docker network create --driver bridge pg-network

Start the primary container

Start the PostgreSQL primary container with the following command:

docker run -d --rm \
    --name=primary \
    --network pg-network \
    -e MODE=postgres \
    -e PG_MODE=primary \
    -e PG_PRIMARY_PORT=5432 \
    -e PG_PRIMARY_USER=repluser \
    -e PG_PRIMARY_PASSWORD=password \
    -e PG_USER=testuser \
    -e PG_PASSWORD=password \
    -e PG_ROOT_PASSWORD=password \
    -e PG_DATABASE=userdb \
    -d crunchydata/crunchy-postgres:centos8-13.2-4.6.2

  • MODE: specify the running mode of PostgreSQL. (Running modes)
  • PG_MODE: specify the role (primary or replica)
  • PG_PRIMARY_PORT: the primary PostgreSQL port
  • PG_PRIMARY_USER: specify the username of the replication user
  • PG_PRIMARY_PASSWORD: specify the password of the replication user
  • PG_USER: specify the username of the general user
  • PG_PASSWORD: specify the password of the general user
  • PG_ROOT_PASSWORD: specify the password of the superuser
  • PG_DATABASE: create an initial database when container is started

Start the replica container

Start the PostgreSQL replica container with the following command:

docker run -d --rm \
    --name=replica \
    --network pg-network \
    -e MODE=postgres \
    -e PG_MODE=replica \
    -e PG_PRIMARY_HOST=primary \
    -e PG_PRIMARY_PORT=5432 \
    -e PG_PRIMARY_USER=repluser \
    -e PG_PRIMARY_PASSWORD=password \
    -e PG_USER=testuser \
    -e PG_PASSWORD=password \
    -e PG_ROOT_PASSWORD=password \
    -e PG_DATABASE=userdb \
    crunchydata/crunchy-postgres:centos8-13.2-4.6.2

  • PG_PRIMARY_HOST: specify the container name of primary.

You can use the following command to verify that replication was configured properly. 

docker exec primary sh -c  "PGPASSWORD=password psql -U postgres -h 127.0.0.1 -c \"select * from pg_stat_replication\""
 pid | usesysid | usename  | application_name | client_addr | client_hostname | client_port |         backend_start         | backend_xmin |   state   | sent_lsn  | write_lsn | flush_lsn | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state |          reply_time           
-----+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+--------------+-----------+-----------+-----------+-----------+------------+-----------+-----------+------------+---------------+------------+-------------------------------
 101 |    16403 | repluser | c90d00e2f2ce     | 172.18.0.3  |                 |       60080 | 2021-07-26 10:45:35.437292+00 |              | streaming | 0/3006A50 | 0/3006A50 | 0/3006A50 | 0/3006A50  |           |           |            |             0 | async      | 2021-07-26 14:08:56.907519+00
(1 row)

Running the Pgpool-II container

Next, let's start the Pgpool-II container. 

To perform authentication, in general all the PostgreSQL users and passwords must be registered in pool_passwd. In this example we only register the password of postgres user in pool_passwd for testing and use the postgres user to perform health check and replication delay check.

Environment variables starting with PGPOOL_PARAMS_ can be converted to Pgpool-II's configuration parameters and override the default configurations.

docker run -d --rm \
    --name pgpool \
    --network pg-network \
    -e POSTGRES_USERNAME=postgres \
    -e POSTGRES_PASSWORD=password \
    -e PGPOOL_PARAMS_BACKEND_HOSTNAME0=primary \
    -e PGPOOL_PARAMS_BACKEND_HOSTNAME1=replica \
    -e PGPOOL_PARAMS_BACKEND_PORT0=5432 \
    -e PGPOOL_PARAMS_BACKEND_PORT1=5432 \
    -e PGPOOL_PARAMS_SR_CHECK_PERIOD=10 \
    -e PGPOOL_PARAMS_SR_CHECK_USER=postgres \
    -e PGPOOL_PARAMS_HEALTH_CHECK_PERIOD=10 \
    -e PGPOOL_PARAMS_HEALTH_CHECK_USER=postgres \
    pgpool/pgpool

Verify the Pgpool-II container setup using the following command:

docker exec pgpool sh -c "PGPASSWORD=password psql -U postgres -h 127.0.0.1 -p 9999 -c \"show pool_nodes\""
 node_id | hostname | port | status | lb_weight |  role   | select_cnt | load_balance_node | replication_delay | replication_state | replication_sync_state | last_status_change  
---------+----------+------+--------+-----------+---------+------------+-------------------+-------------------+-------------------+------------------------+---------------------
 0       | primary  | 5432 | up     | 0.500000  | primary | 8          | true              | 0                 |                   |                        | 2021-07-26 11:33:45
 1       | replica  | 5432 | up     | 0.500000  | standby | 0          | false             | 0                 |                   |                        | 2021-07-26 11:33:45
(2 rows)

Running the Pgpool-II Exporter container

Once the Pgpool-II container is ready, let's start the Pgpool-II Exporter container.

docker run -d --rm \
    --name pgpool2_exporter \
    --network pg-network \
    -e POSTGRES_USERNAME=postgres \
    -e POSTGRES_PASSWORD=password \
    -e PGPOOL_SERVICE=pgpool \
    -e PGPOOL_SERVICE_PORT=9999 \
    pgpool/pgpool2_exporter
  • POSTGRES_USERNAME: specify the username of the PostgreSQL user used to connect to PostgreSQL
  • POSTGRES_PASSWORD: specify the password of the PostgreSQL user used to connect to PostgreSQL
  • PGPOOL_SERVICE: specify Pgpool-II hostname
  • PGPOOL_SERVICE_PORT: specify Pgpool-II port number

If the Pgpool-II Exporter container has been started successfully, you can see the following messages in the container logs. 

docker logs pgpool2_exporter
ts=2021-07-26T11:43:57.787Z caller=log.go:168 level=debug msg="Querying namespace" namespace=pool_cache
ts=2021-07-26T11:43:57.788Z caller=log.go:168 level=debug msg="Querying namespace" namespace=pool_nodes
ts=2021-07-26T11:43:57.788Z caller=log.go:168 level=debug msg="Querying namespace" namespace=pool_processes
ts=2021-07-26T11:43:57.789Z caller=log.go:168 level=debug msg="Querying Pgpool-II version"
ts=2021-07-26T11:43:57.789Z caller=log.go:168 level=debug pgpool_version=4.2.3
ts=2021-07-26T11:43:57.789Z caller=log.go:168 level=info msg="Starting pgpool2_exporter" version="(version=1.0.0, branch=master, revision=71733ed2a04b5f872cbe2293ab7fbbfa350b7924)"
ts=2021-07-26T11:43:57.789Z caller=log.go:168 level=info msg="Listening on address" address=:9719

You can use curl command to obtain the Prometheus metrics:

docker run --rm --network pg-network centos sh -c "curl -s pgpool2_exporter:9719/metrics"

You should see output like below:

...
# HELP pgpool2_frontend_total Number of total child processed
# TYPE pgpool2_frontend_total gauge
pgpool2_frontend_total 32
# HELP pgpool2_frontend_used Number of used child processes
# TYPE pgpool2_frontend_used gauge
pgpool2_frontend_used 1
# HELP pgpool2_last_scrape_duration_seconds Duration of the last scrape of metrics from Pgpool-II.
# TYPE pgpool2_last_scrape_duration_seconds gauge
pgpool2_last_scrape_duration_seconds 0.001410071
# HELP pgpool2_last_scrape_error Whether the last scrape of metrics from Pgpool-II resulted in an error (1 for error, 0 for success).
# TYPE pgpool2_last_scrape_error gauge
pgpool2_last_scrape_error 0
# HELP pgpool2_pool_backend_stats_ddl_cnt DDL statement counts issued to each backend
# TYPE pgpool2_pool_backend_stats_ddl_cnt gauge
pgpool2_pool_backend_stats_ddl_cnt{hostname="primary",port="5432",role="primary"} 0
pgpool2_pool_backend_stats_ddl_cnt{hostname="replica",port="5432",role="standby"} 0
# HELP pgpool2_pool_backend_stats_delete_cnt DELETE statement counts issued to each backend
# TYPE pgpool2_pool_backend_stats_delete_cnt gauge
pgpool2_pool_backend_stats_delete_cnt{hostname="primary",port="5432",role="primary"} 0
pgpool2_pool_backend_stats_delete_cnt{hostname="replica",port="5432",role="standby"} 0
...

Conclusion

In this blog post, I have described how to run PostgreSQL cluster, Pgpool-II and Pgpool-II Exporter in containers. However, managing multiple database containers requires a lot of manual processes. For more advanced configuration, such as automated deployment and high availability, we recommend deploying your PostgreSQL servers and applications on Kubernetes. You can check out Crunchy PostgreSQL Operator, Zalando PostgreSQL Operator and Pgpool-II on Kubernetes which can help you deploy PostgreSQL and Pgpool-II on Kubernetes.

Comments

Post a Comment

Popular posts from this blog

Installing Pgpool-II on Debian/Ubuntu

-
First of all, many thanks to the PostgreSQL Global Development Group (PGDG) for creating Pgpool-II packages for Debian and Ubuntu. This post shows you step by step how to install Pgpool-II using official APT repository provided by PGDG. Prerequisites This blog assumes you have already installed two PostgreSQL 14 servers and setup streaming replication between the PostgreSQL servers.   For testing purpose,we set trust authentication in pg_hba.conf . In a production environment, Please follow the PostgreSQL and Pgpool-II documentation to configure proper authentication settings.  Install Pgpool-II To use the PostgreSQL apt repository, follow the steps below. Create the repository configuration file: # echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list Import the repository signing key: # wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - Update the package lists: #
Read more

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
Read more

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
Read more