Gitlab 16 incompatible with PostgreSQL 12, requires >=13

[lekoder]

docker-compose.yml contained in the 16 release uses sameersbn/postgresql:12-20200524 as a PostgreSQL container. When starting the containers, we get following error:

You are using PostgreSQL 12.3 for the ci database, but this version of GitLab requires PostgreSQL >= 13.

Please upgrade your environment to a supported PostgreSQL version.
See https://docs.gitlab.com/ee/install/requirements.html#database for details.

Despite the message, Gitlab itself appear to work at a cursory glance, but it will probably cause problems further down the line.

[tDo]

Had the same question (Though attached to the actual pull request discussion for 16.0.0: #2768 (comment))

I assume this will most likely require an update to https://github.com/sameersbn/docker-postgresql
as this is currently the default image on the docker-compose parts (And as far as I've understood also
contains auto-upgrade capabilities?)

[sachilles]

We'll provide a workaround. But this will take some time.

[fmiqbal]

I just pg_dump the whole database, and re import it to bitnami/postgresql, so far, everything seems good. Granted our database size only 300mb after dump.

Nothing fancy, just following this https://docs.gitlab.com/ee/administration/postgresql/moving.html

[xps2]

#2572

[f-io]

We moved to official postgres docker image and also used pg_dump.
Database dump ~6G, almost 8y of collected garbage.

Just make sure that the required extensions are available in your new database.

I know there are some convenient features within custom postgres images, but how often do you plan to migrate to a new major version? In my experience using a custom image will most likely lead to more work than it saves.

#dump

docker exec -i postgresql_current /bin/bash -c "PGPASSWORD=……. pg_dump --username gitlab gitlabhq_production" > ./postgres_backup/gitlab_dump_$(date +%y-%m-%d-%H:%M).sql

#import

docker exec -i postgresql_new /bin/bash -c "PGPASSWORD=…….  psql --username gitlab gitlabhq_production" < ./postgres_backup/gitlab_dump__.sql

[Gaibhne]

@sachilles a good first step would be - if you have the permissions needed at all, if not, the discussion on that topic is probably moot - to provide a PQ13 version of sameersbn/postgresql that people could test drive with a manual upgrade process. If that isn't possible (I'm not sure the original author is still around at all), we should probably discuss the best part forward. A fork, maybe ?

It's probably not very productive if people report back on their issues with random hand rolled deployments of official PG13 images and/or custom patched/upgraded versions of sameersbn/postgresql and will just end up muddling the discussion.

[tDo]

We moved to official postgres docker image and also used pg_dump. Database dump ~6G, almost 8y of collected garbage.

Just make sure that the required extensions are available in your new database.

I know there are some convenient features within custom postgres images, but how often do you plan to migrate to a new major version? In my experience using a custom image will most likely lead to more work than it saves.

#dump

docker exec -i postgresql_current /bin/bash -c "PGPASSWORD=……. pg_dump --username gitlab gitlabhq_production" > ./postgres_backup/gitlab_dump_$(date +%y-%m-%d-%H:%M).sql

#import

docker exec -i postgresql_new /bin/bash -c "PGPASSWORD=…….  psql --username gitlab gitlabhq_production" < ./postgres_backup/gitlab_dump__.sql

In addition to that (and supporting @Gaibhne prediction, that people might report back with any kinds of issues on their hand rolled deployments) there are some small things you can stumble on when using the official postgres image and porting it to the docker-compose of this project.

For example the sameerssbn/postgresqlimage defines it's volume as

volumes:
    - postgresql-data:/var/lib/postgresql:Z

which could be directly ported to official images, but results in the data not being persisted, since
the official image wants the volume mount at /var/lib/postgresql/data
(See https://stackoverflow.com/a/76217607 for an explanation). And while the original definition would start and run
fine, you would then lose the data after restarting the containers... (One small thing I ran into when switching to the official image on a test-setup).

Still the official image works fine if you read the docs carefully, but as mentioned before this could be a source for
future issues which might be hard to debug when using custom images.

[bor8]

We'll provide a workaround. But this will take some time.

When and where will that be available?

Alongside: Please take into account that people with http connect proxies can have trouble upgrading Postgres - like last time: #2171

[sachilles]

Sorry for being slow. Actually I wanted to have a look at the special features of the sameersbn/postgresql image this week. Unfortunately, I was very busy this week, so I did not get around to it.

However, I guess it makes more sense to use an official postgres image and thus, the documentation needs to be updated.

[kkimurak]

Note: Here are some special features of sameersbn/postgresql:

• Enable extensions via environment variable DB_EXTENSION
  • The process is just issuing command to database (CREATE EXTENSION IF NOT EXISTS ..;). This can be transplant to docker-gitlab side, I think.
  • (updated 2023-10-21) GitLab's DB structure initialization will issue the query. btree_gits is added by MR gitlab-org/gitlab!33340, especially gitlab-org/gitlab@b1dfb4a9. So we may not need to do anything about this.
• Automatically migrate old database on launching new version of database
  There doesn't seem to be much we can do about this.

[bor8]

How I did it - or very inofficial instructions for Docker Compose and Postgres image from Bitnami.

Credits to @f-io.

1. Shutdown approx. 4 containers: docker compose down. Make sure you are using the newest software with the new syntax; without the - between docker and compose.
2. Rename postgresql: to postgresql-old: in docker-compose.yml file.
3. Insert new section into docker-compose.yml file:

    postgresql:
        environment:
            - POSTGRESQL_USERNAME=gitlab
            - POSTGRESQL_PASSWORD=***
            - POSTGRESQL_DATABASE=gitlabhq_production
        image: bitnami/postgresql:13.11.0
        networks:
            ***:
        restart: unless-stopped
        volumes:
            - /etc/apt/apt.conf:/etc/apt/apt.conf
            ###- ./volume_postgresql/:/bitnami/postgresql/
        # docker compose exec postgresql psql -U gitlab gitlabhq_production
        # CREATE EXTENSION IF NOT EXISTS pg_trgm;
        # CREATE EXTENSION IF NOT EXISTS btree_gist;

4. Power up postgresql-old: docker compose up -d postgresql-old.
5. Do the backup: sudo bash -c 'docker compose exec postgresql-old /bin/bash -c "PGPASSWORD=*** pg_dump --username gitlab gitlabhq_production" > ./gitlab_dump_.sql'. Notice the space at the beginning of the command. On some distros this will prevent the command saved to the bash history. Useful because of the password.
6. Shutdown again with docker compose down.
7. Comment the old section and rename the volume:

    #postgresql-old:
    #    environment:
    #        - DB_USER=gitlab
    #        - DB_PASS=***
    #        - DB_NAME=gitlabhq_production
    #        - DB_EXTENSION=pg_trgm,btree_gist
    #    image: sameersbn/postgresql:12-20200524
    #    networks:
    #        ***:
    #    restart: unless-stopped
    #    volumes:
    #        - /etc/apt/apt.conf:/etc/apt/apt.conf
    #        - ./volume_postgresql_old:/var/lib/postgresql:Z

8. Rename the old volume: sudo mv volume_postgresql volume_postgresql_old.
9. Power up the new database: docker compose up -d postgresql.
10. Copy out the conf and data folder:

POSTGRES_CONTAINER_ID=$(docker compose ps --format json | grep postgres | grep -oP '"ID":"[0-9a-fA-F]+"' | grep -oP '[0-9a-fA-F]+' | tail -n 1)
[ $(echo -n $POSTGRES_CONTAINER_ID | wc -c) -eq 64 ] && echo 'Fine, got Postgres container ID.' || echo 'Something went wrong!?'
# Do the copy:
sudo docker cp ${POSTGRES_CONTAINER_ID}:/bitnami/postgresql/ .
# Rename the new host folder:
sudo mv postgresql volume_postgresql
cd volume_postgresql
# Fix permissions:
sudo chown 1001:root data -R

11. Remove the comment ### from new volume: section in docker-compose.yml file.
12. Shutdown again: docker compose down.
13. Power up the new database again: docker compose up -d postgresql.
14. Activate 2 needed extensions:

docker compose exec postgresql psql -U gitlab gitlabhq_production
# Enter password
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE EXTENSION IF NOT EXISTS btree_gist;

15. Do the restore: docker compose exec -T postgresql /bin/bash -c "PGPASSWORD=*** psql --username gitlab gitlabhq_production" < ./gitlab_dump_.sql
16. Shutdown again: docker compose down.
17. Start in foreground: docker compose up and see if it works...
18. Break foreground with [Ctrl]+[C] and start in background: docker compose up -d.

[Aoiujz]

Before merging this PR sameersbn/docker-postgresql#158, you can use this zuojiazi/postgresql image instead of sameersbn/postgresql

[jfreax]

Before merging this PR sameersbn/docker-postgresql#158, you can use this zuojiazi/postgresql image instead of sameersbn/postgresql

Is there a reason to not link the source repository? It's a hard pass on my side. Otherwise, nice initiative.

[davidglassborow]

The latest image: sameersbn/postgresql:latest is now pg14.8 and stops the warnings on gitlab startup

[omueller]

Good to know @davidglassborow and @sachilles, as the README.md and sample docker-compose.yml (https://github.com/sameersbn/docker-gitlab/blob/master/docker-compose.yml) are still mentioning image: sameersbn/postgresql:12-20200524

So we can "simply" replace sameersbn/postgresql:12-20200524 by sameersbn/postgresql:latest, or is still in testing ? Sticking to 15.11.x in the mean time.

[Kiina]

So we can "simply" replace sameersbn/postgresql:12-20200524 by sameersbn/postgresql:latest, or is still in testing ? Sticking to 15.11.x in the mean time.

I tried going to the latest tag like that, in the past (aka pre 12) it would migrate the data. Now my gitlab instance is empty and select * from projects; and select * from users; returns nothing. Going back to postgres 12 at least made the data available but now gitlab wont start. So be careful with the update, it might not be as easy as just replacing the postgres version. You might need to make a dump and restore it.

[omueller]

Thanks a lot for your comment @Kiina, it's somehow what I expected. So no production server upgrade for now in any case :) I hope your rollback will be fine again soon !

[davidglassborow]

Not saying it's right but was already using sameersbn/postgresql:latest in docker-compose, so I just did docker-compose pull and then rebuild the containers.

The logs show PG detecting the change, and auto updating:

postgresql_1  | Initializing datadir...
postgresql_1  | Initializing certdir...
postgresql_1  | Initializing logdir...
postgresql_1  | Initializing rundir...
postgresql_1  | Setting resolv.conf ACLs...
postgresql_1  | Initializing database...
postgresql_1  | ‣ Migrating PostgreSQL 12 data to 14...
postgresql_1  | ‣ Installing PostgreSQL 12...
postgresql_1  | debconf: delaying package configuration, since apt-utils is not installed
postgresql_1  | ‣ Migration in progress. Please be patient...Performing Consistency Checks
postgresql_1  | -----------------------------
postgresql_1  | Checking cluster versions                                   ok
postgresql_1  | Checking database user is the install user                  ok
postgresql_1  | Checking database connection settings                       ok
postgresql_1  | Checking for prepared transactions                          ok
postgresql_1  | Checking for system-defined composite types in user tables  ok
postgresql_1  | Checking for reg* data types in user tables                 ok
postgresql_1  | Checking for contrib/isn with bigint-passing mismatch       ok
<snip>
postgresql_1  |   /var/lib/postgresql/12/main/base/16400/2683
postgresql_1  |                                                             ok
postgresql_1  | Setting next OID for new cluster                            ok
postgresql_1  | Sync data directory to disk                                 ok
postgresql_1  | Creating script to delete old cluster                       ok
postgresql_1  | Checking for extension updates                              notice
postgresql_1  | 
postgresql_1  | Your installation contains extensions that should be updated
postgresql_1  | with the ALTER EXTENSION command.  The file
postgresql_1  |     update_extensions.sql
postgresql_1  | when executed by psql by the database superuser will update
postgresql_1  | these extensions.
postgresql_1  | 
postgresql_1  | 
postgresql_1  | Upgrade Complete
postgresql_1  | ----------------
postgresql_1  | Optimizer statistics are not transferred by pg_upgrade.
postgresql_1  | Once you start the new server, consider running:
postgresql_1  |     /usr/lib/postgresql/14/bin/vacuumdb --all --analyze-in-stages
postgresql_1  | 
postgresql_1  | Running this script will delete the old cluster's data files:
postgresql_1  |     ./delete_old_cluster.sh
postgresql_1  | 
postgresql_1  | Configuring hot standby...
postgresql_1  | ‣ Setting postgresql.conf parameter: wal_level = 'hot_standby'
postgresql_1  | ‣ Setting postgresql.conf parameter: max_wal_senders = '16'
postgresql_1  | ‣ Setting postgresql.conf parameter: checkpoint_segments = '8'
postgresql_1  | ‣ Setting postgresql.conf parameter: wal_keep_segments = '32'
postgresql_1  | ‣ Setting postgresql.conf parameter: hot_standby = 'on'
postgresql_1  | ‣ Setting postgresql.conf parameter: data_directory = '/var/lib/postgresql/14/main'
postgresql_1  | ‣ Setting postgresql.conf parameter: log_directory = '/var/log/postgresql'
postgresql_1  | ‣ Setting postgresql.conf parameter: log_filename = 'postgresql-14-main.log'
postgresql_1  | ‣ Setting postgresql.conf parameter: ssl = 'off'
postgresql_1  | Creating database user: gitlab
postgresql_1  | Creating database: gitlabhq_production...
postgresql_1  | ‣ Loading pg_trgm extension...
postgresql_1  | ‣ Loading btree_gist extension...
postgresql_1  | ‣ Granting access to gitlab user...
postgresql_1  | Starting PostgreSQL 14...
postgresql_1  | 2023-06-27 07:07:52.883 UTC [1] LOG:  starting PostgreSQL 14.8 (Ubuntu 14.8-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.5.0-3ubuntu1~18.04) 7.5.0, 64-bit
postgresql_1  | 2023-06-27 07:07:52.883 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432
postgresql_1  | 2023-06-27 07:07:52.883 UTC [1] LOG:  listening on IPv6 address "::", port 5432
postgresql_1  | 2023-06-27 07:07:52.884 UTC [1] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
postgresql_1  | 2023-06-27 07:07:52.889 UTC [2549] LOG:  database system was shut down at 2023-06-27 07:07:52 UTC
postgresql_1  | 2023-06-27 07:07:52.892 UTC [1] LOG:  database system is ready to accept connections

Gitlab is then working fine for us, no data loss, and the admin page is reporting the correct version:

[Kiina]

Yeah I had to dump the old db, import it into the new version and then start everything and execute the db:migrate rake task manually. No idea why it skipped over one of the migrations but it lead to error 500 on load. Afterwards all seems fine. Maybe that's just an issue with my installation.

[pejmanS21]

Hello everyone, I was wondering that can I use use sameersbn/postgres:14-20230628 for my gitlab? since it released but not updated in gitlab-docker-compose file!

[Deninc]

I'm seting up a new instance and the latest sameersbn/postgresql:15-20230628 didn't work.
Using sameersbn/postgresql:14-20230628 worked.

[pejmanS21]

I have a GitLab instance from version 13.0.6, now to upgrade from version 12 Postgres to a newer version apparently it only needs to change the sameersbn/postgres version in docker-compose and wait until the migration is finished!

% docker compose exec -it postgresql ls

• migration in progress

drwx------ 3 postgres postgres 4096 May 22 10:12 12.migrating
-rwx------ 1 postgres postgres   48 Jul  7 07:50 delete_old_cluster.sh
-rw------- 1 postgres postgres  100 Jul  7 07:50 update_extensions.sql

and then after about 2 minutes

drwx------ 3 postgres postgres 4096 May 22 10:12 12
drwx------ 3 postgres postgres 4096 Jul  7 07:50 15
-rwx------ 1 postgres postgres   48 Jul  7 07:50 delete_old_cluster.sh
-rw------- 1 postgres postgres  100 Jul  7 07:50 update_extensions.sql

after this, the only thing is refreshing the browser to see the GitLab login page!

[etlam]

Is it safe to use sameersbn/postgresql:latest for upgrading? Or do we need to do steps 12 > 14 > 15?

[sachilles]

Since major versions of PostgreSQL might be related to breaking changes I'd strongly suggest to migrate from major version to major version, i.e. 12 then 13 then 14 then 15.

[drjoju]

I just updated the PostgreSQL version in my GitLab Docker environment. I went from using the image 'sameersbn/postgresql:12-20200524' to 'sameersbn/postgresql:14-20230628', and then upgraded again to 'sameersbn/postgresql:15-20230628'. During each transition, I performed a Docker Compose up.

PostgreSQL seamlessly detected the need for an upgrade when I switched to the newer versions, and I'm pleased to report that the entire process went smoothly without any issues.

So, to avoid any problem seems to be a good idea to upgrade from 12 to 14 and then 15.

[jirsbek]

I've just updated my GitLab self hosted Docker composition with sameersbn/postgresql:14-20230628 and everything seems fine so I'm staying with postgresql 14. As stated at https://about.gitlab.com/handbook/engineering/development/enablement/data_stores/database/postgresql-upgrade-cadence.html it is "Optionally Supported Version" for GitLab 16.

Upgrade went well, GitLab instance just exited during the upgrade as it was probably waiting too long for postgresql instance.

[gzstokiyama]

I just updated the PostgreSQL version in my GitLab Docker environment. I went from using the image 'sameersbn/postgresql:12-20200524' to 'sameersbn/postgresql:14-20230628', and then upgraded again to 'sameersbn/postgresql:15-20230628'. During each transition, I performed a Docker Compose up.

PostgreSQL seamlessly detected the need for an upgrade when I switched to the newer versions, and I'm pleased to report that the entire process went smoothly without any issues.

So, to avoid any problem seems to be a good idea to upgrade from 12 to 14 and then 15.

I tried your suggestion, but failed ,log says

ERROR:  relation "postgres_partitioned_tables" does not exist at character 85

[etlam]

I just updated the PostgreSQL version in my GitLab Docker environment. I went from using the image 'sameersbn/postgresql:12-20200524' to 'sameersbn/postgresql:14-20230628', and then upgraded again to 'sameersbn/postgresql:15-20230628'. During each transition, I performed a Docker Compose up.

PostgreSQL seamlessly detected the need for an upgrade when I switched to the newer versions, and I'm pleased to report that the entire process went smoothly without any issues.

So, to avoid any problem seems to be a good idea to upgrade from 12 to 14 and then 15.

Works.

[fdcastel]

Thank you everyone who has been contributing to this discussion! 🥇

I just went by a successful migration from 14.10.4 to 16.3.3 following the steps below:

1. Upgraded PostgreSQL from sameersbn/postgresql:12-20200524 to sameersbn/postgresql:14-20230628 (thanks @drjoju -- comment)

2. DID NOT upgrade to PostgreSQL 15 yet, since 14 is still supported (thanks @jirsbek -- comment)

3. I followed the upgrade path from this invaluable GitLab tool. In my specific case I did the following steps:

   • upgraded to 14.10.5
   • upgraded to 15.0.3 (since there is not a sameersbn/15.0.5 which was recommended by upgrade tool)
   • upgraded to 15.4.6
   • upgraded to 15.11.13
   • upgraded to 16.3.3

In each step I started up the image; followed the docker-compose logs -f output to see if everything was upgraded correctly; and went to GitLab Admin Area to confirm that everything was running fine.

Also, please do click on each "version box" of the upgrade path tool, which will show you important information for each case. e.g.:

Best regards!

[mradeschnig]

The upgrade of psotgresql from 12 to 14 does not finish, I had to add the following lines
to the services postgresql:
security_opt:
- 'seccomp:unconfined'

Then I could upgrade :
from sameersbn/postgresql:12-20200524 to sameersbn/postgresql:14-20230628
and then to sameersbn/postgresql:15-20230628

Otherwise the upgrade did nbot finish.