The pgpool II community is gearing up to release the Alpha version of its next major release; pgpool II 4.2. It is going to be another exciting release of pgpool II that is a middleware product and provides mission critical functionality like load balancing, high availability, connection pooling etc for PostgreSQL server. We have written in detail about some of the major features of pgpool II 4.2 i.e. LDAP authentication support, supporting snapshot isolation mode etc, the purpose of this blog is provide brief description about all the major features provided in the 4.2 release.
The focus for last couple of major releases for pgpool II was primarily performance and high availability. The focus for this release was around security and improving user experience by extending the functionality of pgpool II. Another major feature that didn’t make it into this release due to resource constraint was GUI interface for configuration, management and monitoring of pgpool II cluster. This is a much needed feature in order to improve the user experience of pgpool II and make it easy to configure and deploy pgpool II cluster. Some of the infrastructure needed for supporting this feature like improved statistics etc was added (will discuss it in the blog) but GUI interface didn’t make it due to resource constraints.
Below is the summary of most of major and minor features added in pgpool II 4.2 release :
Logging Collector
Similar to community PostgreSQL, the logging_collector parameter that accepts the boolean value is added to added to pgpool II. The logging collector is a background process that captures the log messages and directs them to stderr or into log files. Please note that this parameter can only be set at pgpool II start.
log_disconnections (boolean)
This is another parameter added in pgpool II that is analogous to PostgreSQL, the log_disconnections parameter takes a boolean value. The purpose of this parameter is to log all the client terminations with pgpool II to the log destination.
Please note that this parameter can be changed by reloading the pgpool II configuration file.
Health Check Improvements
Pgpool-II periodically connects to configured PostgreSQL servers in order to detect any errors or faults on the server or the network. The procedure of periodically checking the state of the server or network is called health check. Please note that health check required an extra connection so the user need to adjust the max_connections parameter of PostgreSQL accordingly.
SHOW POOL_HEALTH_CHECK_STATS
Pgpool-II 4.2 provides the command to show statistic of health check by using “SHOW POOL_HEALTH_CHECK_STATS”, the health check statistic shown by this command are collected by the process that performs the health checking.
This command is really helpful for system administrator when diagnosing faults and failures, for example the admin can easily locate the failover event in the log file by looking at “last_failed_health_check” column. Another example is finding unstable connection to backend by evaluating “average_retry_count” column, if a particular node shows higher retry count then other node, there may be a problem to the connection.
Please refer to the link for details on the statistical information show by the “SHOW POOL_HEALTH_CHECK_STATS” command.
https://tatsuo-ishii.github.io/pgpool-II/current/sql-show-pool-health-check-stats.html
SHOW POOL_BACKEND_STATS
This is another very useful command for displaying the pgpool II backend statistics, the command displays the node id, hostname, port, status, role and the counts of following queries issued to each backend :
- Select
- Insert
- Update
- Delete
- DDL
- Other query
This command is really useful in understand the type of traffic sent to each backend server, please visit the link below for details on this command.
https://tatsuo-ishii.github.io/pgpool-II/current/sql-show-pool-backend-stats.html
LDAP Authentication Support
This is major feature added to pgpool II 4.2, it provides LDAP connection between client and pgpool II server. This was a much awaited feature to support LDAP connectivity between client and pgpool II server, the support for LDAP connectivity between pgpool II and backend server is there already. With the addition of this feature in pgpool II 4.2, the user can get end to end LDAP connectivity from client thru pgpool II thru backend PostgreSQL server. This is really helpful in using the same LDAP server for getting complete end of end connectivity with pgpool II and backend server.
I have written a detailed blog on how to get the LDAP connectivity working with pgpool II, it dwells into how to get the LDAP server setup and configured and how to get it working with pgpool II setup. The blog is really helpful for user trying to get LDAP connectivity working with pgpool II
PCP reload config
This is minor but very useful feature to reload the configuration file on the local pgpool II node or reload the configuration on all pgpool II nodes. The pcp reload config takes —scope (or -s) command line switch, the user can pass -c to the —scope and it will reload all the configuration files of pgpool II cluster nodes. If you pass -l to the —scope, it will only reload the configuration fie on the local pgpool II node.
Snapshot Isolation Mode
This is a major and complex feature added in pgpool II 4.2, this is really critical for a distributed system where transactions are spanned over multiple servers. The scale out solutions that are being implemented in community PostgreSQL can also learn from this feature implemented in middleware pgpool.
The feature is snapshot isolation that guarantees atomic visibility among nodes, the implementation is based on a research paper Pangea: An Eager Database Replication Middleware guaranteeing Snapshot Isolation without Modification of Database Servers. This feature avoids the inconsistency that can be caused among nodes due to visibility inconsistency. This is a must needed feature for a write workload application that does global transaction involving multiple nodes.
Please visit this link to see more details about this feature..
Importing PostgreSQL 13 Parser
Every major PostgreSQL release has compatibility with parser of the latest PostgreSQL release, pgpool II 4.2 contains compatibility with PostgreSQL 13 parser. Any new grammar rules or features added in PostgreSQL 13 parser will be compatible with pgpool II 4.2 This mean that 4.2 will recognise the new keywords added in PG-13 and deal with it accordingly.
Conclusion
I have given brief introduction of most of the major and minor features added in pgpool II 4.2 release, links are provided where necessary to provide more details about some of the features.
It is clearly evident that pgpool II has come a long way in terms of functionality, security and stability in last few years. It is becoming the middleware of choice with PostgreSQL.
The next major release of pgpool II after 4.2 will focus on easy of use and providing a graphical user interface that make the configuration, management and monitoring of Pgpool-II easy.
Ahsan Hadi
Recent Comments