Restore postgres in docker

Question

I ran into a long series of problems trying to use docker exec and the postgresql psql utility to restore the postgresql installation working in docker 1.3. I have a backup working OK through docker exec mycontainer pg_dumpall --clean --user=postgres --no-password > /tmp/backup.sql .

I have problems trying to recover, including:

• docker exec only works when the container starts, but psql cannot correctly restore files with actively connected clients.
• pg_ctl stop will exit the postgres server process, which stops both the main container and the docker exec process that you use with it.
• docker exec works as root, but pg_ctl should execute as postgres (I tried using pg_ctl to stop postgres for recovery to work)

So, given the scenario in which the postgresql container is running and which serves for active connections to the application, how to restore it? Finding parts around a clean stop, starting recovery, starting, etc.

Env - docker 1.3, postgresql 9.4, data is stored in the amount of data set in /var/lib/postgresql/data in the container. I have a .sql backup file in the docker host file system.

Update: FYI I am open to any solution that works, regardless of whether it is related to docker exec or not. If I have to start a separate container and bind it to the main postgresql container and talk to it over TCP, for example, this is fine as long as I get some workable thread.

---

Here is what I still have. Suggestions are welcome. This is a bash script (with some mustache variables that are interpolated during the build process) designed to 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 -es/.\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 usually start and stop the database?

① same container

If I had an installation in which the database was included in the same container, I would have run a container script that first runs PostgreSQL in the background, and then an exec application.

In this case, it is easy to insert a recovery command before the exec application, close the container,

② separate container

Of course, separating the database service from the application, as you said, you can make, in general, the best decision - in the end, these are separate services.

③ control container

You wrote your application, which has DB files, "data volume installed." In this case, you can create a management container in which these files are also installed, which runs PostgreSQL, restores the backup, and exits again. Then close the application container, run the recovery container on the same database volume, wait until it exits, and then run the application container again.

④ host

In the same vein, you can run PostgreSQL on the database volume on the host. This violates the principles of isolation and requires tuning the database on the host, so you may not want to do this.

my personal recommendations

Go first for ②, then ①, then (in your setup) ③, and then ④ in order of preference.