How to backup and restore PostgreSQL using Docker

Let's backup data

When managing Sentry.io on premise,
I encountered an issue.

I need to backup data.

Sentry v10 is ready

Sentry v10 has been released (https://blog.sentry.io/2020/01/07/self-hosted-sentry-10-is-ready-to-serve-get-it-while-its-hot),
and I was trying to upgrade from v9.1.2

It should be simply done by below command.

upgrade

git pull origin master
./install.sh

But I should care about failure cases
so that I started investigating how to rollback.

Handle PostgreSQL version's diff

Sentry v9.1.2 uses PostgreSQL v9.5,
and v10 is based one psql v9.6

First I tried to checkout to tag9.1.2 after version up.
and downgrade on perpose.

But one error was shown.
I'm not sure about the cause,
but only thing I can say is that
backup data before up grade and restore after rollback should be good idea.

So I started investigation how to backup and restore data of PostgreSQL with Docker.

Here it is

Backup current all data.

Backup

$ docker exec -t -u postgres onpremise_postgres_1 pg_dumpall -c > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

Let's prune volume and then reinstall.

reinstall

$ docker volume prune
$ ./install.sh

Let's initialize db (drop all tables) before restoring data.

initializedb

$ docker exec -it onpremise_postgres_1 bash #into psql container
$ su postgres #default user: postgres
$ psql -U postgres #login to psql
  drop schema public cascade;
  create schema public;
  grant usage on schema public to public;
  grant create on schema public to public;
ctrl+D
ctrl+D

Finally restore data.

Restore

$ cat <backup file>.sql | docker exec -i onpremise_postgres_1 psql -U postgres