Creating a table in single user mode 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/bashecho "******CREATING DOCKER DATABASE******"echo "starting postgres"gosu postgres pg_ctl -w startecho "bootstrapping the postgres db"gosu postgres psql -h localhost -p 5432 -U postgres -a -f /db/bootstrap.sqlecho "initializing tables"gosu postgres psql -h localhost -p 5432 -U postgres -d orpheus -a -f /db/setup.sqlecho "stopping postgres"gosu postgres pg_ctl stopecho "stopped postgres"echo ""echo "******DOCKER DATABASE CREATED******"

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_socketchown -r postgres:postgres /dbPGHOST=/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 workPGHOST=/db/temp_socket psql -f some_scriptPGHOST=/db/temp_socket pg_ctl -D /path/to/datadir -m fast -w stoppg_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.

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;EOSQLecho    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   );EOSQLecho

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à