A.33. Release 4.2.0

A.33.1. Overview

Many enhancements are added to this version for easier configuration and administration. Moreover new clustering mode which allows not only write consistency but read consistency in multiple PostgreSQL servers. Also SSL is improved to allow more secure operation. New PostgreSQL 13 SQL parse is imported.

Please be warned that in this version some configuration parameters are changed to use more appropriate language. Also some words used in reporting are changed accordingly. See "Migration" section for more details.

Below are major enhancements.

• Some items in the configuration file pgpool.conf are vastly enhanced for easier configuration and administration.

• Implement logging_collector for easier log management.

• Implement log_disconnections to collect disconnection logs.

• Implement pg_enc and pg_md5 to allow to register multiple passwords at once.

• Allow to show statistics of health check by using SHOW POOL_HEALTH_CHECK_STATS command, and also allow to show statistics of issued SQL by using SHOW POOL_BACKEND_STATS command.

• New PCP command pcp_reload_config is added.

• Now it is possible to omit write_function_list and read_only_function_list by looking at system catalog information.

• Add new clustering mode snapshot isolation mode which guarantees not only data modifications to multiple PostgreSQL but read consistency.

• Support LDAP authentication between clients and Pgpool-II.

• Add ssl_crl_file and ssl_passphrase_command to SSL configuration.

• Import PostgreSQL 13's SQL parser.

A.33.2. Migration to Version 4.2

• master_slave_mode, master_slave_sub_mode and replication_mode are deprecated and replaced by backend_clustering_mode. Please change them as follows.

  • master_slave_mode = 'on', master_slave_sub_mode = 'stream' → backend_clustering_mode = 'streaming_replication'

  • master_slave_mode = 'on', master_slave_sub_mode = 'logical' → backend_clustering_mode = 'logical_replication'

  • master_slave_mode = 'on', master_slave_sub_mode = 'slony' → backend_clustering_mode = 'slony'

  • replication_mode = 'on' → backend_clustering_mode = 'native_replication'

  • replication_mode = 'off', master_slave_mode = 'off' → backend_clustering_mode = 'raw'

• Cleanup language. (Umar Hayat)

  Following changes are made in pgpool.conf.

  • black_function_list → write_function_list

  • white_function_list → read_only_function_list

  • black_query_pattern → primary_routing_query_pattern

  • black_memcache_table_list → cache_unsafe_table_list

  • white_memcache_table_list → cache_safe_table_list

  • ALWAYS_MASTER flag → ALWAYS_PRIMARY flag

  • follow_master_command → follow_primary_command

  • Replace relcache_query_target option value 'master' to 'primary'.

  Some words used in reporting are changed.

  • master → main

    slave → replica

  Some parameter names used in scripts are changed.

  • master → main

  Some names used in watchdog are changed.

  • master → leader

• Watchdog parameters below are deprecated.

      - wd_hostname
      - wd_port
      - wd_heartbeat_port
      - heartbeat_device
      - heartbeat_destination0
      - heartbeat_destination_port0
      - heartbeat_destination1
      - heartbeat_destination_port1
      - other_pgpool_hostname0
      - other_pgpool_port0
      - other_wd_port0
      - other_pgpool_hostname1
      - other_pgpool_port1
      - other_wd_port1
       

  Use below parameters instead. See Section 5.14.2 for more details.

       hostname0 = 'server1'
       wd_port0 = 9000
       pgpool_port0 = 9999
      
       hostname1 = 'server2'
       wd_port1 = 9000
       pgpool_port1 = 9999
      
       hostname2 = 'server3'
       wd_port2 = 9000
       pgpool_port2 = 9999
      
       heartbeat_hostname0 = 'server1'
       heartbeat_port0 = 9694
       heartbeat_device0 = ''
      
       heartbeat_hostname1 = 'server2'
       heartbeat_port1 = 9694
       heartbeat_device1 = ''
      
       heartbeat_hostname2 = 'server3'
       heartbeat_port2 = 9694
       heartbeat_device2 = ''
       

  Unlike 4.1 or before, now all watchdog parameters are identical on all hosts. To distinguish which host is which, create a file pgpool_node_id and put number 0, 1, 2 etc. to identify host.

• write_function_list and read_only_function_list are now both empty.

A.33.3. Bug fixes

• In this release same bug fixes as Pgpool-II 4.1.4 are already applied. See Section A.52 for more details of those fixes.

A.33.4. Major Enhancements

• Removing strerror() call from ereports. (Muhammad Usama)

  Call to ereport() resets the errno value and using the "strerror(errno)" emits the wrong error message. The right way is to use %m format specifier instead.

• Unify master_slave_mode, master_slave_sub_mode and replication_mode into backend_clustering_mode. (Tatsuo Ishii)

• Simplify Watchdog related configuration parameters. (Bo Peng)

• Allow units to be specified with configuration settings. (Muhammad Usama)

• Add logging_collector. (Muhammad Usama, Japanese doc by Tatsuo Ishii)

  Import PostgreSQL's logging collector.

• Allow to collect disconnection log by log_disconnections. (Takuma Hoshiai)

• Allow to set application name parameter of log_line_prefix in more cases. (Tatsuo Ishii)

  %a in log_line_prefix was only be set if application_name is specified in startup message. Now it is possible to set application_name if it is set in the connection parameter or SET command.

  Also set hard coded application_name in various internal process so that admins can easily recognize each log line belonging to which process.

• Change pgpool_setup so that log_line_prefix includes application name. (Tatsuo Ishii)

• Add support for an user/password input file to pg_enc. (Umar Hayat, Japanese doc by Tatsuo Ishii)

• Add support for an user/password input file to pg_md5. (Umar Hayat, Japanese doc by Bo Peng)

• Add SHOW POOL_BACKEND_STATS. (Tatsuo Ishii)

  The new command shows the number of SQL commands executed and errors returned from backend since Pgpool-II started.

• Add SHOW POOL_HEALTH_CHECK_STATS to show health check statistics (Tatsuo Ishii)

• Add new PCP command pcp_health_check_stats. (Tatsuo Ishii)

• Add new pgpool_adm function pgpool_adm_pcp_health_check_stats. (Tatsuo Ishii)

• New PCP command pcp_reload_config for reloading the pgpool configuration (Jianshen Zhou, Muhammad Usama, Japanese doc by Tatsuo Ishii)

• Allow to stop whole pgpool cluster by using pcp_stop_pgpool command. (Muhammad Usama, Japanese doc by Tatsuo Ishii)

• Add wd_cli utility (Muhammad Usama, Japanese doc by Tatsuo Ishii)

  wd_cli makes it easier to integrate the external health check systems with the Pgpool-II.

• Implement automatic writing function detection by checking volatile property. (Tatsuo Ishii, Hou, Zhijie)

  If a function included in SELECT/WITH has volatile property by checking system catalog, regard it a writing function. This feature is available only when the write_function_list and the read_only_function_list are both empty. The default of the black_function_list and the white_function_list are now empty string so that this feature is enabled by default.

• Enhance the way getting function names in multiple places. (Hou, Zhijie)

  With query cache enabled, Pgpool-II examines function calls in SELECTs. However if a function were called with schema qualification, it was not recognized. This commit fix to allow schema qualifications in the case.

  Also now schema qualified function names in write_function_list and read_only_function_list are allowed. Note that if you want to register schema qualified function names, you have to register function names without schema qualification as well.

• Add dml object level load balance support in disable_load_balance_on_write .(Subiao, Muhammad Usama)

• Allow to use argument names in pgpool_adm functions. (Hou, Zhijie)

  Example:

       SELECT * FROM pcp_node_count(host => 'localhost', port => 11001, username => 't-ishii', password => 't-ishii');
       

• Add public API to invalidate query cache by table/database oid. (Tatsuo Ishii)

• Add support for LDAP authentication between client and pgpool. (Takuma Hoshiai) See Section 6.2.7 for more details.

• Enhance scram/md5 auth regression test to use AES256 encrypted password. (Tatsuo Ishii)

• Add new backend_clustering_mode "snapshot isolation mode". (Tatsuo Ishii)

  This mode is similar to existing native replication mode except it provides more strict read consistency among backends (so called "atomic visibility") and will give a illusion that a cluster consisting with multiple backends seems to be a single PostgreSQL server.

• Add mention about hostssl/hostnossl to pool_hba.conf sample file. (Tatsuo Ishii)

  Although hostssl/hostnossl are supported, pool_hba.conf comment did not mention about it.

• Add support for ssl_passphrase_command. (Umar Hayat, Japanese doc by Tatsuo Ishii)

• Add support for ssl_crl_file (CRL: Certificate Revocation List). (Umar Hayat, Japanese doc by Tatsuo Ishii)

• Change relative path of SSL files to configuration directory. (Umar Hayat, Japanese doc by Bo Peng)

• Allow to show appropriate process status by ps command with extend queries. (Takuma Hoshiai)

• Downgrade too verbose authentication logs. (Tatsuo Ishii)

• Display more informative error message in authentication process. (Tatsuo Ishii)

• Suppress unnecessary error message when there's no standby server. (Tatsuo Ishii)

• Check if socket file exists at startup and remove them if PID file doesn't exist. (Bo Peng)

• Teach pgproto notification response message. (Tatsuo Ishii)

• Import PostgreSQL 13 beta3 new parser. Major changes of PostgreSQL 13 parser include:

  • Remove an object's dependency on an extension

    	 ALTER TRIGGER ... NO DEPENDS ON EXTENSION ...
    	 ALTER FUNCTION ... NO DEPENDS ON EXTENSION ...
    	

  • Allow FETCH FIRST to use WITH TIES.

    	 FETCH FIRST ... WITH TIES
    	

  • Allow ALTER VIEW to rename view columns

    	 ALTER VIEW ... RENAME COLUMN ... TO ...
    	

  • Add VACUUM clause PARALLEL option

    	 VACUUM (PARALLEL 1) ...
    	

• and watchdog_setup with PostgreSQL's bin path and lib path. (Bo Peng)

• Use pg_config to get PostgreSQL lib path and add this path to LD_LIBRARY_PATH environment parameter in regress.sh. (Bo Peng)

• Make RPMs to include pcp.conf and pool_hba.conf sample files. (Bo Peng)

• Change PCP UNIX_DOMAIN_PATH of RPM package to "/var/run/postgresql". (Bo Peng)

• Rename src/redhat/pgpool_rhel7.sysconfig to src/redhat/pgpool_rhel.sysconfig to make this file available on RHEL8/CentOS8. (Bo Peng)

A.33.5. Major Documentation Enhancements

• Add new documentation "Pgpool-II on Kubernetes". (Bo Peng)

• Add escalation.sh sample script executed by wd_escalation_command. (Masaya Kawamoto)

• Remove "8.2. Watchdog Configuration Example". (Bo Peng)

  See Section 8.2.6.10 of "Pgpool-II + Watchdog Setup Example" for watchdog configuration.

• Add Pgpool-II/PostgreSQL start/stop etc. documents. (Tatsuo Ishii)

  See Section 3.5 etc. for more details.

• Mention that pgpool_setup needs to be configured to log into localhost using ssh without password. (Tatsuo Ishii)

• Change sample scripts and yum repository urls. (Bo Peng)

• Clarify what pcp_promote_node actually does. (Tatsuo Ishii)

• Update "Installation from RPM" section. (Bo Peng)

• Add note about auto_failback. (Takuma Hoshiai)

• Add explanation about .pgpoolkey. (Tatsuo Ishii)

• Improve the description of wd_priority. (Kenichiro Tanaka)

• Add note about if_up_cmd and if_down_cmd command. (Bo Peng)

• Enhance installation documents. (Tatsuo Ishii)

  Add "Planning" section (see Section 2.1) so that users can grasp a big picture of installation.

• Explicitly mention that Pgpool-II needs 3 nodes to avoid split brain. (Tatsuo Ishii)

  See Section for more details.

• Enhance description of failover. (Tatsuo Ishii)

  See failover_command for more details.

• Clarify the session disconnection conditions while failover. (Tatsuo Ishii)

  See failover_command for more details.

• Enhance description of enable_shared_relcache parameter. (Tatsuo Ishii)

• Add sample script links in configuration example. (Bo Peng)

  See Section 8.2 for more details.

• Mention about environment variable PGPOOLKEYFILE in pg_enc command. (Tatsuo Ishii)

  pg_enc command actually recognizes the environment variable.