I managed to configure Pgpool using 3 virtual machines on my pc, but when I repeated the same procedure on a VPS it gives the following error
child pid 5286: ERROR: unable to read message kind child pid 5286: DETAIL: kind does not match between main(53) slot[1] (4e)
I'm using 3 VPS a master on one machine, a slave on a second machine and pgpooll on a third machine
Replication is working normally, I just can't get pgpool to work
From pgpool VPS I can access both databases using psql normally my pgpoll settings
----------------------------
# pgPool-II configuration file
# ----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# Whitespace may be used. Comments are introduced with "#" anywhere on a line.
# The complete list of parameter names and allowed values can be found in the
# pgPool-II documentation.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pgpool reload". Some
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
#------------------------------------------------------------------------------
# BACKEND CLUSTERING MODE
# Choose one of: 'streaming_replication', 'native_replication',
# 'logical_replication', 'slony', 'raw' or 'snapshot_isolation'
# (change requires restart)
#------------------------------------------------------------------------------
backend_clustering_mode = 'streaming_replication'
#------------------------------------------------------------------------------
# CONNECTIONS
#------------------------------------------------------------------------------
# - pgpool Connection Settings -
listen_addresses = '*'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 9999
# Port number
# (change requires restart)
#socket_dir = '/var/run/postgresql'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
#reserved_connections = 0
# Number of reserved connections.
# Pgpool-II does not accept connections if over
# num_init_chidlren - reserved_connections.
# - pgpool Communication Manager Connection Settings -
#pcp_listen_addresses = 'localhost'
# Host name or IP address for pcp process to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
#pcp_port = 9898
# Port number for pcp
# (change requires restart)
#pcp_socket_dir = '/var/run/postgresql'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
#listen_backlog_multiplier = 2
# Set the backlog parameter of listen(2) to
# num_init_children * listen_backlog_multiplier.
# (change requires restart)
#serialize_accept = off
# whether to serialize accept() call to avoid thundering herd problem
# (change requires restart)
# - Backend Connection Settings -
backend_hostname0 = '{my-ip-1}'
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 0
# Weight for backend 0 (only in load balancing mode)
#backend_data_directory0 = '/data'
# Data directory for backend 0
backend_flag0 = 'DISALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_PRIMARY
#backend_application_name0 = 'server0'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '{my-ip-2}'
backend_port1 = 5432
backend_weight1 = 1
#backend_data_directory1 = '/etc/postgresql/15/main'
backend_flag1 = 'DISALLOW_TO_FAILOVER'
#backend_application_name1 = 'server2'
# - Authentication -
#enable_pool_hba = off
# Use pool_hba.conf for client authentication
#pool_passwd = 'pool_passwd'
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
# (change requires restart)
#authentication_timeout = 1min
# Delay in seconds to complete client authentication
# 0 means no timeout.
#allow_clear_text_frontend_auth = off
# Allow Pgpool-II to use clear text password authentication
# with clients, when pool_passwd does not
# contain the user password
# - SSL Connections -
#ssl = off
# Enable SSL support
# (change requires restart)
#ssl_key = 'server.key'
# SSL private key file
# (change requires restart)
#ssl_cert = 'server.crt'
# SSL public certificate file
# (change requires restart)
#ssl_ca_cert = ''
# Single PEM format file containing
# CA root certificate(s)
# (change requires restart)
#ssl_ca_cert_dir = ''
# Directory containing CA root certificate(s)
# (change requires restart)
#ssl_crl_file = ''
# SSL certificate revocation list file
# (change requires restart)
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
# Allowed SSL ciphers
# (change requires restart)
#ssl_prefer_server_ciphers = off
# Use server's SSL cipher preferences,
# rather than the client's
# (change requires restart)
#ssl_ecdh_curve = 'prime256v1'
# Name of the curve to use in ECDH key exchange
#ssl_dh_params_file = ''
# Name of the file containing Diffie-Hellman parameters used
# for so-called ephemeral DH family of SSL cipher.
#ssl_passphrase_command=''
# Sets an external command to be invoked when a passphrase
# for decrypting an SSL file needs to be obtained
# (change requires restart)
#------------------------------------------------------------------------------
# POOLS
#------------------------------------------------------------------------------
# - Concurrent session and pool size -
#num_init_children = 32
# Number of concurrent sessions allowed
# (change requires restart)
#max_pool = 4
# Number of connection pool caches per connection
# (change requires restart)
# - Life time -
#child_life_time = 5min
# Pool exits after being idle for this many seconds
#child_max_connections = 0
# Pool exits after receiving that many connections
# 0 means no exit
#connection_life_time = 0
# Connection to backend closes after being idle for this many seconds
# 0 means no close
#client_idle_limit = 0
# Client is disconnected after being idle for that many seconds
# (even inside an explicit transactions!)
# 0 means no disconnection
Does anyone know what this error means?
Checking the postgres logs I found that the problem is being caused by both systems having different versions of collation
The log message instructs to do a database rebuild, I don't need to do that, I don't know if this might cause any problems in the future.
Before performing the steps here, make a backup of your database.
I solved the problem as follows
My master database was created about 3 months ago, my replication database was created recently, that means even the two servers being ubuntu 22.04 their LTS version are different, and it is not solved by a simple apt upgrade
The collation versions of my systems are different and to update the master collation I have to update the ubuntu LTS version
I used the following command to update ubuntu master
sudo do-release-upgrade
After that I used the following command that I got from the error log itself
REINDEX DATABASE {database_name};
ALTER DATABASE <your-database-name> REFRESH COLLATION VERSION;
Update by @laurenz-albe