I'm tring to setup a simple master/slave replication between two PostgreSQL instances, each running in a virtual machine in two different hosts.
Both virtual machines have a NAT configuration, with the following port forwarding rules:
PROTO HOST GUEST
TCP 40022 22
TCP 45432 5432
TCP 9999 9999
I can connect to both PostgreSQL instances just fine using PGAdmin III, both from the host and guest OS. The pgpool2 service also starts without errors, but I can't connect to it using either pgAdmin or psql.
This is what happens when I try to connect to pgpool using psql:
psql -p 9999 -U postgres -h localhost dbname
psql: server closed the connection unexpectedly
This probably means the server terminated abnormally before or while processing the request.
Both backends are defined in pgpool.conf:
backend_hostname0 = 'localhost'
backend_port0 = '5432'
backend_data_directory0 = '/var/lib/postgresql/9.3/main'
backend_weight0 = 1
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.7.25'
backend_port1 = '45432'
backend_data_directory1 = '/var/lib/postgresql/9.3/main'
backend_weight1 = 1
backend_flag1 = 'ALLOW_TO_FAILOVER'
In the same file, both replication_mode and load_balance_mode are set to true.
I believe there might be some configuration issue with pgpool because, when ran manually with debug flags enabled, I can see the backends are being picked:
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_hostname0
2015-07-14 15:10:45 DEBUG: pid 4386: value: 'localhost' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_port0
2015-07-14 15:10:45 DEBUG: pid 4386: value: '5432' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: port slot number 0
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_data_directory0
2015-07-14 15:10:45 DEBUG: pid 4386: value: '/var/lib/postgresql/9.3/main' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_weight0
2015-07-14 15:10:45 DEBUG: pid 4386: value: 1 kind: 2
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: weight slot number 0 weight: 1.000000
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_flag0
2015-07-14 15:10:45 DEBUG: pid 4386: value: 'ALLOW_TO_FAILOVER' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: extract_string_tokens: token: ALLOW_TO_FAILOVER
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: allow_to_failover on
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: slot number 0 flag: 0000
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_hostname1
2015-07-14 15:10:45 DEBUG: pid 4386: value: '192.168.7.25' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_port1
2015-07-14 15:10:45 DEBUG: pid 4386: value: '45432' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: port slot number 1
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_data_directory1
2015-07-14 15:10:45 DEBUG: pid 4386: value: '/var/lib/postgresql/9.3/main' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_weight1
2015-07-14 15:10:45 DEBUG: pid 4386: value: 1 kind: 2
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: weight slot number 1 weight: 1.000000
2015-07-14 15:10:45 DEBUG: pid 4386: key: backend_flag1
2015-07-14 15:10:45 DEBUG: pid 4386: value: 'ALLOW_TO_FAILOVER' kind: 4
2015-07-14 15:10:45 DEBUG: pid 4386: extract_string_tokens: token: ALLOW_TO_FAILOVER
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: allow_to_failover on
2015-07-14 15:10:45 DEBUG: pid 4386: pool_config: slot number 1 flag: 0000
However, further down the log, the following line is found:
2015-07-14 15:10:45 DEBUG: pid 4386: num_backends: 0 total_weight: 0.000000
Am I doing something wrong here? I've followed several tutorials on pgpool and I'm mostly going on with default settings, but I still can't get it to run. Any help is appreciated.
This most likely was an issue with Ubuntu's respository version of pgpool2 and postgresql not working correctly.
After raising the log level for more details and checking what was happening when I tried to connect, I found this message:
Cannot accept() new connection. all backends are down.
Searching for this error message on google brought me to this link.
I decided to take the same approach, downloaded and built pgpool from source. After a few tweaks, being forced to use pool_hba.conf instead of relying only on pg_hba.conf for access authorization, I got it working as expected.
Edit: typo.