Creating a table in single user mode in postgres

Question

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?

Single user mode is not intended for anything else than disaster recovery: postgresql.nabble.com/… The question is: why do you start Postgres in single user mode at all? — user330315
– user330315, Commented Jan 30, 2015 at 22:49
It is recommended in the official docker repo for postgres. "If you need to execute SQL commands as part of your initialization, the use of Postgres single user mode is highly recommended." - registry.hub.docker.com/_/postgres — sdab
– sdab, Commented Jan 30, 2015 at 23:24
@a_horse_with_no_name I think they're trying to avoid the database coming up and being available for general connections before the desired init has finished. It'd be infinitely saner to start it with listen_addresses = '' and use a unix socket, or listen_addresses = '127.0.0.1, ::1' (via the -c` argument to postgres) and use a loopback psql session, do what you need to do, and restart Pg for general access, but the Docker folks seem to prefer a quick hack to a robust solution most of the time... — Craig Ringer
– Craig Ringer, Commented Feb 1, 2015 at 10:59

sdab · Accepted Answer · 2015-01-31 00:46:19Z

10

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

answered Jan 31, 2015 at 0:46

sdab

1931 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jondlm Over a year ago

This worked great for me. Thanks for the tip; I was starting to spin my wheels.

Vitaly Isaev Over a year ago

You saved several hours of my life... Thanks a lot.

GlynD Over a year ago

Almost perfect - I switched the user on the "initialising tables" to one created in the bootstrap though :)

Louis Simoneau · Accepted Answer · 2015-04-09 23:43:27Z

5

If you want to prevent PostgreSQL from being accessible to users before whatever setup you need to perform is done, start it with only loopback access or only a unix socket, do your initialisation, then restart it for general access.

I don't speak Docker, but if you were doing this in a regular environment you'd do something like:

mkdir -p /db/temp_socket
chown -r postgres:postgres /db

PGHOST=/db/temp_socket pg_ctl -D /path/to/datadir -o "-c listen_addresses='' -c unix_socket_directories='/db/temp_socket'" -l "/db/dbsetup.log" -w start

# Do your work
PGHOST=/db/temp_socket psql -f some_script

PGHOST=/db/temp_socket pg_ctl -D /path/to/datadir -m fast -w stop

pg_ctl -D /path/to/datadir -w start ...normalstartupoptionsblah...

i.e. start PostgreSQL not listening on any TCP/IP sockets, and with a non-default unix_socket_directories. Do your setup. Then restart it with the default (or configured) unix_socket_directories and listen_addresses once it's ready for general access.

Instead of this you could:

Modify pg_hba.conf to only allow access from your setup user / only on the loopback address / etc
Start Pg
Do your setup
Replace pg_hba.conf with the production one
pg_ctl reload or SELECT pg_reload_conf() to load the new settings and allow general access

... however this will permit applications to connect then reject their authentication during the setup stage; that may not be what you want, and not all applications cope with this correctly.

edited Apr 9, 2015 at 23:43

Louis Simoneau

1,79114 silver badges19 bronze badges

answered Feb 1, 2015 at 11:20

Craig Ringer

329k84 gold badges743 silver badges821 bronze badges

1 Comment

Louis Simoneau Over a year ago

I like this answer. Our app containers are using a little script to wait until the postgres container is accepting TCP connections before attempting to connect to it. Having it briefly up in order to import the schema is a problem, which is nicely solved by using a local socket.

gabuzo · Accepted Answer · 2015-05-05 18:49:29Z

I tested your script and it is almost working fine. Using Postgresql 9.4 I managed to make the following to work:

gosu postgres postgres --single -jE <<- EOSQL
   CREATE DATABASE orpheus;
EOSQL
echo    
gosu postgres postgres --single -jE orpheus <<- EOSQL
   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

Basically I had to split the script in two as the postgresql was complaining that the create database couldn't be used in multiline scripts. And the other was only to add the database name, orpheus, on the second command.

And voilà

Collectives™ on Stack Overflow

Creating a table in single user mode in postgres

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related