restore postgres within docker

Question

I'm running into a long series of issues trying to use docker exec and the postgresql psql utility to restore a postgresql installation running within docker 1.3. I have a backup running OK via docker exec mycontainer pg_dumpall --clean --user=postgres --no-password > /tmp/backup.sql.

I have hit issues attempting a restore including:

• docker exec only works when the container is running but psql can't restore things properly with actively connected clients
• pg_ctl stop will exit the postgres server process, which stops both the main container and the docker exec process you are running alongside it.
• docker exec runs as root but pg_ctl must run as postgres (I was trying to use pg_ctl to stop postgres so the restore would work)

So given the scenario that a postgresql container is running and serving active connections to an application, how can I restore it? Looking for details around cleanly stopping, running the restore, starting, etc.

Env is docker 1.3, postgresql 9.4, data lives in a data volume mounted at /var/lib/postgresql/data in the container. I have a valid .sql backup file on the docker host filesystem.

Update: FYI I'm open to any solution that works, whether it involves docker exec or not. If I should run a separate container and link to the main postgresql container and talk to it over TCP, for example, that's fine as long as I get some workable flow.

---

Here's what I have so far. Suggestions welcome. It's a bash script (with some mustache variables that get interpolated during the build process) designed to be run on the docker host.

#!/bin/bash
docker_sql() {
  docker exec \
    --interactive \
    --tty \
    "${container}" \
    psql --user="${user}" --no-password --file="$1"
}

export DOCKER_HOST=tcp://localhost:2375
container="{{appName}}_db"
user=postgres
backup_path="$1"
if [[ -z "${backup_path}" ]]; then
  echo "Provide a path to a backup file" 1>&2
  exit 1
fi
backup_file=$(basename "${backup_path}")

restore_file=$(echo "${backup_file}" | sed -e s/.\bz2//)
restore_path="/var/lib/postgresql/data/${restore_file}"
bunzip2 --stdout "${backup_path}" > "/var/local/"${container}/"${restore_file}"

terminate_path="/var/lib/postgresql/data/terminate.sql"
cat <<EOF > "/var/local/${container}/terminate.sql"
revoke connect on database {{appName}} from public;
alter database {{appName}} connection limit 0;
select pg_terminate_backend(pid)
  from pg_stat_activity
  where pid <> pg_backend_pid()
  and datname='{{appName}}';
EOF

docker_sql "${terminate_path}"
docker_sql "${restore_path}"

Answer 1

How do you start and stop the database normally?

① same container

If I had a setup that bundled the database in the same container, I would have a container start script that first starts PostgreSQL in the background, and then execs the application.

In this case, it’s easy to insert the restore command before the exec of the application, shut down the container,

② separate container

Of course, separating the database service from the application, as you said you could do, is overall the best solution – they are separate services, after all.

③ management container

You wrote your application has the DB files “data volume mounted”. In this case, you could create a management container, that also has those files mounted, which only starts PostgreSQL, restores the backup, and exits again. Then, shut down your app container, start the restore container on the same DB volume, wait until it exits, then start your app container again.

④ host

In the same vein, you can likely run PostgreSQL on the DB volume on the host. This violates the isolation principles, and requires setting up the DB on the host though, so you may not want to do that.

my personal recommendation

I’d go for ② first, then ①, then (in your setup) ③, and then ④, in the order of preference.