"error: pq: role "根" does not exist" 当使用 Postgres for Docker 运行 pq 时

Question

我正在Docker上用Postgres:14 alpine镜像建立一个本地Postgres数据库，并用golang migrate在上面运行数据库迁移，运行migrate工具后收到以下错误消息：

error: pq: role "root" does not exist

我正在运行以下命令：

$ docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -d postgres:14-alpine
$ docker exec -it postgres14 createdb --user=root --owner=root demodb
$ migrate -path db/migrations -database postgresql://root:pass@localhost:5432/demodb?sslmode=disable --verbose up

这些命令也可以在这个Makefile中查看，完整的代码库可以在这个存储库中找到。

以下是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 ... UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
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....2022-10-15 09:56:41.209 UTC [36] LOG:  starting PostgreSQL 14.5 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
2022-10-15 09:56:41.211 UTC [36] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2022-10-15 09:56:41.217 UTC [37] LOG:  database system was shut down at 2022-10-15 09:56:41 UTC
2022-10-15 09:56:41.220 UTC [36] LOG:  database system is ready to accept connections
done
server started
CREATE DATABASE

/usr/local/bin/docker-entrypoint.sh: ignoring /docker-entrypoint-initdb.d/*
waiting for server to shut down...2022-10-15 09:56:41.422 UTC [36] LOG:  received fast shutdown request
.2022-10-15 09:56:41.423 UTC [36] LOG:  aborting any active transactions
2022-10-15 09:56:41.423 UTC [36] LOG:  background worker "logical replication launcher" (PID 43) exited with exit code 1
2022-10-15 09:56:41.424 UTC [38] LOG:  shutting down
2022-10-15 09:56:41.434 UTC [36] LOG:  database system is shut down
done
server stopped
PostgreSQL init process complete; ready for start up.

如何正确配置root角色？

Answer 1

docker image docs指定POSTGRES_USER环境变量默认为postgres，如果未设置，请尝试使用它而不是root，或者放下容器，使用正确的环境变量重新构建它

一旦进入psqlshell，就可以使用创建用户

CREATE USER username WITH PASSWORD 'your_password';

然后授予用户访问特定数据库的权限：

GRANT ALL PRIVILEGES ON DATABASE demodb TO username;

完成后，您可以在make文件中的连接字符串中使用用户

Answer 2

原来Hombree在我的操作系统上安装和设置的Postgres服务器使用了相同的端口，这与在相同端口号下对容器化数据库的请求发生了冲突。

这个问题可以通过为容器化数据库使用不同的端口号来解决，也可以通过关闭操作系统上的数据库来解决。