How To Upgrade Gitlab and Postgres with Docker Compose

Yesterday I got hit with the double whammy. I tried to upgrade GitLab to v16.3.4 as part of their recent critical security release when I realised that the GitLab server was running version 15.x (I don’t remember which minor release), so first I would need to install 16.0.0, then get up to 16.3.4.

This, it turned out, meant first upgrading Postgres from version 12.10 to at least version 13.6.

Checking out Dockerhub, I saw version 16 of Postgres was available, so figured, heck, why not just whack on the latest and greatest?

Well, it turned out to be trickier than that. Quite a bit trickier. And that’s why I am making this blog post.

Docker Compose Setup

I’ve been running a self hosted / local GitLab since at least 2015, presumably earlier but that’s from when I first made a video series about GitLab.

It’s a great bit of kit, but there is a fair bit of on-going maintenance involved in self hosting. Backups, updates, more backups, firewall settings, patching the host, and more stuff I will have forgotten at this point in time.

I’ve also had two unrecoverable failures with earlier installs, meaning I’ve iterated my setup several times. First I had it all installed on an actual physical Linux server. The second time, it was on VMWare. And the third and current form is via Docker.

Specifically, Docker Compose.

Here’s the full docker-compose.yaml file, which is quite large and the actually interesting parts of this config are ‘hidden’ at first glance. But I share it simply because others may find it interesting:

version: "3"

services:
  gitlab:
    image: "gitlab/gitlab-ce:16.3.4-ce.0"
    container_name: gitlab
    restart: unless-stopped
    hostname: "your-gitlab-url.com" # Change host here
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        external_url 'https://your-gitlab-url.com'       # Change url here
        nginx['listen_port'] = 80
        nginx['listen_https'] = false
        nginx['proxy_set_headers'] = {
          "X-Forwarded-Proto" => "https",
          "X-Forwarded-Ssl" => "on"
        }
        # DATABASE CONNECTION SETTINGS: in our case we use postgresql as database
        gitlab_rails['db_adapter'] = "postgresql"
        gitlab_rails['db_database'] = "gitlab"
        gitlab_rails['db_username'] = "postgres"
        gitlab_rails['db_password'] = "someSecurePassword123!!" # set the database password here
        gitlab_rails['db_host'] = "gitlab_database"

        # GITLAB DOCKER IMAGE REGISTRY: so that we can use our docker image registry with gitlab
        registry['enable'] = false # we do not activate this option because we provide our own registry
        gitlab_rails['registry_enabled'] = true
        gitlab_rails['registry_host'] = "registry.your-gitlab-url.com"                       # Change registry host here
        gitlab_rails['registry_api_url'] = "https://registry.your-gitlab-url.com"            # Change registry url here
        gitlab_rails['registry_issuer'] = "gitlab-issuer"

        # SMTP SETTINGS: So that gitlab can send emails. In our case we send via google mail.
        gitlab_rails['smtp_enable'] = false
        gitlab_rails['smtp_address'] = "smtp.gmail.com"
        gitlab_rails['smtp_port'] = 587
        gitlab_rails['smtp_user_name'] = "gitlab@example.com"
        gitlab_rails['smtp_password'] = "xxxx"
        gitlab_rails['smtp_domain'] = "smtp.gmail.com"
        gitlab_rails['smtp_authentication'] = "login"
        gitlab_rails['smtp_enable_starttls_auto'] = true
        gitlab_rails['smtp_tls'] = false
        gitlab_rails['smtp_openssl_verify_mode'] = "peer"
    ports:
      - "22:22"
    volumes:
      - ./volumes/gitlab/secrets:/root/secret/gitlab/backups
      - ./volumes/gitlab/config:/etc/gitlab
      - ./volumes/gitlab/logs:/var/log/gitlab
      - ./volumes/gitlab/data:/var/opt/gitlab
      - ./volumes/registry/certs:/certs
    labels:
      - "traefik.enable=true"
      - "traefik.gitlab.port=80"
      - "traefik.gitlab.backend=gitlab"
      - "traefik.gitlab.frontend.rule=Host:your-gitlab-url.com" # Change host here
      - "traefik.gitlab.frontend.entryPoints=http,https"
      - "traefik.docker.network=traefik_default"

  registry:
    restart: unless-stopped
    image: registry:2.8
    container_name: gitlab_registry
    volumes:
      - ./volumes/registry/data:/registry
      - ./volumes/registry/certs:/certs
    labels:
      - "traefik.enable=true"
      - "traefik.frontend.rule=Host:registry.your-gitlab-url.com" # Change registry host here
      - "traefik.port=5000"
      - "traefik.backend=gitlab-registry"
      - "traefik.frontend.entryPoints=http,https"
      - "traefik.docker.network=traefik_default"
    environment:
      REGISTRY_LOG_LEVEL: debug
      REGISTRY_STORAGE_FILESYSTEM_ROOTDIRECTORY: /registry
      REGISTRY_AUTH_TOKEN_REALM: https://your-gitlab-url.com/jwt/auth # Change url here
      REGISTRY_AUTH_TOKEN_SERVICE: container_registry
      REGISTRY_AUTH_TOKEN_ISSUER: gitlab-issuer
      REGISTRY_AUTH_TOKEN_ROOTCERTBUNDLE: /certs/gitlab-registry.crt
      REGISTRY_STORAGE_DELETE_ENABLED: "true"

  database:
    image: postgres:16-alpine
    container_name: gitlab_database
    restart: unless-stopped
    #ports:
    #  - "5434:5432"
    environment:
      POSTGRES_PASSWORD: "someSecurePassword123!!" # use the same password as the one you used above
      POSTGRES_DB: gitlab
      POSTGRES_INITDB_ARGS: "--auth-host=scram-sha-256"
    volumes:
      - ./volumes/gitlab/database/16:/var/lib/postgresql/data

  traefik:
    container_name: gitlab_traefik
    restart: unless-stopped
    image: traefik:v1.7
    command: --configFile=/var/traefik/traefik.toml
    ports:
      - "443:443"
      - "80:80"
      - "8090:8090"
    volumes:
      - /var/run/docker.sock:/var/run/docker.sock
      - ./traefik.toml:/var/traefik/traefik.toml:ro
      - ./volumes/traefik/log:/log
      - ./acme.json:/acme.json

networks:
  web:
    external:
      name: traefik_default

Code language: YAML (yaml)

Now, I must first clarify that I did not invent this setup.

I found it – years ago – and have adapted it to my needs. Unfortunately a cursory Google today doesn’t return an obvious result as to where I found it originally. I’m pretty sure it was a GitHub gist.

As I said at the start of this post, the issue(s) here stem from me not doing too much in terms of day-to-day maintenance.

I have kept the GitLab container image updated, but prior to this recent change my actual config looked like this:

services:
  gitlab:
    image: "gitlab/gitlab-ce:15.11.8-ce.0"
  database:
    image: postgres:12-alpine
    volumes:
      - ./volumes/gitlab/database/12:/var/lib/postgresql/dataCode language: YAML (yaml)

Obviously that’s slimmed right down to highlight just the interesting bits.

The next thing I need to do is upgrade Traefik to v2.x, as I do know v3.x is just around the corner. Anyway, that’s for a different time.

The really useful bit here is that I am using a bind mount volume for my database container.

This setup binds the host directory ./volumes/gitlab/database/12 to the container directory /var/lib/postgresql/data, effectively making the data stored in /var/lib/postgresql/data in the Postgres container accessible from the host machine at ./volumes/gitlab/database/12. Changes made in either location will be reflected in the other since it’s a two-way bind.

By sheer good fortune I had the foresight to denote that I was using Postgres 12 in the directory path itself. This came in very useful during the upgrade.

Upgrading Postgres With Docker

In an ideal world, the way that a Dockerised Postgres database would be upgrade would be to stop the running container, change the image from e.g. Postgres:12 to Postgres:16, do a docker-compose up, and somehow Postgres would handle everything seamlessly for us.

However, we do not live in an ideal world. And that is not what actually happens.

If you do the above, when the new Postgres container starts it will throw a fatal error:

docker-compose up

Recreating postgres-test_db_1 ... done
Attaching to postgres-test_db_1
db_1  | 
db_1  | PostgreSQL Database directory appears to contain a database; Skipping initialization
db_1  | 
db_1  | 2023-09-19 17:59:01.632 UTC [1] FATAL:  database files are incompatible with server
db_1  | 2023-09-19 17:59:01.632 UTC [1] DETAIL:  The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 16.0.
Code language: JavaScript (javascript)

And you may be thinking, well… OK, maybe it’s because you’re going from 12.x to 16.x. What if I go up only one major version?

The data directory was initialized by PostgreSQL version 12, which is not compatible with this version 13.12Code language: JavaScript (javascript)

So, basically the same error.

The officially recommended Postgres way to upgrade between major versions is to use the pg_upgrade tool.

However, I couldn’t find a great deal of info about using pg_upgrade when using Docker. Instead, the generally suggested way is to do a backup from the older / existing Postgres instance, and then a restore into the new / upgraded instance.

The benefit of this, I suppose, is that you do get a backup. Because I know some of you would YOLO this process. And by ‘some of you’, I mean that’s what I would have done.

Anyway, I won’t claim credit for this process. I got it from Holger Woltersdorf’s blog. I’d encourage you to read that post before continuing.

The difference between the approach Holger provides, and my approach is that he is using a named docker volume, where I am using a bind mounted volume. I still can’t shift away from them, and in a single host server setup, I haven’t yet found any problems in using them.

The Process

There are several steps to complete. Let’s break them down one by one.

Backup Your Docker Compose File

First of all, I’d recommend copying your docker-compose.yaml file:

cp docker-compose.yaml docker-compose.yaml.bakCode language: Shell Session (shell)

Shut Down Your Running Containers

Then take your service down:

docker-compose downCode language: Shell Session (shell)

Edit Your Docker Compose File

Now, edit your docker-compose.yaml file to comment out all the other services.

There are other ways to do this. I just work best doing things the simple way.

Basically this stops anything else starting up and interacting with your database during this process.

At this point your docker-compose.yaml file should look something like this:

version: "3"

services: 
  database:
    image: postgres:12-alpine
    container_name: gitlab_database
    restart: unless-stopped
    #ports:
    #  - "5434:5432"
    environment:
      POSTGRES_PASSWORD: "someSecurePassword123!!"
      POSTGRES_DB: gitlab
    volumes:
      - ./volumes/gitlab/database/12:/var/lib/postgresql/dataCode language: YAML (yaml)

Restart Just Your Existing Database Container

Now, bring that back up:

docker-compose up -dCode language: Shell Session (shell)

All being well, if you look at the output of the container logs you should see:

database system is ready to accept connectionsCode language: Shell Session (shell)

Perfect.

Backup Your Existing Database

OK, next we need to take a backup of the database.

In order to do this you need to know two things:

1. Your Postgres username
2. The Postgres DB service name

Fortunately, both of these things are easy to find out by examining your docker-compose.yaml file:

version: "3"

services: 
  database:
    image: postgres:12-alpine
    container_name: gitlab_database
    restart: unless-stopped
    #ports:
    #  - "5434:5432"
    environment:
      POSTGRES_PASSWORD: "someSecurePassword123!!"
      POSTGRES_DB: gitlab
    volumes:
      - ./volumes/gitlab/database/12:/var/lib/postgresql/data
Code language: Dockerfile (dockerfile)

In the case above, the service name is database. It is literally whatever the string / key is under the services section.

To find out your username, you will either have explicitly set it, e.g. :

# docker-compose.yaml

version: '3.9'
services:
  db:
    container_name: "bob"
    image: postgres:12.14-alpine
    ports:
      - "5445:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
Code language: YAML (yaml)

Or by default, it will be ‘postgres’.

From your host system, run the following command:

docker-compose exec db pg_dumpall -U "postgres" > 2023-09-19-Backup.sqlCode language: Shell Session (shell)

Remember, swap out:

• db for your service name
• -U "postgres" for -U YOUR_USER_NAME_HERE
• 2023-09-19-Backup.sql for whatever filename you want to give to your back up file.

This may take a while to run, depending on the size of your database.

Once done, you should have a SQL dump file in your current working directory with the name of the file you provided.

Validate The Backup

How you choose to do this is up to you.

For the truly paranoid, I would suggest doing a restore to another machine.

For the casual fly-boys I would suggest doing a cat my-backup-name.sql and if it looks alright-ish, then on we go.

Of course, use the level of professionalism necessary for the task. If this is your own stuff, as it is mine, you can be as blasé as you like. For paid work, be sensible and serious. I shouldn’t have to tell you this.

What I will say is that this is a fairly safe process as we won’t be deleting anything. Absolute worse case we can bring the existing container back online at any time and our existing data will be there.

On we go.

Shut Down The Old Database Container

Now that we have a backup, we no longer need the old container.

docker-compose down

Update The Docker Compose File

Next we need to update the docker-compose.yaml file to specify the new version of Postgres that we are upgrading too.

I am going to Postgres 16, which is the latest (and therefore greatest) at the time of writing.

Version 16 of Postgres also causes some problems, which we will cover below.

version: "3"

services: 
  database:
    image: postgres:16-alpine
    container_name: gitlab_database
    restart: unless-stopped
    #ports:
    #  - "5434:5432"
    environment:
      POSTGRES_PASSWORD: "someSecurePassword123!!"
      POSTGRES_DB: gitlab
    volumes:
      - ./volumes/gitlab/database/16:/var/lib/postgresql/data
Code language: YAML (yaml)

I’ve highlighted the two changes.

We bump to postgres:16-alpine.

Note: Postgres:16 (not Alpine) has some problems.

And importantly, I am using a new volume directory for Postgres 16.

This is one way to keep the previous and the new containers completely separate, and is how we can easily roll back if needed.

Start Your New Docker Container

An easy one:

docker-compose up

I’d suggest doing this in a split terminal.

You want to be able to see the logs here.

What you are looking for is the line:

LOG:  database system is ready to accept connectionsCode language: HTTP (http)

Essentially this is a brand new instance of Postgres, completely unaware of your previous / existing data. A fresh start.

Again, after a little bit of time you should see some logs like this:

db_1  | PostgreSQL init process complete; ready for start up.
db_1  | 
db_1  | 2023-09-20 18:09:26.255 UTC [1] LOG:  starting PostgreSQL 16.0 on x86_64-pc-linux-musl, compiled by gcc (Alpine 12.2.1_git20220924-r10) 12.2.1 20220924, 64-bit
db_1  | 2023-09-20 18:09:26.255 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
db_1  | 2023-09-20 18:09:26.255 UTC [1] LOG:  listening on IPv6 address "::", port 5432
db_1  | 2023-09-20 18:09:26.260 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
db_1  | 2023-09-20 18:09:26.264 UTC [53] LOG:  database system was shut down at 2023-09-20 18:09:26 UTC
db_1  | 2023-09-20 18:09:26.267 UTC [1] LOG:  database system is ready to accept connectionsCode language: JavaScript (javascript)

This just confirms that Postgres 16 is online. Be sure to look for the exact version that you stated in your docker-compose.yaml file, and that you haven’t accidentally started the old container.

Copy The SQL Backup File In To Your New Container

A fiddly one this.

You now need to copy the SQL file you dumped from your host machine in to your new Postgres Docker container.

In order to do that, you need the container ID.

docker ps -a

CONTAINER ID   IMAGE                          
5cdfb4aace0f   postgres:16-alpine    Code language: Shell Session (shell)

I’ve removed the reams of other guff spat out by this command. Honestly, you need a 4K screen just to see the full output of a docker ps -a.

Now the command to copy the file over uses docker, not docker-compose.

And note the path / location at the end of the command:

docker cp 2023-09-20-Backup.sql 5cdfb4aace0f:/      
            
Successfully copied 7.68kB to 5cdfb4aace0f:/Code language: Shell Session (shell)

Obviously replace the file name and container ID with your values.

And if you so wish, change the location.

As it stands, this will copy the file to the container root.

Connect To Your Docker Container’s Shell

Switching back to using docker-compose now, we will execute an interactive bash session on the Postgres container:

docker-compose exec db /bin/bash

5cdfb4aace0f:/# ls -la
total 80
drwxr-xr-x    1 root     root          4096 Sep 20 15:24 .
drwxr-xr-x    1 root     root          4096 Sep 20 15:24 ..
-rwxr-xr-x    1 root     root             0 Sep 20 15:08 .dockerenv
-rw-rw-r--    1 1000     1000          5841 Sep 20 15:04 2023-09-20-Backup.sql
drwxr-xr-x    1 root     root          4096 Sep 15 22:26 bin
drwxr-xr-x    5 root     root           340 Sep 20 15:09 dev
drwxr-xr-x    2 root     root          4096 Aug  9 02:26 docker-entrypoint-initdb.d
drwxr-xr-x    1 root     root          4096 Sep 20 15:08 etc
drwxr-xr-x    2 root     root          4096 Aug  7 13:09 home
drwxr-xr-x    1 root     root          4096 Sep 15 22:26 lib
drwxr-xr-x    5 root     root          4096 Aug  7 13:09 media
drwxr-xr-x    2 root     root          4096 Aug  7 13:09 mnt
drwxr-xr-x    2 root     root          4096 Aug  7 13:09 opt
dr-xr-xr-x  621 root     root             0 Sep 20 15:09 proc
drwx------    2 root     root          4096 Aug  7 13:09 root
drwxr-xr-x    1 root     root          4096 Sep 15 22:26 run
drwxr-xr-x    1 root     root          4096 Sep 15 22:26 sbin
drwxr-xr-x    2 root     root          4096 Aug  7 13:09 srv
dr-xr-xr-x   13 root     root             0 Sep 20 15:09 sys
drwxrwxrwt    1 root     root          4096 Sep 15 22:26 tmp
drwxr-xr-x    1 root     root          4096 Sep 15 22:26 usr
drwxr-xr-x    1 root     root          4096 Aug  7 13:09 var
Code language: Shell Session (shell)

Very good, our SQL dump is on our running container.

Restore Your Database

Now you can restore your database.

Again the values you need here either come from your docker-compose.yaml file, or the Postgres Docker image environment defaults.

psql -U "app_user" -d "app_db" < 2023-09-20-Backup.sqlCode language: JavaScript (javascript)

Which would be based on the following docker-compose.yaml file:

# docker-compose.yaml

version: '3.9'
services:
  db:
    image: postgres:16-alpine
    ports:
      - "5445:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - ./16:/var/lib/postgresql/data
Code language: YAML (yaml)

This will then spit out a potentially very large amount of text as it fully plays back your SQL dump on screen.

Again, this process can take a while.

We Are Done! (Maybe)

OK, so at this point, depending your chosen version of Postgres, you may be done.

Earlier versions of Postgres did not suffer from this next issue.

Connect To Your New Database

The next thing to do is to connect to your newly upgraded Postgres database and … well, do whatever it is you do in your database.

You will very likely immediately know if things look right.

But before you can do that, you need to connect to your Postgres database.

Again, from the above docker-compose.yaml file we have:

# docker-compose.yaml

version: '3.9'
services:
  db:
    image: postgres:16-alpine
    ports:
      - "5445:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - ./16:/var/lib/postgresql/data
Code language: YAML (yaml)

So we should be able to connect to that database using port 5445.

I’m not going to document how I do this right here, but if you’re unsure, I use JetBrains DataGrip and I have documented the process to connect to a Postgres Docker container in this post.

As I say, depending on which version you just upgraded too, depends on whether you can immediately connect, or if you see an error.

Fixing Postgres 16 Authentication Errors

If you kept your logs open, you may have seen this:

db_1  | 2023-09-20 17:43:04.856 UTC [1] LOG:  database system is ready to accept connections
db_1  | 2023-09-20 17:43:09.065 UTC [28] FATAL:  password authentication failed for user "app_user"
db_1  | 2023-09-20 17:43:09.065 UTC [28] DETAIL:  User "app_user" does not have a valid SCRAM secret.
db_1  | 	Connection matched file "/var/lib/postgresql/data/pg_hba.conf" line 128: "host all all all scram-sha-256"
Code language: JavaScript (javascript)

Which is … unfortunate.

I don’t have a perfect fix for this.

Instead, I found a workaround on GitHub.

Applying The Workaround

OK, so with our Postgres 16 container up and running, we need to do the get another interactive terminal session underway:

docker-compose exec db /bin/bashCode language: Shell Session (shell)

Edit the Host-Based Authentication Configuration File

Now we need to edit the pg_hba.conf file:

vi /var/lib/postgresql/data/pg_hba.confCode language: JavaScript (javascript)

The change we need to make is at the very end of the file. You should be able to scroll to the bottom very quickly by mashing the ‘Page Down’ key. Other more vim-fu ways surely exist.

Once mashed down to the bottom, press the up arrow a few times to get to this bit:

We need to change the line:

host    all             all             127.0.0.1/32            trust

To read:

host    all             all             0.0.0.0/0            trust

This isn’t a vi tutorial, but use the cursor keys to get to that first 1, then press x several times to delete everything. Press i to insert, type in 0.0.0.0/0, then press esc, then type :wq.

Bosh.

Restart The Container

OK, now restarting the container is much easier than the previous step.

Type exit to get out of the container’s terminal, then do your usual docker-compose down / docker-compose up routine to get a new container instance up and running.

Alter The Password

You should now be able to connect to your Postgres container.

Remember our config?

# docker-compose.yaml

version: '3.9'
services:
  db:
    image: postgres:16-alpine
    ports:
      - "5445:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - ./16:/var/lib/postgresql/data
Code language: YAML (yaml)

So we should be able to get a DataGrip (or whatever) session open on that port:

Even though we can now connect, we still need to update the password to properly reset it to use scram-sha-256.

That’s a simple change:

ALTER USER app_user WITH PASSWORD 'my_top_secret_password';Code language: JavaScript (javascript)

Again, why do we do this?

Because:

# docker-compose.yaml

version: '3.9'
services:
  db:
    image: postgres:16-alpine
    ports:
      - "5445:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - ./16:/var/lib/postgresql/data
Code language: YAML (yaml)

These values must match.

Update The PG_HBA.CONF File Again

We aren’t done yet.

You now need to go back to the container’s terminal:

docker-compose exec db /bin/bash

Then edit the pg_hba.conf file again:

vi /var/lib/postgresql/data/pg_hba.confCode language: JavaScript (javascript)

And repeat the vi steps to update the file as follows:

Save, exit, restart the container, and finally, we are done.

Cleaning Up

OK, so if you have been following along, there are a couple of bits of clean up to do.

You will need to copy the new Database config from your current docker-compose.yaml file in to your docker-compose.yaml.bak, and switch those files around.

Also if you haven’t already deleted the SQL dump file from the Database container, or deleted the container entirely, then do that also. No point keeping that on there.

I think that’s all that is left.

Anyway you should now be rocking a brand new, upgraded Postgres docker container.

Wasn’t that completely painless? :/