I’m using a PostgreSQL database with gitea running in docker as per https://docs.gitea.com.
I’m having trouble finding documentation in any web searches on how to upgrade the database version being used. I have attempted to increase my postgresql version number in my docker-compose.yml, but it always breaks gitea. I’ve been successful testing upgrading gitea itself this way, but not with upgrading postgresql.
Any help or suggestions is greatly appreciated.
Postgres containers cannot be updated as easily as other containers. At least not if the major version changes. To update Postgres to a new major version, you must first make a dump of the DB, then start the new Postgres version and then import the dump of the old version. It is best to create a new volume for the new DB container. Then you still have the old one available as a backup / fallback solution.
This has less to do with Gitea. Gitea runs smoothly with the current Postgres version 16.
Thank you for your reply @p7k
I followed the steps here Upgrading A PostgreSQL Database Running In Docker, which explained how to dump the DB and then import the data into it.
It appears to have worked to upgrade the postgresql version running, but after I login to my gitea instance the dashboard is empty and it was not before the upgrade. When I stop/start it again to use the older postgresql, there is no issue with the dashboard. Any ideas?
Found this tool, GitHub - tianon/docker-postgres-upgrade: a PoC for using "pg_upgrade" inside Docker -- learn from it, adapt it for your needs; don't expect it to work as-is!, which appears to work. Will be further testing and documenting the process to share here as I know I’m not the only one running a psql docker with gitea.
I was able to upgrade PostgreSQL running in Docker using this tool, GitHub - tianon/docker-postgres-upgrade: a PoC for using "pg_upgrade" inside Docker -- learn from it, adapt it for your needs; don't expect it to work as-is!
I had to make the following changes to get this working for gitea, as the above tool is expecting a specific directory structure for it to function correctly:
Had to create create ~/gitea/postgres/9.6/data and rsync ~/gitea/postgres/* to it and then make sure the permissions remained the same:
# chown -R systemd-coredump:systemd-coredump 9.6/
# chmod 700 9.6
# chmod 700 9.6/data
Then had to change docker-compose.yml from what it is to point to this new directory:
“- ./postgres/9.6/data:/var/lib/postgresql/data”
I then started the gitea and postgresql dockers to verify that they were still functioning with this new directory structure, which they were.
I then ran the tool to migrate psql 9.6 to 15. You can change the old and new postgresql versions in the command to whichever you are upgrading to & from.
$ docker run --rm -e PGUSER=gitea -e POSTGRES_INITDB_ARGS=--username=gitea -v "$PWD/postgres":/var/lib/postgresql tianon/postgres-upgrade:9.6-to-15 --link
Next modify the pg_hba.conf file for psql 15 with config I had in 9.6 as follows:
host all all all md5Then docker pull "postgres:15" to pull down the new postgresql docker version.
Then edit your docker-compose.yml as follows:
Now start it up and your gitea will be running the newer version (15) via docker. If it does not work for some reason, then troubleshoot accordingly by running the following:
docker logs --tail 100 gitea_db_1
Thanks! These are great suggestions!
Just to add another solution here that I stumbled upon that I am currently using:
https://hollo.me/devops/upgrade-postgresql-database-with-docker.html
In a nutshell, you add a new service to your docker-compose file with the postgres image that you wanna migrate to and a new managed volume. Then you dump your tables from your old db and read them into the new db. You switch over to the new postgres by updating the postgres container’s hostname in your gitea configuration (.env) as well as other references. The docker-compose file looks like this:
services:
server:
image: gitea/gitea:latest
container_name: gitea
env_file: .env
restart: always
networks:
- gitea
volumes:
- type: volume
source: gitea
target: /data
- type: bind
source: /home/git/.ssh
target: /data/git/.ssh
- type: bind
source: /etc/timezone
target: /etc/timezone
read_only: true
- type: bind
source: /etc/localtime
target: /etc/localtime
read_only: true
ports:
- "127.0.0.1:3000:3000"
- "127.0.0.1:2222:22"
depends_on:
- db-pg-16
# The old database
# db-pg-15:
# image: postgres:15
# restart: always
# env_file: .env_db
# networks:
# - gitea
# volumes:
# - type: volume
# source: postgres_data_15
# target: /var/lib/postgresql/data
# The new database
db-pg-16:
image: postgres:16
restart: always
env_file: .env_db
networks:
- gitea
volumes:
- type: volume
source: postgres_data_16
target: /var/lib/postgresql/data
networks:
gitea:
external: false
volumes:
gitea:
# postgres_data_15:
postgres_data_16:
make run a several postgresql containers in one docker-compose
services:
gitea:
...
pgold:
image: postgres:9.6
environment:
POSTGRES_USER: gitea
POSTGRES_PASSWORD: gitea
POSTGRES_DB: gitea
networks:
- gitea
volumes:
- ./pgdump:/tmp/pgdump
- ./postgres9:/var/lib/postgresql/data
- /etc/timezone:/etc/timezone:ro
- /etc/localtime:/etc/localtime:ro
pgnew:
image: postgres:16.3
environment:
POSTGRES_USER: gitea
POSTGRES_PASSWORD: gitea
POSTGRES_DB: gitea
networks:
- gitea
volumes:
- ./pgdump:/tmp/pgdump
- ./postgres16:/var/lib/postgresql/data
- /etc/timezone:/etc/timezone:ro
- /etc/localtime:/etc/localtime:ro
where pgold - your original PG, pgnew - your new PG, pgdump is a shared directory to transfer dump from containers
docker-compose stop gitea
docker exec -it pgold /bin/bash
PGPASSWORD="gitea" && pg_dumpall -U gitea -f /tmp/pgdump/dump.sql
docker exec -it pgnew /bin/bash
PGPASSWORD="gitea" && psql -d gitea -f /tmp/pgdump/dump.sql