about read / write split with pgpool2

[masonlu2014]

hello team,

could you pls teach me how to configure pgpool2 in streaming_replication mode do read/write split ?, right now. my master db pod is do write and read as well which caused a lot work load, i want to offload the read query to my salve db pod, so any document or parameter setting i can use ?

[masonlu2014]

i am using pgpool2 4.2, and i don't see the config "master_slave_mode", i guess it has been removed from the latest version of pgpool ?

[masonlu2014]

here is our pgpool2 config

               item                    |                                value                                |                                                       description

--------------------------------------------+---------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------- backend_hostname0 | pg-prod-0.pg-prod-svc.k8s.io | hostname or IP address of PostgreSQL backend. backend_port0 | 5432 | port number of PostgreSQL backend. backend_weight0 | 1 | load balance weight of backend. backend_data_directory0 | /opt/bitnami/pgpool/data | data directory of the backend. backend_application_name0 | | application_name of the backend. backend_flag0 | ALLOW_TO_FAILOVER | Controls various backend behavior. backend_hostname1 | pg-prod-1.pg-prod-svc.k8s.io | hostname or IP address of PostgreSQL backend. backend_port1 | 5432 | port number of PostgreSQL backend. backend_weight1 | 1 | load balance weight of backend. backend_data_directory1 | /opt/bitnami/pgpool/data | data directory of the backend. backend_application_name1 | | application_name of the backend. backend_flag1 | ALLOW_TO_FAILOVER | Controls various backend behavior. backend_hostname2 | pg-prod-2.pg-prod-svc.k8s.io | hostname or IP address of PostgreSQL backend. backend_port2 | 5432 | port number of PostgreSQL backend. backend_weight2 | 1 | load balance weight of backend. backend_data_directory2 | /opt/bitnami/pgpool/data | data directory of the backend. backend_application_name2 | | application_name of the backend. backend_flag2 | ALLOW_TO_FAILOVER | Controls various backend behavior. backend_hostname3 | pg-prod-3.pg-prod-svc.k8s.io | hostname or IP address of PostgreSQL backend. backend_port3 | 5432 | port number of PostgreSQL backend. backend_weight3 | 1 | load balance weight of backend. backend_data_directory3 | /opt/bitnami/pgpool/data | data directory of the backend. backend_application_name3 | | application_name of the backend. backend_flag3 | ALLOW_TO_FAILOVER | Controls various backend behavior. health_check_period | 30 | Time interval in seconds between the health checks. health_check_timeout | 10 | Backend node health check timeout value in seconds. health_check_user | anchore_repl | User name for PostgreSQL backend health check. health_check_password | | Password for PostgreSQL backend health check database user. health_check_database | postgres | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries | 5 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay | 5 | The amount of time in seconds to wait between failed health check retries. connect_timeout | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period0 | 30 | Time interval in seconds between the health checks. health_check_timeout0 | 10 | Backend node health check timeout value in seconds. health_check_user0 | anchore_repl | User name for PostgreSQL backend health check. health_check_password0 | | Password for PostgreSQL backend health check database user. health_check_database0 | postgres | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries0 | 5 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay0 | 5 | The amount of time in seconds to wait between failed health check retries. connect_timeout0 | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period1 | 30 | Time interval in seconds between the health checks. health_check_timeout1 | 10 | Backend node health check timeout value in seconds. health_check_user1 | anchore_repl | User name for PostgreSQL backend health check. health_check_password1 | | Password for PostgreSQL backend health check database user. health_check_database1 | postgres | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries1 | 5 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay1 | 5 | The amount of time in seconds to wait between failed health check retries. connect_timeout1 | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period2 | 30 | Time interval in seconds between the health checks. health_check_timeout2 | 10 | Backend node health check timeout value in seconds. health_check_user2 | anchore_repl | User name for PostgreSQL backend health check. health_check_password2 | | Password for PostgreSQL backend health check database user. health_check_database2 | postgres | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries2 | 5 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay2 | 5 | The amount of time in seconds to wait between failed health check retries. connect_timeout2 | 10000 | Timeout in milliseconds before giving up connecting to backend. health_check_period3 | 30 | Time interval in seconds between the health checks. health_check_timeout3 | 10 | Backend node health check timeout value in seconds. health_check_user3 | anchore_repl | User name for PostgreSQL backend health check. health_check_password3 | * | Password for PostgreSQL backend health check database user. health_check_database3 | postgres | The database name to be used to perform PostgreSQL backend health check. health_check_max_retries3 | 5 | The maximum number of times to retry a failed health check before giving up and initiating failover. health_check_retry_delay3 | 5 | The amount of time in seconds to wait between failed health check retries. connect_timeout3 | 10000 | Timeout in milliseconds before giving up connecting to backend. allow_multiple_failover_requests_from_node | off | A Pgpool-II node can send multiple failover requests to build consensus. dml_adaptive_object_relationship_list | | list of relationships between objects. failover_if_affected_tuples_mismatch | off | Starts degeneration, If there's a data mismatch between primary and secondary. primary_routing_query_pattern_list | | list of query patterns that should be sent to primary node. app_name_redirect_preference_list | | redirect by application name. memqcache_auto_cache_invalidation | on | Automatically deletes the cache related to the updated tables. cache_unsafe_memqcache_table_list | | list of tables should not be cached. database_redirect_preference_list | | redirect by database name. enable_consensus_with_half_votes | off | apply majority rule for consensus and quorum computation at 50% of votes in a cluster with an even number of nodes. wd_no_show_node_removal_timeout | 0 | Timeout in seconds to revoke the cluster membership of NO-SHOW watchdog nodes. cache_safe_memqcache_table_list | | list of tables to be cached. allow_clear_text_frontend_auth | off | allow to use clear text password authentication with clients, when pool_passwd does not contain the user password. clear_memqcache_on_escalation | on | Clears the query cache in the shared memory when pgpool-II escalates to leader watchdog node. client_idle_limit_in_recovery | 0 | Time limit is seconds for the child connection, before it is terminated during the 2nd stage recovery. disable_load_balance_on_write | transaction | Load balance behavior when write query is received. wd_monitoring_interfaces_list | | List of network device names, to be monitored by the watchdog process for the network link state. statement_level_load_balance | off | Enables statement level load balancing failover_on_backend_shutdown | on | Triggers fail over when backend is shutdown. wd_lost_node_removal_timeout | 0 | Timeout in seconds to revoke the cluster membership of LOST watchdog nodes. replication_stop_on_mismatch | off | Starts degeneration and stops replication, If there's a data mismatch between primary and secondary. failover_when_quorum_exists | on | Do failover only when cluster has the quorum. search_primary_node_timeout | 0 | Max time in seconds to search for primary node after failover. recovery_1st_stage_command | | Command to execute in first stage recovery. recovery_2nd_stage_command | | Command to execute in second stage recovery. ignore_leading_white_space | on | Ignores leading white spaces of each query string. memqcache_cache_block_size | 2MB | Cache block size in bytes. prefer_lower_delay_standby | off | If the load balance node is delayed over delay_threshold on SR, pgpool find another standby node which is lower delayed. failover_require_consensus | on | Only do failover when majority aggrees. ssl_prefer_server_ciphers | off | Use server's SSL cipher preferences, rather than the client's failover_on_backend_error | off | Triggers fail over when reading/writing to backend socket fails. listen_backlog_multiplier | 2 | length of connection queue from frontend to pgpool-II log_truncate_on_rotation | off | If on, an existing log file gets truncated on time based log rotation. wd_remove_shutdown_nodes | off | Revoke the cluster membership of properly shutdown watchdog nodes. memqcache_memcached_port | 11211 | Port number of Memcached server. wd_de_escalation_command | | Command to execute when watchdog node resigns from the cluster leader node. memqcache_memcached_host | localhost | Hostname or IP address of memcached. memqcache_max_num_cache | 5000000 | Total number of cache entries. read_only_function_list | | list of functions that does not writes to database. backend_clustering_mode | streaming_replication | backend clustering mode. auto_failback_interval | 12s | min interval of executing auto_failback in seconds wd_heartbeat_keepalive | 2s | Time interval in seconds between sending the heartbeat signal. ssl_passphrase_command | | Path to the Diffie-Hellman parameters contained file log_per_node_statement | off | Logs per node detailed SQL statements. authentication_timeout | 30s | Time out value in seconds for client authentication. follow_primary_command | | Command to execute in streaming replication mode after a primary node failover. enable_shared_relcache | on | relation cache stored in memory cache. relcache_query_target | primary | Target node to send relache queries. wd_heartbeat_deadtime | 30s | Deadtime interval in seconds for heartbeat signal. wd_escalation_command | | Command to execute when watchdog node becomes cluster leader node. child_max_connections | 0 | A pgpool-II child process will be terminated after this many connections from clients. wd_lifecheck_password | * | Password for watchdog user in lifecheck. check_unlogged_table | on | Enables unlogged table check. memqcache_total_size | 2GB | Total memory size in bytes for storing memory cache. reserved_connections | 1 | Number of reserved connections. memory_cache_enabled | off | Enables the memory cache functionality. connection_life_time | 5min | Cached connections expiration time in seconds. pcp_listen_addresses | localhost | hostname(s) or IP address(es) on which pcp will listen on. detach_false_primary | on | Automatically detaches false primary node. write_function_list | | list of functions that writes to database. client_min_messages | notice | Which messages should be sent to client. wd_lifecheck_method | heartbeat | method for watchdog lifecheck. log_client_messages | off | Logs any client messages in the pgpool logs. wd_lifecheck_dbname | template1 | Database name to be used for by watchdog lifecheck. log_error_verbosity | default | How much details about error should be emitted. wd_lifecheck_query | SELECT 1 | SQL query to be used by watchdog lifecheck. log_disconnections | off | Logs end of a session. allow_sql_comments | off | Ignore SQL comments, while judging if load balance or query cache is possible. ssl_dh_params_file | | Path to the Diffie-Hellman parameters contained file memqcache_maxcache | 400kB | Maximum SELECT result size in bytes. log_rotation_size | 10MB | Automatic rotation of logfiles will happen after that much (kilobytes) log output. recovery_password | | Password for online recovery. client_idle_limit | 0 | idle time in seconds to disconnects a client. health_check_test | off | If on, enable health check testing. num_init_children | 200 | Number of children pre-forked for client connections. log_standby_delay | if_over_threshold | When to log standby delay. logging_collector | off | Enable capturing of stderr into log files. load_balance_mode | on | Enables load balancing of queries. wd_ipc_socket_dir | /tmp | The directory to create the UNIX domain socket for accepting pgpool-II watchdog IPC connections. wd_lifecheck_user | nobody | User name to be used for by watchdog lifecheck. sr_check_password | | The password for user to perform streaming replication delay check. sr_check_database | anchore_repl_db | The database name to perform streaming replication delay check. replicate_select | off | Replicate SELECT statements when load balancing is disabled. memqcache_oiddir | /var/log/pgpool/oiddir | Temporary directory to record table oids. recovery_timeout | 90s | Maximum time in seconds to wait for the recovering PostgreSQL node. listen_addresses | * | hostname or IP address on which pgpool will listen on. replication_mode | off | Enables replication mode. log_rotation_age | 1d | Automatic rotation of logfiles will happen after that (minutes) time. log_min_messages | warning | Which messages should be emitted to server log. connection_cache | on | Caches connections to backends. serialize_accept | off | whether to serialize accept() call to avoid thundering herd problem memqcache_method | shmem | Cache store method. either shmem(shared memory) or Memcached. shmem by default. failback_command | | Command to execute when backend node is attached. memqcache_expire | 0 | Memory cache entry life time specified in seconds. check_temp_table | catalog | Enables temporary table check. reset_query_list | ABORT;DISCARD ALL | list of commands sent to reset the backend connection when user session exits. failover_command | echo ">>> Failover - that will initialize new primary node search!" | Command to execute when backend node is detached. sr_check_period | 30s | Time interval in seconds between the streaming replication delay checks. ssl_ca_cert_dir | | Directory containing CA root certificate(s). log_destination | stderr | destination of pgpool-II log log_line_prefix | %m: %a pid %p: | printf-style string to output at beginning of each log line. lobj_lock_table | | Table name used for large object replication control. log_connections | off | Logs each successful connection. child_life_time | 1h | pgpool-II child process life time in seconds. syslog_facility | LOCAL0 | syslog local facility. trusted_servers | | List of servers to verify connectivity. enable_pool_hba | on | Use pool_hba.conf for client authentication. delay_threshold | 0 | standby delay threshold in bytes. relcache_expire | 0 | Relation cache expiration time in seconds. pcp_socket_dir | /opt/bitnami/pgpool/tmp | The directory to create the UNIX domain socket for accepting pgpool-II PCP connections. ssl_ecdh_curve | prime256v1 | The curve to use in ECDH key exchange. recovery_user | | User name for online recovery. auto_failback | on | Enables nodes automatically reattach, when detached node continue streaming replication. log_directory | /tmp/pgpool_logs | directory where log files are written. sr_check_user | anchore_repl | The User name to perform streaming replication delay check. log_statement | off | Logs all statements in the pgpool logs. relcache_size | 256 | Number of relation cache entry. log_file_mode | 384 | creation mode for log files. wd_life_point | 3 | Maximum number of retries before failing the life check. pid_file_name | /opt/bitnami/pgpool/tmp/pgpool.pid | Path to store pgpool-II pid file. ssl_crl_file | | SSL certificate revocation list file use_watchdog | off | Enables the pgpool-II watchdog. log_hostname | on | Logs the host name in the connection logs. logfilename | pgpool-%Y-%m-%d%H%M%S.log | log file name pattern. syslog_ident | pgpool | syslog program ident string. wd_priority | 1 | Watchdog node priority for leader election. wd_interval | 10s | Time interval in seconds between life check. insert_lock | on | Automatically locks table with INSERT to keep SERIAL data consistency ssl_ca_cert | | Single PEM format file containing CA root certificate(s). delegate_IP | | Delegate IP address to be used when pgpool node become a watchdog cluster leader. if_cmd_path | /sbin | Path to interface command. ssl_ciphers | HIGH:MEDIUM:+3DES:!aNULL | Allowed SSL ciphers. if_down_cmd | /usr/bin/sudo /sbin/ip addr del $IP$/24 dev eth0 | Complete command to bring down virtual interface. arping_path | /usr/sbin | path to arping command. pool_passwd | ***** | File name of pool_passwd for md5 authentication. socket_dir | /opt/bitnami/pgpool/tmp | The directory to create the UNIX domain socket for accepting pgpool-II client connections. arping_cmd | /usr/bin/sudo /usr/sbin/arping -U $IP$ -w 1 -I eth0 | arping command. wd_authkey | | Authentication key to be used in watchdog communication. ping_path | /bin | path to ping command. if_up_cmd | /usr/bin/sudo /sbin/ip addr add $IP$/24 dev eth0 label eth0:0 | Complete command to bring UP virtual interface. max_pool | 4 | Maximum number of connection pools per child process. pcp_port | 9898 | tcp/IP port number on which pgpool PCP process will listen on. ssl_cert | | SSL public certificate file. ssl_key | | SSL private key file. logdir | /opt/bitnami/pgpool/logs | PgPool status file logging directory. port | 5432 | tcp/IP port number on which pgpool will listen on. ssl | off | Enables SSL support for frontend and backend connections (210 rows)

[masonlu2014]

got the answer from the blog, close the issue now