Upgrade to PostgreSQL 13

[sue445]

PostgreSQL 12 is deprecated since GitLab 15.0
https://about.gitlab.com/releases/2022/05/22/gitlab-15-0-released/#release-deprecations

docker-gitlab is using 12, so it would be better to upgrade.
https://github.com/sameersbn/docker-gitlab/blob/14.10.3/docker-compose.yml#L14

[xps2]

https://gitlab.com/gitlab-org/gitlab/-/issues/349185

[gjrtimmer]

I successfully updated to PostgreSQL:13, getting ready for upgrade to 14.
I'm using my own PostgreSQL container for this. gjrtimmer/docker-postgresql
(Disclaimer: I'm the maintainer / owner of the mentioned container, I did model some of this container upon the sameersbn/postgresql container, years/months ago)

The container I've build also easily allows for configuration of a cluster, just start a second one and link it. Everything is configured with environment variables.

One of the best things about the container is that it performs automatic migrations.

I was previously also using the sameersbn/postgresql container.

---

So here is the guide on how switched.

1. Shutdown Gitlab & Database
2. BACKUP your postgresql data directory
3. Rename data folder from 12 to 12.12 my container uses also the minor in the data folder due to auto migrations.
4. Update the docker-compose file and set the correct path of the data folder. With the new container everything should be mapped to /config in the container. I'm using a host mount.

volumes:
      - /volume1/Docker/gitlab/data/postgresql:/config

Inside the host mount you should create a directory data in which you place your 12.12 folder.

Result on host:
/volume1/Docker/gitlab/data/postgresql/data/12.12/main

5. Next step I added some additional environment variable PUID and PGID because it simply makes my live easier with backups.
6. Create a file PG_VERSION_FULL in the main data directory of your PostgreSQL. This file is normally used by the container for its automigration process. in the file you only set the version number 12.12 without newline.

cd /volume1/Docker/gitlab/data/postgresql/data/12.12/main
echo "12.12" > PG_VERSION_FULL

Note
Because I'm using PUID and PGID I'm able to use the same permissions on the host as in the container. Make sure you set the owner of the file to the same as the other psotgresql files.

7. Update postgresql.conf, because my container works from the volume mount of /config you need to update the postgresql.conf to make sure the paths are set correctly.
   Update the following configurations keys.

data_directory = '/config/data/12.12/main'
log_directory = '/config/logs'

Also do not worry about additional directories the container will auto configure everything.

8. Update docker-compose to use the new image: image: gjrtimmer/postgresql:12

The trick is to have the database start correctly with the same major version with a different container.

When you have the database up & running upgrading to version 13 is only changing the image version in the docker-compose. If you have done all the steps correctly it will auto upgrade to version 13 (if there is enough diskspace)

TIP
When upgrading to version 13 it will create a sql file update_extension which you have to run against the database to upgrade the extensions, before starting gitlab. Should be as easy as move the file to the init.db.d directory and restarting the container. Will update about that later.

Important

My advice if you are thinking about updating

• Shutdown GitLab
• Copy PostgreSQL data directory to save location (psql.bak)
• Make second copy as Working Copy (psql.work)
• Start GitLab
• Now Start playing the working copy to upgrade it with the new container, each time you screw up or forget something you simply remove the working copy and make a new working copy from the clean backup you have made. This way you can safely play with this container.

Final Note

The container I've built can easily be used to spin up and turn you PostgreSQL into a cluster. Checkout the examples int he earlier mentioned repository. Overall should be simple procedure, took me about 10m to switch containers.

[gjrtimmer]

@sue445 If your going to try, please let me know if all the steps were clear and you experience.

[sue445]

@gjrtimmer I'm sorry

My company is currently planning a GitLab migration.

We are currently using sameersbn/docker-gitlab and sameersbn/postgresql:12, but after the migration I plan to use official Docker images (gitlab/gitlab-ce) and fully managed PostgreSQL (GCP Cloud SQL).

Therefore, I don't plan to upgrade the container version of PostgreSQL.

[learn0208]

This is how I solved the problem of upgrading the PG version to 13.

FROM sameersbn/gitlab:14.0.5

RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -

RUN apt update -y

RUN DEBIAN_FRONTEND=noninteractive apt purge postgresql-12 postgresql-client-12 postgresql-contrib-12 -y
RUN apt remove postgresql-12 postgresql-client-12 postgresql-contrib-12 -y

RUN DEBIAN_FRONTEND=noninteractive apt install --no-install-recommends postgresql-13 postgresql-client-13 postgresql-contrib-13 -y \
  && rm -rf /var/lib/apt/lists/*

[sramazzina]

Cloned the sameersbn/docker-postgresql repo and easily upgraded to version 14 by changing the value of the PG_VERSION environment variable

[Basti-Fantasti]

@sramazzina can you please give some further explanations on what you exactly did to upgrade the PG12 to 14?
did you also have to do some changes to the docker-compose file and how was the automatic migration done?

Thanks in advance

EDIT: Ah - I saw that you already made a PR to the original postgresql repo.
So when the PR is merged it should/would be possible to update the original sameersbn/postgresql container. This would be perfect.

[lyenliang]

@gjrtimmer I'm sorry

My company is currently planning a GitLab migration.

We are currently using sameersbn/docker-gitlab and sameersbn/postgresql:12, but after the migration I plan to use official Docker images (gitlab/gitlab-ce) and fully managed PostgreSQL (GCP Cloud SQL).

Therefore, I don't plan to upgrade the container version of PostgreSQL.

How did you migrate from sameersbn/docker-gitlab to gitlab/gitlab-ce? Is there any tutorial for the migration?

[sue445]

@lyenliang

Is there any tutorial for the migration?

No, everything is my full scratch 😇

I have written all about the GitLab migration below. (But in Japanese)

• 1st part. https://inside.pixiv.blog/2022/11/29/110000
• 2nd part. https://inside.pixiv.blog/2022/12/20/113000
• 3rd part. https://inside.pixiv.blog/2022/12/22/110000

[lyenliang]

Cloned the sameersbn/docker-postgresql repo and easily upgraded to version 14 by changing the value of the PG_VERSION environment variable

I tried to upgrade sameersbn/docker-postgresql's version from 12 to 13 by changing PG_VERSION environment variable. But I got the following error messages after launching PostgreSQL container:

‣ Migrating PostgreSQL 12 data to 13...
‣ Installing PostgreSQL 12...
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8
E: The repository 'http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease' is not signed.
W: http://security.ubuntu.com/ubuntu/dists/jammy-security/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://security.ubuntu.com/ubuntu/dists/jammy-security/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://security.ubuntu.com/ubuntu jammy-security InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://security.ubuntu.com/ubuntu jammy-security InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-updates/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-updates/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy-updates InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy-updates InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-backports/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-backports/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy-backports InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy-backports InRelease' is not signed.
E: Problem executing scripts APT::Update::Post-Invoke 'rm -f /var/cache/apt/archives/*.deb /var/cache/apt/archives/partial/*.deb /var/cache/apt/*.bin || true'
E: Sub-process returned an error code
ERROR! Failed to install PostgreSQL 12. Exiting...

[Floyddotnet]

Look at this: #2771 (comment)
this works for me.

And alternaitve: #2771 (comment)
i didn't try this because at this time i did the upgrade a newer image was not available

[lyenliang]

This is how I solved the problem of upgrading the PG version to 13.

FROM sameersbn/gitlab:14.0.5

RUN curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -

RUN apt update -y

RUN DEBIAN_FRONTEND=noninteractive apt purge postgresql-12 postgresql-client-12 postgresql-contrib-12 -y
RUN apt remove postgresql-12 postgresql-client-12 postgresql-contrib-12 -y

RUN DEBIAN_FRONTEND=noninteractive apt install --no-install-recommends postgresql-13 postgresql-client-13 postgresql-contrib-13 -y \
  && rm -rf /var/lib/apt/lists/*

Do you use the image created by this Dockerfile as the image of postgresql service in docker-compose.yml?

[modus-operandi]

Cloned the sameersbn/docker-postgresql repo and easily upgraded to version 14 by changing the value of the PG_VERSION environment variable

I tried to upgrade sameersbn/docker-postgresql's version from 12 to 13 by changing PG_VERSION environment variable. But I got the following error messages after launching PostgreSQL container:

‣ Migrating PostgreSQL 12 data to 13...
‣ Installing PostgreSQL 12...
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://apt.postgresql.org/pub/repos/apt/dists/jammy-pgdg/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 7FCC7D46ACCC4CF8
E: The repository 'http://apt.postgresql.org/pub/repos/apt jammy-pgdg InRelease' is not signed.
W: http://security.ubuntu.com/ubuntu/dists/jammy-security/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://security.ubuntu.com/ubuntu/dists/jammy-security/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://security.ubuntu.com/ubuntu jammy-security InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://security.ubuntu.com/ubuntu jammy-security InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-updates/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-updates/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy-updates InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy-updates InRelease' is not signed.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-backports/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2012-cdimage.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: http://archive.ubuntu.com/ubuntu/dists/jammy-backports/InRelease: The key(s) in the keyring /etc/apt/trusted.gpg.d/ubuntu-keyring-2018-archive.gpg are ignored as the file is not readable by user '_apt' executing apt-key.
W: GPG error: http://archive.ubuntu.com/ubuntu jammy-backports InRelease: The following signatures couldn't be verified because the public key is not available: NO_PUBKEY 871920D1991BC93C
E: The repository 'http://archive.ubuntu.com/ubuntu jammy-backports InRelease' is not signed.
E: Problem executing scripts APT::Update::Post-Invoke 'rm -f /var/cache/apt/archives/*.deb /var/cache/apt/archives/partial/*.deb /var/cache/apt/*.bin || true'
E: Sub-process returned an error code
ERROR! Failed to install PostgreSQL 12. Exiting...

Figured it out. This is another annoying problem with Ubuntu Jammy containers requiring --security-opt seccomp=unconfined with older versions of docker. Should have known.