In the world of GitLab and PostgreSQL, compatibility is crucial. When using GitLab Omnibus, you’ll find it ships with PostgreSQL 9.6. However, if you’re leveraging an external PostgreSQL database container, it’s essential to adhere to a key requirement: GitLab only supports the exact version of PostgreSQL internally and externally. This subtlety often gets lost in the shuffle, especially for those new to the ecosystem. To put it plainly, if you’re running a Docker installation of GitLab, you must use PostgreSQL 9.6—no more, no less.

TL;DR:

To address the version mismatch error, run a Python script against your PostgreSQL 11 dump to remove AS integer from CREATE SEQUENCE statements, then proceed with the import as usual.

Setting the Scene

Consider a docker-compose.yml file that resembles the following (note that some details are intentionally obfuscated for clarity):

version: '3.5'

services:

  gitlab:
    image: 'gitlab/gitlab-ce:latest'
    container_name: "gitlab"
    restart: always
    hostname: 'git.sebastientaggart.com'
    environment:
      GITLAB_OMNIBUS_CONFIG: |
        external_url 'https://git.sebastientaggart.com'
        # Other configurations...
    ports:
      - '3000:3000'
      - '10022:22'
    volumes:
      - './data/gitlab/config:/etc/gitlab'
      - './data/gitlab/logs:/var/log/gitlab'
      - './data/gitlab/data:/var/opt/gitlab'

  postgresql-gitlab:
    image: 'postgres:11.1-alpine'
    container_name: "gitlab_postgresql"
    restart: always
    environment:
      - POSTGRES_USER=gitlab
      - POSTGRES_PASSWORD={PASSWORD}
      - POSTGRES_DB=gitlab
    volumes:
      - ./data/postgresql-gitlab:/var/lib/postgresql/data

If you attempt to back up the database using the typical command, such as:

docker exec -t gitlab gitlab-rake gitlab:backup:create

You might encounter the following error:

Dumping PostgreSQL database gitlab ... pg_dump: server version: 11.1; pg_dump version: 9.6.11
pg_dump: aborting because of server version mismatch

Extracting the Database Dump

To resolve this issue, you’ll need to access the PostgreSQL container and upgrade the PostgreSQL client to version 11. Here’s how:

echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ xenial-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
apt install wget ca-certificates
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt update
apt install postgresql-client
ln -s /usr/bin/psql /usr/bin/pg_dump /opt/gitlab/bin/

With the client upgraded, you can now carry out the export with the following command:

docker exec -t gitlab_postgresql pg_dumpall -c -U gitlab > dump_11_$(date +%d-%m-%Y"_"%H_%M_%S).sql

Remember, this command is executed directly against the PostgreSQL container, not through the usual rake command, as we are preparing to downgrade PostgreSQL back to 9.6.

Managing Data Between Operations

In my scenario, I’ve been using a volume for the PostgreSQL data directory to keep the database intact between container resets. Before switching to an older PostgreSQL version in your docker-compose.yml, you’ll need to clean out the data directory:

sudo rm -rf /opt/docker/gitlab/data/postgresql-gitlab/*

Downgrading from 10 to 9

Exporting from PostgreSQL 11 and importing into 10 usually goes smoothly. However, moving from 10 to 9 can lead to database errors. Fortunately, a simple Python script can help us navigate this issue, as referenced in this Stack Overflow discussion:

#!/usr/bin/env python3
import sys

# Downgrades pg_dump 10 script to 9.x
# removing 'AS integer' from 'CREATE SEQUENCE' statement

inside_create_sequence = False
for row in sys.stdin.readlines():
    if inside_create_sequence and row.strip().lower() == 'as integer':
        pass
    else:
        print(row, end='', flush=True)
    inside_create_sequence = row.strip().startswith('CREATE SEQUENCE ')

Save this script as pgdump_10_to_9.py, make it executable, and run it:

cat dump_10_12-04-2019_15_09_16.sql | ./pgdump_10_to_9.py > dump_9_compatible.sql

After that, update your docker-compose.yml to leverage a PostgreSQL 9.6 image and import your cleaned-up dump:

cat dump_9_compatible.sql | docker exec -i gitlab_postgresql psql -U gitlab

And just like that, you’ve successfully downgraded to a version of PostgreSQL that’s more compatible with GitLab.

By approaching the downgrade methodically, you can navigate the complexities of version compatibility with confidence. Remember, clarity in your setup can save you from numerous headaches down the line.