Smooth PostgreSQL upgrades in DockerDev environments with Lefthook

It's not a secret that I'm a big fan of Docker for local development: I'm building large Rails apps with it and hacking Ruby itself.

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.

Upgrading PostgreSQL in Docker

Although PostgreSQL has an official upgrade tool (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:

Upgrading between major versions? #37

roosmaa posted on Nov 23, 2014

There doesn't seem to be a good way to upgrade between major versions of postgres. When sharing the volume with a new container with a newer version of postgres it won't run as the data directory hasn't been upgraded. pg_upgrade on the other hand requires (?) old installation binary files, so upgrading the data files from new server container is also difficult.

It would be nice if there was some suggested way of doing this in the readme. Maybe even some meta container which does the upgrading from version to version?

View on GitHub

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 for the complete configuration):

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:

  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:

# 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:

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 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:

#!/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:

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!

Comments

[Steve Schwarz]

Great idea to run both containers. When I ran:

docker exec my-app-dev_postgres-old_1 pg_dumpall -U postgres | \
docker exec -i my-app-dev_postgres_1 psql -U postgres

I got an error because only one of the postgres containers exposed a port(?).
I had to save the pg_dumpall to a local file and then cat it into the other container running psql.