无法从Intellij连接到我的Docker中的Postgres数据库

Question

我正在按照以下链接中描述的相同步骤进行操作：https://medium.com/better-programming/connect-from-local-machine-to-postgresql-docker-container-f785f00461a7，但是当我尝试使用密码mysecretpassword从Intellij连接到我的PostgreSQL时，出现以下错误：

The specified database user/password combination is rejected: [28P01] FATAL: password authentication failed for user "postgres"

当然，我可以从cmd命令连接到我的数据库：

$ psql -h localhost -p 5432 -U postgres -W                       Password for user postgres:                       
psql (9.5.5, server 10.3 (Debian 10.3-1.pgdg90+1))                       WARNING: psql major version 9.5, server major version 10.                                Some psql features might not work.                       
Type "help" for help.                                               postgres=# \l

我的容器已经启动：

发生了什么？我不知道……我在Intellij中使用的是42.2.5版本的postgres驱动程序。

容器的日志：

The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

waiting for server to start....2020-09-30 11:17:42.613 UTC [45] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-09-30 11:17:42.618 UTC [45] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-09-30 11:17:42.642 UTC [46] LOG:  database system was shut down at 2020-09-30 11:17:42 UTC
2020-09-30 11:17:42.649 UTC [45] LOG:  database system is ready to accept connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*

2020-09-30 11:17:42.700 UTC [45] LOG:  received fast shutdown request
waiting for server to shut down....2020-09-30 11:17:42.705 UTC [45] LOG:  aborting any active transactions
2020-09-30 11:17:42.708 UTC [45] LOG:  background worker "logical replication launcher" (PID 52) exited with exit code 1
2020-09-30 11:17:42.708 UTC [47] LOG:  shutting down
2020-09-30 11:17:42.737 UTC [45] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2020-09-30 11:17:42.836 UTC [1] LOG:  starting PostgreSQL 12.2 (Debian 12.2-2.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
2020-09-30 11:17:42.836 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2020-09-30 11:17:42.836 UTC [1] LOG:  listening on IPv6 address "::", port 5432
2020-09-30 11:17:42.845 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2020-09-30 11:17:42.881 UTC [54] LOG:  database system was shut down at 2020-09-30 11:17:42 UTC
2020-09-30 11:17:42.888 UTC [1] LOG:  database system is ready to accept connections

- Matley

---

在我的Docker上的Postgres中，我应该如何处理pg_hba.conf文件？ - Matley

不，我甚至在Intellij中更改了设置，以确保不存储任何密码...以确保没有错误的密码存储在Intellij中...这太疯狂了。 - Matley

下一步是：你的容器中的Postgres日志告诉了你什么？我不会在这里继续聊天，因为这不是本站的精神。你需要获取更多的调试信息，然后编辑你的问题并将它们添加到那里。 - Zeitounator

也许我应该处理一下：initdb: warning: enabling "trust" authentication for local connections。您可以通过编辑pg_hba.conf文件或使用-A选项来更改此设置。 - Matley

我在5433端口创建了新的数据库，现在它可以工作了，我可以通过Intellij连接到数据库... 但是我本地没有任何容器... - Matley

Answer 1

为卷创建目录：C:\Users{user}\docker\postgres_data\data

在 Windows 上连接到 Postgres：

docker run -p 5432:5432 --name postgres -v C:\Users\{user}\docker\postgres_data\data:/var/lib/postgresql/data -e POSTGRES_PASSWORD=root -d postgres:11.3 

从CMD连接到Postgres并创建数据库（仅在首次在空卷上创建容器后）：

使用CMD连接到Postgres并在空卷上创建容器后，才能创建数据库。

docker ps
  

获取输出：
容器ID 镜像 命令 创建时间 状态 端口 名称 e626f4c7b958 postgres:11.3 "docker-entrypoint.s…" 大约一个小时前 运行中 0.0.0.0:5432->5432/tcp postgres

运行：

docker exec -it {CONTAINER ID} bash
psql -h localhost -U postgres
psql -U postgres

使用密码为root从Intellij连接：

- Yaroslav

Answer 2

看起来你在5432端口上运行了postgresql实例。检查一下在5432端口上运行的是什么：

sudo lsof -i :5432

如果有任何记录，您可以手动杀死正在使用此端口的进程（man kill）并重新运行 Docker 容器。在我的情况下，另一个本地实例的 PostgreSQL 在这个端口上运行，所以以下内容对我有帮助：

sudo pkill -u postgres

或者（更好的选择）为您的Docker PostgreSQL指定不同的端口映射（例如5433）：

docker run -p 5433:5432 ...other_your_flags... postgres:latest

- Vladimir Prokhorov

Answer 3

尝试删除您本地安装的PostgreSQL，如果存在的话。因为我的问题恰恰是这个想法不知道连接到哪里，是连接到Docker调查还是连接到我电脑上安装的数据库。

- MarkKush