Docker部署Postgres时golang-migrate报错：pq: role 'root' does not exist

问题描述 #

使用postgres:14-alpine镜像搭建本地PostgreSQL，执行golang-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

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.

解决方案 #

原因1：残留数据卷导致初始化脚本未执行 #

如果之前运行过PostgreSQL容器且未清理数据卷，再次启动容器时会跳过初始化步骤，POSTGRES_USER=root的配置不会生效，导致root角色未创建。

解决步骤：

1. 停止并删除现有容器：

docker stop postgres14
docker rm postgres14

2. 清理关联的匿名数据卷（会删除所有未使用的卷，谨慎操作）：

docker volume prune

3. 重新启动容器，等待初始化完成（通过docker logs -f postgres14查看，直到出现database system is ready to accept connections）：

docker run --name postgres14 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -d postgres:14-alpine

4. 重新执行数据库创建和迁移命令：

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

原因2：本地PostgreSQL服务占用端口 #

如果本地机器已运行PostgreSQL服务并占用5432端口，migrate会连接到本地服务而非Docker容器内的服务，而本地服务无root角色。

解决步骤：

1. 停止本地PostgreSQL服务，或修改Docker端口映射：

docker run --name postgres14 -p 5433:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=pass -d postgres:14-alpine

2. 执行数据库创建命令（容器内操作不受端口映射影响）：

docker exec -it postgres14 createdb --user=root --owner=root demodb

3. 修改migrate命令的端口为映射后的5433：

migrate -path db/migrations -database postgresql://root:pass@localhost:5433/demodb?sslmode=disable --verbose up

验证root角色是否存在 #

可以进入容器执行命令验证root角色是否创建成功：

docker exec -it postgres14 psql -U root -c "\du"

如果输出中包含root角色且标注为Superuser，说明角色创建正常。

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