Smooth PostgreSQL upgrades in DockerDev environments with Lefthook
Tags: docker, postgres, git
Published: 2020-09-30 16:38:25

It's not a secret that I'm a big fan of Docker for local development: I'm building large [Rails apps][ruby-on-whales] with it and hacking [Ruby itself](https://github.com/palkan/ruby-dip).

The main reason why I love Dockerized environments is the simplicity of synchronizing configuration between developers/machines: all you need is to update a `Dockerfile` or `docker-compose.yml` and bump an image version.

Unfortunately, that's not always the case: imaging a situation of upgrading a database (PostgreSQL in our case) to a new major version. Simply changing `postgres:12` to `postgres:13` would break our dev env: 

```
FATAL: database files are incompatible with server
```

Let's see how we can make such upgrades painless with the help of [Lefthook][lefthook].

## Upgrading PostgreSQL in Docker

Although PostgreSQL has an official upgrade tool ([`pg_upgrade`][pg_upgrade]), it's hardly possible to use it with Docker since it requires both versions of PostgreSQL to be installed on the same _machine_:

{% github https://github.com/docker-library/postgres/issues/37 %}

You can find a straightforward workaround in the linked thread: create a SQL dump with the previous version and _restore_ it into the new one. Let's do that!

First, we need to update our `docker-compose.yml` to define two PostgreSQL services (see [Ruby on Whales][ruby-on-whales] for the complete configuration):

```yml
services:
  # ...
  # That's the old version of a service
  postgres-old:
    image: postgres:12
    volumes:
      - .psqlrc:/root/.psqlrc:ro
      - postgres:/var/lib/postgresql/data
      - ./log:/root/log:cached
    environment:
       PSQL_HISTFILE: /root/log/.psql_history
       POSTGRES_PASSWORD: postgres
    healthcheck:
      test: pg_isready -U postgres -h 127.0.0.1
      interval: 5s

  postgres:
    image: postgres:13
    volumes:
       - ./.psqlrc:/root/.psqlrc:ro
       # NOTE: we have a version suffix in a new volume name
       - postgres12:/var/lib/postgresql/data
       - ../log:/root/log:cached
     environment:
       PSQL_HISTFILE: /root/log/.psql_history
       POSTGRES_PASSWORD: postgres
     ports:
       - 5432
     healthcheck:
      test: pg_isready -U postgres -h 127.0.0.1
      interval: 5s

volumes:
  - postgres
  - postgres12
  # ...
```

**NOTE:** If you using PostgreSQL tools in your application container, you also need to upgrade them:

```diff
  app: &app
    build:
      context: .dockerdev
      dockerfile: Dockerfile
      args:
        RUBY_VERSION: '2.6.3'
-       PG_MAJOR: '12'
+       PG_MAJOR: '13' 
        NODE_MAJOR: '11'
        YARN_VERSION: '1.13.0'
        BUNDLER_VERSION: '2.0.2'
    # IMPORTANT: Bump a minor version of a dev image
    # to re-build it for everyone
-   image: my-app-dev:1.0.0
+   image: my-app-dev:1.1.0
    tmpfs:
      - /tmp
```

Now, let's start our postgres services and "migrate" the data:

```
# make sure containers are not running
docker-compose stop

# then, start them
docker-compose up -d postgres-old postgres

# make sure that both containers are up and running (e.g., via `docker ps`)
# that could take some time, because we need to download a new image

# finally, run the migration command
docker exec my-app-dev_postgres-old_1 pg_dumpall -U postgres | \
  docker exec -i my-app-dev_postgres_1 psql -U postgres
```

**NOTE:** Replace `my-app` with your Docker Compose project name.

The last command looks a bit complicated and requires some _hidden_ knowledge: the names of the PostgreSQL containers.

If you're using [Dip][] (like I do), you can define custom commands instead:

```yml
# dip.yml

interaction:
  # ...
  psql:
    description: Run psql console
    service: postgres
    default_args: zipline_light_development
    command: env PGPASSWORD=postgres psql -h postgres -U postgres -d
  pg_dump_old:
    description: Dump a database for a previous PostgreSQL version
    service: postgres-old
    command: pg_dumpall -U postgres -h postgres-old
```

And now the migration command transforms into:

```sh
dip pg_dump_old | dip psql
```

Hopefully, the process above succeeds, and you get all your 
local data successfully migrated.

Now, let's talk about how to automate this process and help your co-workers to avoid this not-so-pleasant experience.

## Automating upgrades with Lefthook

[Lefthook][lefthook] is my top choice for managing Git hooks.
Which hook could help us solve the problem? I think the `post-checkout` hook is a good fit here: whenever a user switches to a branch with the upgraded PostgreSQL, we can automatically perform the required migration actions.

Let's create a new script under `.lefthook/post-checkout` called `postgres-upgrade`:

```sh
#!/bin/bash

# Do not run for irrelevant checkout (i.e., when we do `git checkout` but do not switch branches)
BRANCH_CHANGE=$3
[[ $BRANCH_CHANGE -eq 0 ]] && exit
PREV_HEAD=$1
CURR_HEAD=$2
[ $PREV_HEAD == $CURR_HEAD ] && exit

# Only run when Dip is installed: users without Dip are likely not using Docker dev env at all.
if ! which dip >/dev/null; then
  exit 0
fi

# Only run if Docker is running
if ! $(docker info > /dev/null 2>&1); then
  echo "Docker is not running. Skipping postgres-upgrade hook"
  exit 0
fi

# If there is no new postgres volume, then we haven't used a branch with the upgrade before
if docker volume ls | grep my-app-dev_postgres13 >/dev/null; then
  # Already created
  exit 0
fi

# Drop the old container if any (otherwise, there could be a conflict when we mount a folder with a new version)
if docker container ls | grep my-app-dev_postgres_1 > /dev/null; then
  dip compose rm -f postgres
fi

echo "Preparing a new PostgreSQL container to upgrade to version 13..."

dip up -d postgres-old postgres

# Wait for a new container to become healthy
until docker container ls | grep my-app-dev_postgres_1 | grep healthy;
  do
    sleep 1
  done

echo "Migrating data from the old PostgreSQL instance to a new one..."

dip pg_dump_old | dip psql

# Stop the old container, we don't need it anymore
dip stop postgres-old

echo "Done ✅"

exit 0
```

Finally, let's add our script to the Lefthook configuration:

```yml
post-checkout:
  scripts:
    postgres-upgrade:
      runner: "bash"
```

**NOTE:** If that's the first `post-checkout` hook, you also need to run `lefthook install` to activate it.

That's it!

[ruby-on-whales]: https://evilmartians.com/chronicles/ruby-on-whales-docker-for-ruby-rails-development
[lefthook]: https://github.com/Arkweid/lefthook
[pg_upgrade]: https://www.postgresql.org/docs/current/pgupgrade.html
[Dip]: https://github.com/bibendi/dip