Pgpool-II's Clustering Modes

-


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: 

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
Streaming replication mode on stream off streaming_replication
Logical replication mode on logical off logical_replication
Slony mode on slony off slony
Native replication mode off - on native_replication
Raw mode off - off raw
Snapshot isolation mode - - - snapshot_isolation

 

In the next sections, I will describe how the data is replicated in each clustering mode.

Streaming replication mode

This mode is most commonly used and recommended clustering mode. In this mode, PostgreSQL is responsible for replicating data.

To enable this mode:

backend_clustering_mode = 'streaming_replication'

Native replication mode

In this mode, Pgpool-II is responsible for replicating data. Pgpool-II replicates the same queries to each PostgreSQL server for database synchronization.

To enable this mode:

backend_clustering_mode = 'native_replication'

Snapshot isolation mode

Snapshot isolation mode which adds the visibility consistency among PostgreSQL servers is an extension of the native replication mode.

To enable this mode:

backend_clustering_mode = 'snapshot_isolation'

Logical replication mode

In logical replication mode, Pgpool-II manages PostgreSQL servers setup with logical replication. In this mode, PostgreSQL is responsible for replicating data objects between PostgreSQL servers.

To enable this mode:

backend_clustering_mode = 'logical_replication'

Slony mode

Pgpool-II supports for Slony-I. If you are using Slony-I to synchronize data between PostgreSQL servers, you need to enable this mode. In this mode, Slony-I is responsible for replicating data.

To enable this mode:

backend_clustering_mode = 'slony' 

Raw mode

In this mode, Pgpool-II is not responsible for data replication. This mode is only used for the purposes, such as limiting excess connections to PostgreSQL servers or enabling failover with multiple PostgreSQL servers.

backend_clustering_mode = 'raw'

Supported features in each clustering mode

Pgpool-II is a feature-rich middleware that supports the following features:

  • Load balancing
  • Connection pooling
  • Automatic failover
  • Replication
  • Online recovery
  • In memory query cache

Because the documentation doesn't mention the available features in each clustering mode, let me summarize here.

The table below shows which features are available in each mode.


 Streaming replication mode Native replication mode Snapshot isolation mode Logical replication mode Slony mode Raw mode
Load balancing ✓ ✓ ✓ ✓ ✓ -
Connection pooling ✓ ✓ ✓ ✓ ✓ ✓
Automatic failover ✓ ✓ ✓ ✓ ✓ ✓
Replication - ✓ ✓ - - -
Online recovery ✓ ✓ ✓ - - -
In memory query cache ✓ ✓ ✓ ✓ ✓ ✓

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