Docker部署PostgreSQL无法被DBeaver连接及Adminer访问异常问题

环境信息 #

• Docker Desktop 4.21.1 (114176)

docker-compose配置 #

# Use postgres/example user/password credentials
version: 'latest'

services:

  db:
    image: postgres
    restart: always
    environment:
      POSTGRES_USER: dbadmin
      POSTGRES_PASSWORD: mypassword
      POSTGRES_DB: testdb       
    volumes:
       - /Volumes/Development/directories/workspace/postgres/data:/var/lib/postgresql/data

  adminer:
    image: adminer
    restart: always
    ports:
      - 9080:9080

容器启动日志 #

postgres % docker-compose up
[+] Running 2/0
 ✔ Container postgres-db-1       Created                                                                                                                                                 0.0s 
 ✔ Container postgres-adminer-1  Created                                                                                                                                                 0.0s 
Attaching to postgres-adminer-1, postgres-db-1
postgres-adminer-1  | [Mon Aug 14 15:07:53 2023] PHP 7.4.33 Development Server (http://[::]:8080) started
postgres-db-1       | 
postgres-db-1       | PostgreSQL Database directory appears to contain a database; Skipping initialization
postgres-db-1       | 
postgres-db-1       | 
postgres-db-1       | 2023-08-14 15:07:53.174 UTC [1] LOG:  starting PostgreSQL 15.4 (Debian 15.4-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
postgres-db-1       | 2023-08-14 15:07:53.174 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgres-db-1       | 2023-08-14 15:07:53.174 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgres-db-1       | 2023-08-14 15:07:53.177 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgres-db-1       | 2023-08-14 15:07:53.194 UTC [30] LOG:  database system was shut down at 2023-08-14 15:07:29 UTC
postgres-db-1       | 2023-08-14 15:07:53.205 UTC [1] LOG:  database system is ready to accept connections
postgres-db-1       | 2023-08-14 15:12:53.200 UTC [28] LOG:  checkpoint starting: time
postgres-db-1       | 2023-08-14 15:12:53.250 UTC [28] LOG:  checkpoint complete: wrote 3 buffers (0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.011 s, sync=0.012 s, total=0.051 s; sync files=2, longest=0.010 s, average=0.006 s; distance=0 kB, estimate=0 kB

容器内操作情况 #

p950ojb@MOSF2N57RL7VD postgres % docker ps                        
CONTAINER ID   IMAGE      COMMAND                  CREATED          STATUS          PORTS                              NAMES
a4de0010b9e3   postgres   "docker-entrypoint.s…"   33 minutes ago   Up 34 seconds   5432/tcp                           postgres-db-1
c7a25633ca1f   adminer    "entrypoint.sh php -…"   37 minutes ago   Up 34 seconds   8080/tcp, 0.0.0.0:9080->9080/tcp   postgres-adminer-1
p950ojb@MOSF2N57RL7VD postgres % 
p950ojb@MOSF2N57RL7VD postgres % docker exec -it a4de0010b9e3 bash
root@a4de0010b9e3:/# 
root@a4de0010b9e3:/# psql -d testdb -U dbadmin
psql (15.4 (Debian 15.4-1.pgdg120+1))
Type "help" for help.

testdb=# select * from accounts;
 id | username 
----+----------
  1 | rick
  2 | morty
(2 rows)

遇到的问题 #

• DBeaver测试连接PostgreSQL时被拒绝
• 访问http://localhost:9080无法打开Adminer页面
• 容器内执行psql命令未提示输入密码

解决方案 #

问题1：DBeaver连接被拒绝 #

PostgreSQL容器未对外暴露5432端口，外部工具无法访问。修改docker-compose.yml中db服务的ports配置，添加端口映射：

db:
  # 其他配置保持不变
  ports:
    - "5432:5432"

修改后重启容器：docker-compose down && docker-compose up -d

问题2：Adminer页面无法访问 #

从启动日志可知，Adminer容器内部监听的是8080端口，但docker-compose中映射的是9080:9080，端口不匹配。修改ports配置为：

adminer:
  # 其他配置保持不变
  ports:
    - "9080:8080"

重启容器后访问http://localhost:9080即可打开Adminer页面。

问题3：容器内psql无需输入密码 #

PostgreSQL默认信任本地Unix socket连接，容器内以root用户执行psql时，会自动使用peer认证（匹配操作系统用户名），因此无需输入密码。如果需要强制输入密码，可修改PostgreSQL的pg_hba.conf文件：

1. 进入postgres容器：docker exec -it postgres-db-1 bash
2. 编辑pg_hba.conf：vi /var/lib/postgresql/data/pg_hba.conf
3. 将本地连接的认证方式从peer改为md5：

   # 找到该行
   local   all             all                                     peer
   # 修改为
   local   all             all                                     md5
   

4. 重启PostgreSQL服务：pg_ctl restart -D /var/lib/postgresql/data
   之后在容器内执行psql就会提示输入密码。

内容的提问来源于stack exchange，提问作者Kaliyug Antagonist