在 postgres 中以单用户模式创建表 [英] Creating a table in single user mode in postgres

问题描述

我正在尝试从 postgres 映像创建 Dockerfile.repo 说初始化应该通过在/docker-entrypoint-initdb.d/中放置一个 shell 脚本来处理.我根据我在网上找到的一个示例放置了以下脚本:

Im trying to create a Dockerfile from the postgres image. The repo says that initialization should be handled by placing a shell script in /docker-entrypoint-initdb.d/. I put the following script based on an example I found online:

#!/bin/bash
echo "******CREATING DOCKER DATABASE******"
gosu postgres postgres --single <<- EOSQL
   CREATE DATABASE orpheus;
   CREATE USER docker WITH ENCRYPTED PASSWORD 'pwd_docker';
   GRANT ALL PRIVILEGES ON DATABASE orpheus to docker;
   CREATE TABLE profiles ( 
     profile_id    SERIAL UNIQUE PRIMARY KEY, 
     user_id integer NOT NULL UNIQUE, 
     profile_photo_id integer NOT NULL UNIQUE, 
     age integer 
   );
   CREATE TABLE hidden_user ( 
     owner_id    integer NOT NULL PRIMARY KEY, 
     target_id integer NOT NULL 
   );
EOSQL
echo ""
echo "******DOCKER DATABASE CREATED******"

反斜杠似乎是必需的，否则我会收到解析错误.脚本运行没有错误，除了 CREATE TABLE 命令之外的所有命令似乎都产生了效果.

The backslashes seem required since otherwise I get a parse error. The script runs without error and all of the commands except for the CREATE TABLE commands seem to have had an effect.

是不是单用户模式不支持建表?如果是这样，有没有更好的方法让 dockerfile 设置一个带有在 postgres 中创建的表的图像?

Is it that table creation is not supported in single user mode? If so, is there a better way to have a dockerfile set up an image with tables created in postgres?

推荐答案

@a_horse_with_no_name 的评论让我走上了正轨.我决定放弃单用户模式，即使它是推荐的".相反，我使用 pg_ctl 启动 postgres，加载一些包含我的表创建的 sql 文件，然后使用 pg_ctl 停止服务器.

@a_horse_with_no_name got me on the right track with his comment. I decided to ditch the single user mode even if it was "recommended". Instead I start postgres with pg_ctl, load some sql files containing my table creations, and stop the server with pg_ctl.

我的 shell 脚本如下所示:

My shell script looks like this:

#!/bin/bash
echo "******CREATING DOCKER DATABASE******"

echo "starting postgres"
gosu postgres pg_ctl -w start

echo "bootstrapping the postgres db"
gosu postgres psql -h localhost -p 5432 -U postgres -a -f /db/bootstrap.sql

echo "initializing tables"
gosu postgres psql -h localhost -p 5432 -U postgres -d orpheus -a -f /db/setup.sql

echo "stopping postgres"
gosu postgres pg_ctl stop

echo "stopped postgres"


echo ""
echo "******DOCKER DATABASE CREATED******"

这篇关于在 postgres 中以单用户模式创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！