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:
It does not need a companion server, it acts as a database server in its own right. Postgres has its own SQL language called pgsql.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With