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

问题描述

我试图从postgres图像创建一个Docker文件。 repo表示初始化应该通过将一个shell脚本放在/docker-entrypoint-initdb.d/中来处理。我根据在线发现的一个例子放了以下脚本：

 ＃！/ bin / bash 
 echo* ***** CREATING DOCKER DATABASE ******
 gosu postgres postgres --single<  -  EOSQL 
 CREATE DATABASE orpheus; 
 CREATE USER docker WITH ENCRYPTED PASSWORD'pwd_docker'; 
授予数据库orpheus所有特权到码头; 
 CREATE TABLE profiles（\ 
 profile_id SERIAL UNIQUE PRIMARY KEY，\ 
 user_id integer NOT NULL UNIQUE，\ 
 profile_photo_id integer NOT NULL UNIQUE，\ 
年龄整数\ 
）; 
 CREATE TABLE hidden_​​user（\ 
 owner_id integer NOT NULL PRIMARY KEY，\ 
 target_id integer NOT NULL \ 
）; 
 EOSQL 
 echo
 echo****** DOCKER DATABASE CREATED ******
  

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

是否在单用户模式下不支持表创建？如果是这样，有没有更好的方法来建立一个dockerfile，在postgres中创建表格？

解决方案

@a_horse_with_no_name我在正确的轨道上与他的评论。即使是推荐，我也决定使用单一用户模式。相反，我用pg_ctl启动postgres，加载一些包含我的表创建的sql文件，并用pg_ctl停止服务器。

我的shell脚本如下所示：

 ＃！/ bin / bash 
 echo****** CREATING DOCKER DATABASE ******
 
 echo开始postgres
 gosu postgres pg_ctl -w start 
 
 echobootstrapping the postgres db
 gosu postgres psql -h localhost -p 5432 -U postgres -a -f /db/bootstrap.sql 
 
 echo初始化表
 gosu postgres psql -h localhost -p 5432 -U postgres -d orpheus -a -f /db/setup.sql 
 
 echostopped postgres
 gosu postgres pg_ctl停止
 
 echostopped postgres
 
 
 echo
 echo****** DOCKER DATABASE CREATED ****** 
  

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******"

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.

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 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.

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屋！