[pgpool-general: 7104] pgpool connect with "replication=database" → no PostgreSQL user name specified in startup packet

Axel Richter axel.richter68 at yahoo.com
Fri Jun 26 06:21:54 JST 2020


Hi group,

thanks for the warm welcome, I hope you all are healthy!

After setting up a pgpool→postgres connection to a remote 
server I try to connect:

axel at pgpoolclient:~$ psql -h localhost -p 5432 -U postgres -W
Password:
DEBUG:  pool_write: to frontend: kind:N po:0
DEBUG:  initializing session context
DETAIL:  selected load balancing node: 0
psql (12.3 (Ubuntu 12.3-1.pgdg18.04+1), server 12.2)
Type "help" for help.

postgres=# \q

So far, so good. The goal is to *replicate data* from this 
instance and I try to connect with:

axel at pgpoolclient:~$ psql -h localhost -p 5432 -U postgres -W "replication=database"
Password:
psql: error: could not connect to server: FATAL:*no PostgreSQL user name specified in startup packet*

The steps from the installation and a debug log of pgpool is 
attached. I don't think it's really the missing username, I 
think about an unneccessary test in this case.

Any clue is highly appreciated!

Axel.

-- 
Axel Richter
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20200625/fef5d23c/attachment.html>
-------------- next part --------------
…installed a clean and new Ubuntu server 18.04 LTS

echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | sudo tee  /etc/apt/sources.list.d/pgdg.list
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt update
sudo apt install -y pgpool2
sudo systemctl start pgpool2
sudo systemctl stop pgpool2
sudo vim /etc/pgpool2/pgpool.conf

Changes to /etc/pgpool2/pgpool.conf:
-----------------------------------------

root at pgpoolclient:/etc/pgpool2# diff pgpool.conf.original pgpool.conf
68c68
< backend_hostname0 = 'localhost'
---
> backend_hostname0 = '192.168.74.139'
74c74
< backend_data_directory0 = '/var/lib/pgsql/data'
---
> backend_data_directory0 = '/var/lib/pgsql/12/data'
76c76
< backend_flag0 = 'ALLOW_TO_FAILOVER'
---
> backend_flag0 = 'ALWAYS_MASTER'
91c91
< enable_pool_hba = off
---
> enable_pool_hba = on
367c367
< master_slave_mode = off
---
> master_slave_mode = on
422c422
< health_check_period = 0
---
> health_check_period = 5
428c428
< health_check_user = 'nobody'
---
> health_check_user = 'postgres'
430c430
< health_check_password = ''
---
> health_check_password = 'postgres'
438c438
< health_check_max_retries = 0
---
> health_check_max_retries = 20
498c498
< failover_on_backend_error = on
---
> failover_on_backend_error = off

…after this:

sudo pg_md5 --md5auth --username=postgres postgres
sudo systemctl restart pgpool2
cat /var/log/postgresql/pgpool_status
sudo vi /etc/pgpool2/pgpool.conf
sudo systemctl restart pgpool2
sudo apt install postgresql-client
-------------- next part --------------
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [173-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [173-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  health_check.c:294
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [174-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  pool_flush_it: flush size: 41
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [174-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_stream.c:651
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [175-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  pool_read: read 327 bytes from backend 0
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [175-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_stream.c:201
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [176-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  authenticate kind = 0
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [176-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_auth.c:141
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [177-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  authenticate backend: key data received
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [177-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_auth.c:251
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [178-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  authenticate backend: transaction state: I
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [178-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_auth.c:284
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [179-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [179-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  health_check.c:311
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [180-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [180-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  health_check.c:311
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [181-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  pool_write: to backend: 0 kind:X
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [181-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_stream.c:468
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [182-1] 2020-06-25 16:26:30: pid 3686: DEBUG:  pool_flush_it: flush size: 5
Jun 25 16:26:30 pgpoolclient pgpool[3686]: [182-2] 2020-06-25 16:26:30: pid 3686: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [183-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [183-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  health_check.c:294
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [184-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  pool_flush_it: flush size: 41
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [184-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [185-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  pool_read: read 327 bytes from backend 0
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [185-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_stream.c:201
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [186-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  authenticate kind = 0
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [186-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_auth.c:141
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [187-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  authenticate backend: key data received
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [187-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_auth.c:251
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [188-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  authenticate backend: transaction state: I
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [188-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_auth.c:284
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [189-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [189-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  health_check.c:311
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [190-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  health check: clearing alarm
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [190-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  health_check.c:311
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [191-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  pool_write: to backend: 0 kind:X
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [191-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_stream.c:468
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [192-1] 2020-06-25 16:26:35: pid 3686: DEBUG:  pool_flush_it: flush size: 5
Jun 25 16:26:35 pgpoolclient pgpool[3686]: [192-2] 2020-06-25 16:26:35: pid 3686: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [161-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  connection_count_up: number of connected children: 1
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [161-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:1551
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [162-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  I am 3675 accept fd 8
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [162-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:2186
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [163-1] 2020-06-25 16:26:35: pid 3675: LOG:  new connection received
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [163-2] 2020-06-25 16:26:35: pid 3675: DETAIL:  connecting host=localhost port=58870
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [163-3] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:2199
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [164-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  reading startup packet
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [164-2] 2020-06-25 16:26:35: pid 3675: DETAIL:  Protocol Major: 1234 Minor: 5679 database:  user:
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [164-3] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:711
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [165-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  selecting backend connection
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [165-2] 2020-06-25 16:26:35: pid 3675: DETAIL:  SSLRequest from client
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [165-3] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:2263
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [166-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: kind:N po:0
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [166-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:479
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [167-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_flush_it: flush size: 1
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [167-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [168-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  reading startup packet
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [168-2] 2020-06-25 16:26:35: pid 3675: DETAIL:  application_name: psql
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [168-3] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:669
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [169-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: kind:E po:0
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [169-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:479
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [170-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: length:4 po:1
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [170-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:482
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [171-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: length:91 po:5
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [171-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:482
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [172-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_flush_it: flush size: 96
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [172-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [173-1] 2020-06-25 16:26:35: pid 3675: FATAL:  failed while reading startup packet
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [173-2] 2020-06-25 16:26:35: pid 3675: DETAIL:  no PostgreSQL user name specified in startup packet
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [173-3] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:699
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [174-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: kind:E po:0
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [174-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:479
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [175-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: length:4 po:1
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [175-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:482
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [176-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_write: to frontend: length:119 po:5
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [176-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:482
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [177-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  pool_flush_it: flush size: 124
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [177-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  pool_stream.c:651
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [178-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  connection_count_down: number of connected children: 0
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [178-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  child.c:1578
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [179-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  shmem_exit(1): 0 callbacks to make
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [179-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  elog.c:2612
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [180-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  proc_exit(1): 0 callbacks to make
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [180-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  elog.c:2584
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [181-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  exit(1)
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [181-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  elog.c:2537
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [182-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  shmem_exit(-1): 0 callbacks to make
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [182-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  elog.c:2612
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [183-1] 2020-06-25 16:26:35: pid 3675: DEBUG:  proc_exit(-1): 0 callbacks to make
Jun 25 16:26:35 pgpoolclient pgpool[3675]: [183-2] 2020-06-25 16:26:35: pid 3675: LOCATION:  elog.c:2584
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [163-1] 2020-06-25 16:26:35: pid 3632: DEBUG:  reaper handler
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [163-2] 2020-06-25 16:26:35: pid 3632: LOCATION:  pgpool_main.c:2575
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [164-1] 2020-06-25 16:26:35: pid 3632: LOG:  child process with pid: 3675 exits with status 256
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [164-2] 2020-06-25 16:26:35: pid 3632: LOCATION:  pgpool_main.c:2643
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [165-1] 2020-06-25 16:26:35: pid 3632: LOG:  fork a new child process with pid: 3695
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [165-2] 2020-06-25 16:26:35: pid 3632: LOCATION:  pgpool_main.c:2752
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [166-1] 2020-06-25 16:26:35: pid 3632: DEBUG:  reaper handler: exiting normally
Jun 25 16:26:35 pgpoolclient pgpool[3632]: [166-2] 2020-06-25 16:26:35: pid 3632: LOCATION:  pgpool_main.c:2763
Jun 25 16:26:35 pgpoolclient pgpool[3695]: [165-1] 2020-06-25 16:26:35: pid 3695: DEBUG:  I am Pgpool Child process with pid: 3695
Jun 25 16:26:35 pgpoolclient pgpool[3695]: [165-2] 2020-06-25 16:26:35: pid 3695: LOCATION:  child.c:144
Jun 25 16:26:35 pgpoolclient pgpool[3695]: [166-1] 2020-06-25 16:26:35: pid 3695: DEBUG:  initializing backend status
Jun 25 16:26:35 pgpoolclient pgpool[3695]: [166-2] 2020-06-25 16:26:35: pid 3695: LOCATION:  child.c:1809


More information about the pgpool-general mailing list