[pgpool-general: 6542] Almost there with pgpool, but it's not accepting all necessary connections
Rob Reinhardt
rreinhardt at eitccorp.com
Sat May 4 03:54:47 JST 2019
3-node pg 9.5 streaming replica cluster with repmgr is up and working...
1-node pgpool 4.0.4 is up and running, can't say totally working yet
because some access attempts fail. but it sees the cluster and some
connection attempts succeed. (only as pgpool user on the localhost of
pgpool, (of course all connections local on the primary postgres server
works as they always have, and all remote client connections as postgres to
any of the cluster servers still work--but not to the pgpool server)
-bash-4.2$ psql -U pgpool --dbname=pgpool --host localhost -c "show
pool_nodes"
node_id | hostname | port | status | lb_weight | role | select_cnt
| load_balance_node | replication_delay | last_status_change
---------+---------------+------+--------+-----------+---------+------------+-------------------+-------------------+---------------------
0 | 192.168.95.15 | 5432 | up | 0.333333 | primary | 2
| false | 0 | 2019-05-03 15:00:34
1 | 192.168.95.14 | 5432 | up | 0.333333 | standby | 0
| true | 0 | 2019-05-03 15:00:34
2 | 192.168.95.13 | 5432 | up | 0.333333 | standby | 0
| false | 0 | 2019-05-03 15:00:34
(3 rows)
and, this works:
psql -h localhost -p 5432 -U pgpool
It drops me right into the database, from which I can \l or whatever and
see all my prod db's on the cluster, from there I can \c and attach to a
live prod db and list tables, whatever, so with this I'm in.
1) but connecting as postgres fails, changing this to use the real hostname
instead of localhost it fails for -both- pgpool and postgres:
-bash-4.2$ psql -h localhost -p 5432 -U postgres
psql: ERROR: authentication failed
DETAIL: password authentication failed for user "postgres"
2) and if I try connection from a remote (client) host it fails at the hba:
psql -h r01sv02 -p 5432 -U postgres
psql: FATAL: client authentication failed
DETAIL: no pool_hba.conf entry for host "192.168.95.10", user
"postgres", database "postgres", SSL off
HINT: see pgpool log for details
3) And running any of the pcp_* utilities from the command line fails:
-bash-4.2$ pcp_pool_status -v -p 9898 -h /var/run/pgpool -U pgpool
Password:
FATAL: authentication failed for user "pgpool"
DETAIL: username and/or password does not match
first, it prompts me for password which I thought it would not since the
.pcppass file exists, but in any case when I provide it I'm told it failed.
-bash-4.2$ ls -lah /var/run/pgpool/.s.*
srwxrwxrwx. 1 postgres postgres 0 May 3 15:00 /var/run/pgpool/.s.PGSQL.5432
srwxrwxrwx. 1 postgres postgres 0 May 3 15:00 /var/run/pgpool/.s.PGSQL.9898
If you can, I need help figuring out why the pool_hba.conf seems to be
claiming a host is not listed which Is in fact listed as part of a subnet
md5 entry. -as well- get complaints about passwords failing when the
password is the same everywhere in this build right now, postgres, repmgr,
pgpool all using the same postgres password set in the original production
standalone postgres build.
Here are relevant config files:
---------------------------------------
All three cluster members plus the one pgpool server have this same file in
the postgres user home_dir
-rw-------. 1 postgres postgres 328 May 3 14:27 .pgpass
localhost:5432:*:postgres:secret
r01sv04:5432:replication:repmgr:secret
r01sv03:5432:*:postgres:secret
r01sv05:5432:replication:repmgr:secret
r01sv05:5432:*:postgres:secret
r01sv04:5432:*:postgres:secret
r01sv03:5432:replication:repmgr:secret
r01sv02:5432:*:pgpool:secret
additionally, the pgpool server has the required .pcppass and pool_passwd
files which look like this:
in postgres $homedir:
-rw-------. 1 postgres postgres 52 May 3 18:10 .pcppass
*:*:postgres:secret
*:*:pgpool:secret
in postgres $homedir/pgpool:
-rw-------. 1 postgres postgres 82 Apr 25 19:19 pcp.conf
postgres:pg_md5-hash-of-secret-same-in all-entries
pgpool:pg_md5-hash-of-secret-same-in all-entries
rw-------. 1 postgres postgres 82 Apr 25 19:19 pool_passwd
postgres:pg_md5-hash-of-secret-same-in all-entries
pgpool:pg_md5-hash-of-secret-same-in all-entries
postgres $homedir/pgpool/pool_hba.conf
-----------------------
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 192.168.95.0/24 md5 *<---this
is my whole subnet*
my pg_hba.conf
----------------------
# TYPE DATABASE USER ADDRESS METHOD
# the cluster trusts need to be at the top of the file
host all pgpool 192.168.95.12/32 trust
host repmgr repmgr 192.168.95.15/32 trust
host replication repmgr 192.168.95.15/32 trust
host repmgr repmgr 192.168.95.14/32 trust
host replication repmgr 192.168.95.14/32 trust
host repmgr repmgr 192.168.95.13/32 trust
host replication repmgr 192.168.95.13/32 trust
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
#Application Connections
host all all 192.168.95.0/24
md5 *<---this is my whole subnet (and this works for postgres, just
the other one doesn't seem to work for pgpool)*
pgpool.conf entries: (you can ignored the watchdog and related stuff
because we're not using it)
----------------------------------------
listen_addresses = '0.0.0.0'
port = '5432'
socket_dir = '/var/run/pgpool'
pcp_listen_addresses = '0.0.0.0'
pcp_port = '9898'
pcp_socket_dir = '/var/run/pgpool'
listen_backlog_multiplier = '2'
serialize_accept = 'off'
backend_hostname0 = '192.168.95.15'
backend_port0 = '5432'
backend_weight0 = 1
backend_data_directory0 = '/var/lib/pgsql/9.5/data'
backend_flag0 = 'ALLOW_TO_FAILOVER'
backend_hostname1 = '192.168.95.14'
backend_port1 = '5432'
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/9.5/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_hostname2 = '192.168.95.13'
backend_port2 = '5432'
backend_weight2 = 1
backend_data_directory2 = '/var/lib/pgsql/9.5/data'
backend_flag2 = 'ALLOW_TO_FAILOVER'
enable_pool_hba = on
pool_passwd = 'pool_passwd'
authentication_timeout = 60
allow_clear_text_frontend_auth = off
ssl = off
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
ssl_prefer_server_ciphers = off
num_init_children = 32
max_pool = 4
child_life_time = 300
child_max_connections = 0
connection_life_time = 0
client_idle_limit = 0
log_destination = 'stderr,syslog'
log_line_prefix = '%t: pid %p: '
log_connections = 'off'
log_hostname = 'on'
log_statement = 'off'
log_per_node_statement = 'off'
log_client_messages = 'off'
log_standby_delay = 'if_over_threshold'
syslog_facility = 'LOCAL0'
syslog_ident = 'pgpool'
log_error_verbosity = 'default'
client_min_messages = 'notice'
log_min_messages = 'warning'
pid_file_name = '/var/run/pgpool/pgpool.pid'
logdir = '/var/log/postgres'
connection_cache = 'on'
reset_query_list = 'ABORT; DISCARD ALL'
replication_mode = off
replicate_select = off
insert_lock = off
lobj_lock_table = ''
replication_stop_on_mismatch = off
failover_if_affected_tuples_mismatch = off
load_balance_mode = 'on'
ignore_leading_white_space = 'on'
white_function_list = ''
black_function_list = 'currval,lastval,nextval,setval'
black_query_pattern_list = ''
database_redirect_preference_list = ''
app_name_redirect_preference_list = ''
allow_sql_comments = 'off'
disable_load_balance_on_write = 'transaction'
master_slave_mode = 'on'
master_slave_sub_mode = 'stream'
sr_check_period = '10'
sr_check_user = 'pgpool'
sr_check_password = 'secret'
sr_check_database = 'pgpool'
delay_threshold = '10000000'
follow_master_command = ''
health_check_period = '10'
health_check_timeout = '20'
health_check_user = 'pgpool'
health_check_password = 'secret'
health_check_database = 'pgpool'
health_check_max_retries = '0'
health_check_retry_delay = '1'
connect_timeout = '10000'
failover_command = '/var/lib/pgsql/pgpool/bin/failover.sh %d %h %p %D %m %H
%M %P %r %R %%'
failover_on_backend_error = 'on'
detach_false_primary = 'off'
search_primary_node_timeout = '300'
recovery_user = ''
recovery_password = ''
recovery_1st_stage_command = ''
recovery_2nd_stage_command = ''
recovery_timeout = 90
client_idle_limit_in_recovery = 0
use_watchdog = off
trusted_servers = ''
ping_path = '/bin'
wd_hostname = ''
wd_port = 9000
wd_priority = 1
wd_authkey = ''
wd_ipc_socket_dir = '/var/run/pgpool'
delegate_IP = ''
if_cmd_path = '/sbin'
if_up_cmd = 'ip addr add $_IP_$/24 dev eth0 label eth0:0'
if_down_cmd = 'ip addr del $_IP_$/24 dev eth0'
arping_path = '/usr/sbin'
arping_cmd = 'arping -U $_IP_$ -w 1'
clear_memqcache_on_escalation = 'off'
wd_escalation_command = ''
wd_de_escalation_command = ''
failover_when_quorum_exists = on
failover_require_consensus = on
allow_multiple_failover_requests_from_node = off
wd_lifecheck_method = 'heartbeat'
wd_interval = 10
wd_heartbeat_port = 9694
wd_heartbeat_keepalive = 2
wd_heartbeat_deadtime = 30
heartbeat_destination0 = 'host0_ip1'
heartbeat_destination_port0 = 9694
heartbeat_device0 = ''
wd_life_point = 3
wd_lifecheck_query = 'SELECT 1'
wd_lifecheck_dbname = 'template1'
wd_lifecheck_user = 'pgpool'
wd_lifecheck_password = 'secret'
relcache_expire = '0'
relcache_size = '256'
check_temp_table = 'on'
check_unlogged_table = 'on'
memory_cache_enabled = 'on'
memqcache_method = 'shmem'
memqcache_memcached_host = 'localhost'
memqcache_memcached_port = '11211'
memqcache_total_size = '67108864'
memqcache_max_num_cache = '1000000'
memqcache_expire = '0'
memqcache_auto_cache_invalidation = 'on'
memqcache_maxcache = '409600'
memqcache_cache_block_size = '1048576'
memqcache_oiddir = '/var/log/postgres/oiddir'
white_memqcache_table_list = ''
black_memqcache_table_list = ''
Thanks,
Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20190503/060a12b0/attachment-0001.html>
More information about the pgpool-general
mailing list