7

In an initialization script, I want to initialize a PostgreSQL directory, but don't need (and don't want) a running PostgreSQL server at this stage.

This would be a no-brainer if I just create the cluster (as user postgres):

initdb -D ...

However, I also need to create the PostgreSQL role, create the database and add some extensions (also as user postgres):

createuser someuser
createdb -O someuser somedb
echo 'CREATE EXTENSION xyz;' | psql somedb

The latter commands require a running PostgreSQL server. So this whole thing becomes quite messy:

initdb -D ...

# Start PostgreSQL server in background
... &

# Wait in a loop until PostgreSQL server is up and running
while ! psql -f /dev/null template1; do
    sleep 0.5
done

createuser someuser
createdb -O someuser somedb
echo 'CREATE EXTENSION xyz;' | psql somedb

# Kill PostgreSQL server
kill ...

# Wait until the process is really killed
sleep 2

Especially the part that is waiting for the PostgreSQL server is never 100% reliable. I tried lots of variants and each of them failed in roughly 1 of 20 runs. Also, killing that process may not be 100% reliable in a simple shell script, let alone ensuring that it has stopped correctly.

I believe this is a standard problem that occurs in all use cases involving bootstrapping a server or preparing a VM image. So one would expect that in the year 2016, there should be some existing, realiable tooling for that. So my questions are:

  • Is there a simpler and more reliable way to achieve this?
  • For example, is there a way to run a PostgreSQL server in some special mode, where just starts up, executes certain SQL commands, and quits immediately after the last SQL command finished?
  • As a rough idea, is there something from the internal PostgreSQL test suite can be reused for this purpose?
vog
  • 23,517
  • 11
  • 59
  • 75

1 Answers1

9

You are looking for single-user mode.

If you start PostgreSQL like that, you are is a session connected as superuser that waits for SQL statements on standard input. As soon as you disconnect (with end-of-file), the server process is stopped.

So you could do it like this (with bash):

postgres --single -D /usr/local/pgsql/data postgres <<-"EOF"
CREATE USER ...;
CREATE DATABASE somedb ...;
EOF

postgres --single -D /usr/local/pgsql/data somedb <<-"EOF"
CREATE EXTENSION ...;
EOF
Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • Thanks! The single-user mode was the missing hint. However, your example code is a bit flawed. It tries to access the database before it was created. It seems that `postgres --single` needs to be executed twice - once with the `postgres` database, then with the created database. – vog Oct 14 '16 at 11:25
  • Of course. `dbname` was just an example. I'll update the answer for more clarity. – Laurenz Albe Oct 14 '16 at 11:28