A.63. Release 4.0

A.63.1. Overview

This version adds support for SCRAM and CERT authentication, improves load balancing control and import PostgreSQL 11 new SQL parser.

Major enhancements in Pgpool-II 4.0 include:

• Add SCRAM and Certificate authentication support.

• Detecting "false" primary server of PostgreSQL.

• Improvement of load balancing:

  • More load balancing fine control after write queries.

  • Load balancing control for specific queries.

  • Allow to specify load balance weight ratio for load balance parameters.

• Add last state change timestamp to SHOW POOL NODES.

• Import PostgreSQL 11 SQL parser.

• Logging client messages.

A.63.2. Migration to Version 4.0

Version 4.0 contains a number of changes that may affect compatibility with previous releases. Observe the following incompatibilities:

• Add 1st/2nd stage online recovery commands parameter to get the node number to be recovered. (Tatsuo Ishii)

  Online recovery script now accepts 5 parameters, rather than 4 (the 5th parameter is node number to be recovered). Run ALTER EXTENSION pgpool_recovery UPDATE TO '1.2' to update pgpool_recovery version. Existing 4-parameter-style recovery scripts can be used if you don't care about information provided by the 5th parameter.

  See recovery_1st_stage_command and recovery_2nd_stage_command for more details.

• fail_over_on_backend_error parameter is renamed to failover_on_backend_error. (Muhammad Usama)

  Now we throw a warning message when old config name fail_over_on_backend_error is used instead of failover_on_backend_error. Using the old config variable name will have no effect.

• Allow to specify the AES encrypted password in the pgpool.conf. (Muhammad Usama)

  Since 4.0, you can specify the AES encrypted password in the pgpool.conf file for health_check_password, sr_check_password, wd_lifecheck_password and recovery_password.

  To specify the unencrypted clear text password, prefix the password string with TEXT. In the absence of a valid prefix, Pgpool-II will consider the string as a plain text password.

  The empty password string specified in the pgpool.conf file for health_check_password, sr_check_password, wd_lifecheck_password and recovery_password will only be used when the pool_passwd does not contain the password for that specific user. If these parameters are left blank, Pgpool-II will first try to get the password for that specific user from pool_passwd file before using the empty password.

A.63.3. Major Enhancements

• Add support for SCRAM and Certificate based authentication methods. (Muhammad Usama)

  • Add support for SCRAM authentication method.

    SCRAM authentication is supported using the pool_passwd authentication file.

    See Section 6.2.4 for more details.

  • Allow to use CERT authentication between Pgpool-II and frontend.

    To use this authentication method, Pgpool-II will require that the client provide a valid certificate.

    See Section 6.2.5 for more details.

  • Able to use different auth methods for frontend and backend.

    Now it is possible to use different authentication methods between client to Pgpool-II and Pgpool-II to backend.

  • Now pool_passwd can store three format passwords. AES256 encrypted format, plain text format and md5 format.

    Pgpool-II identifies the password format type by it's prefix, so each password entry in the pool_passwd must be prefixed as per the password format.

    md5 hashed passwords will be prefixed with md5 and AES256 encrypted password types will be stored using AES prefix. To store the password in the plain text format TEXT prefix can be used.

    In the absence of a valid prefix, Pgpool-II will be considered the string as a plain text password.

    For example:

    	 username1:AESIFwI86k+ZbVdf6C+t3qpGA==
    	 username2:md59c6583185ba6a85bdcd1f129ec8cabb4
    	 username3:TEXTmypassword
    	

  • Able to use MD5 and SCRAM authentication methods to connect to database without pool_passwd.

    A new configuration parameter allow_clear_text_frontend_auth is added. This parameter enables this config allows the Pgpool-II to use clear-text-password authentication with frontend clients when pool_passwd file does not contain the password for the connecting user, and use that password (provided by client) to authenticate with the backend using MD5 and/or SCRAM authentication.

  • New pg_enc utility to create encrypted passwords.

    A new utility pg_enc is added to create AES encrypted passwords.

  See Chapter 6 for more details.

• Add new parameter detach_false_primary. (Tatsuo Ishii)

  If set detach_false_primary = on, detach false primary node. The default is off. This parameter is only valid in streaming replication mode and for PostgreSQL 9.6 or after since this feature uses pg_stat_wal_receiver. If PostgreSQL 9.5.x or older version is used, no error is raised, just the feature is ignored.

• Add disable_load_balance_on_write parameter to specify load balance behavior after write queries appear. (Tatsuo Ishii)

  This parameter allows to specify the behavior when a write query issued.

• Allow to specify load balance weight ratio for load balance parameters. (Bo Peng)

  Add a new feature to allow to specify load balance weight ratio for database_redirect_preference_list and app_name_redirect_preference_list parameters.

  You can specify the list of "database-name:node id(ratio)" pairs to send SELECT queries to a particular backend node for a particular database connection at a specified load balance ratio.

  Also you can specify list of "application-name:node id(ratio)" pairs to send SELECT queries to a particular backend node for a particular client application connection at a specified load balance ratio.

  This load balance ratio specifies a value between 0 and 1, and the default is 1.0.

• Add new parameter primary_routing_query_pattern_list to enable specifying SQL patterns lists that should not be load-balanced. (Bo Peng)

  Specify a semicolon separated list of SQL patterns that should be sent to primary node only. Regular expression can be used in SQL patterns. Only Master Slave mode is supported.

• Add new parameter log_client_messages to allow logging client message. (Takuma Hoshiai, Tatsuo Ishii)

  Set log_client_messages = on, any client messages will be logged without debugging messages.

• Add last_status_change column to SHOW POOL NODES command. (Tatsuo Ishii)

  The new column indicates the time when status or role has been changed.

  See [pgpool-hackers: 2822] for the reasoning to add the column.

• Import PostgreSQL 11's SQL parser. (Bo Peng)

  Now Pgpool-II can fully understand the newly added SQL syntax in PostgreSQL 11, such as CREATE/ALTER/DROP PROCEDURE, { RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ], etc.

A.63.4. Other Enhancements

• Add "-r" option to pgpool_setup to allow use of pg_rewind. (Tatsuo Ishii)

  With this option, pgpool_setup creates basebackup.sh which tries pg_rewind first. If it fails, falls back to rsync.

  Also a new environment variable "USE_PG_REWIND" to pgpool_setup is added. This brings the same effect as "-r" option is specified.

• Add "-s" option to pgpool_setup to support for replication slot. (Tatsuo Ishii)

  This eliminates the problem when standby is promoted. When a standby is promoted, it changes the time line in PITR archive, which will stop other standby if any because of shared archive directory.

  Also a new environment variable "USE_REPLICATION_SLOT" to pgpool_setup is added. This brings the same effect as "-s" option is specified.

  If "USE_REPLICATION_SLOT=true", in streaming replication mode, use replication slot instead of archive.

  By setting USE_REPLICATION_SLOT environment variable, now pgpool_setup in all tests uses replication slots. This reduces disk space under src/test/regression from 6.3GB to 5,1GB (1.2GB savings).

• Introduce pgproto to Pgpool-II. (Takuma Hoshiai)

  A new utility pgproto is added to test PostgreSQL or any other servers that understand the frontend/backend protocol.

• Allow to display Pgpool-II child process id and PostgreSQL backend id in pcp_proc_info. (Tatsuo Ishii)

  Add --all option to display all child processes and their available connection slots.

• Add replication_delay and last_status_change to pcp_node_info. (Tatsuo Ishii)

• Add role, replication_delay and last_status_change columns to pgpool_adm's pgpool_adm_pcp_node_info. (Tatsuo Ishii)

A.63.5. Changes

• Downgrade most of DEBUG1 messages to DEBUG5. (Tatsuo Ishii)

  This significantly reduces the size of pgpool log when pgpool starts with -d option (this is equivalent to setting client_min_messages to debug1).

  Per discussion [pgpool-hackers: 2794].

A.63.6. Bug fixes

• Fix syntax error in native replication, when queries including time functions (now(), etc.) and IN (SELECT ...) in WHERE clause. (bug 433) (Bo Peng)

• Fix compiler error if HAVE_ASPRINTF is not defined. (Tatsuo Ishii)

• Fix configure.ac to remove generating src/sql/pgpool_adm/Makefile.in. (Tatsuo Ishii)

• Fix pgpool main process segfault when PostgreSQL 9.5 is used. (Tatsuo Ishii)

  pgpool_setup -n 3 (or greater) triggers the bug. While recovering node 2, pgpool main process tried to retrieve version info from backend #2 even if it's not running. This causes the segfault because connection was not established yet. The reason why PostgreSQL 9.6 or later was not suffered from the bug was, PostgreSQL exited the loop as soon as the server version is higher than 9.5. To fix this, call to VALID_BACKEND macro was added.

• Add missing health_check_timeout in pgpool_setup. (Tatsuo Ishii)

  Per node health_check_timeout was missing and this should had been there since the per node health check parameter support was added.

• Test: Try to reduce the chance of regression 006.memcache failure. (Tatsuo Ishii)

  It seems the occasional failure of the test is caused by replication lag. The script tries to read tables from standby but it returns a table not existing error. So insert pg_sleep() after creation of tables.

• Test: Fix regression test 055.backend_all_down error. (Bo Peng)

• Doc: Enhance online recovery document. (Tatsuo Ishii)

  Clarify that 2nd stage command is only required in native replication mode.

• Test: Add new regression test 017.node_0_is_down for node 0 not being primary. (Tatsuo Ishii)