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}"

Can't you just stop the active connections while you restore the database? You could also start a new postgres container, restore the database there, and then spawn a new app container linking to it, stopping the previous one. — dukebody
– dukebody, Commented Dec 20, 2014 at 17:47
If you were to provide the right commands, yes, I think I could "just" stop the active connections. See my snippet above. I'm asking for details because the details matter and this turns out to be non-trivial in a docker environment. Your suggestion about spawing a new container sounds plausible but due to container naming would require some scripting to avoid conflicts, etc. Got a snippet for that? — Peter Lyons
– Peter Lyons, Commented Dec 20, 2014 at 18:06
Your script looks right to me... You're disallowing all non-superuser new connections and then terminating existing ones. Which errors do you get? The only thing I'm missing is turning on new connections after restore. — dukebody
– dukebody, Commented Dec 21, 2014 at 14:07
The script above works without error. My app seems to be able to connect afterward even with explicitly re-enabling connections. Maybe because the DB itself gets recreated during the restore. — Peter Lyons
– Peter Lyons, Commented Dec 21, 2014 at 15:06

Dancrumb · Accepted Answer · 2014-12-27 16:49:44Z

4

+50

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.

edited Dec 27, 2014 at 16:49

Dancrumb

27.7k10 gold badges79 silver badges139 bronze badges

answered Dec 23, 2014 at 16:23

mirabilos

5,4193 gold badges49 silver badges78 bronze badges

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

2 Comments

Peter Lyons Over a year ago

My app runs in a separate container. I'm using the official library/postgres:9.4 postgres image. I start and stop them with "docker start" and "docker stop". Your suggestions don't seem to address my problem really. 1 and 4 Seem entirely out of harmony with docker. 2. Isn't an answer. 3. Seems possible, but you can't have 2 containers writing to the same data volume at once, so I'd have to coordinate stop db, start restore, wait for restore to exit, start db.

Peter Lyons Over a year ago

Restoring from backup is a hopefully-never operation. I don't want it in the startup script. I'm not using a startup script at the moment because I'm just running the stock postgres container. If I do that, the restore happens, and I forget to delete the restore file before the next db restart for whatever reason, BAM, data loss. Sounds treacherous. Did you read my script above? Any comments on that?

Collectives™ on Stack Overflow

restore postgres within docker

1 Answer 1

① same container

② separate container

③ management container

④ host

my personal recommendation

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

① same container

② separate container

③ management container

④ host

my personal recommendation

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related