Content

OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade

Added by Hubertus Paar about 1 month ago

Hi,

I did follow the instructions in this article https://www.openproject.org/docs/installation-and-operations/misc/migration-to-postgresql17/ but it ends always with this message "Current and new postgres version are identical. Aborting."

This message comes from the "postgres-db-upgrade" script.

I would like to upgrade from version 13.

Any help here is appreciated.

Replies (11)

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Andreas Fragner 28 days ago

Same thing here - I inspected the file which should do the upgrade:

https://github.com/opf/openproject/blob/dev/docker/prod/postgres-db-upgrade

The line NEW_PGVERSION="13" tells me, that there is actually no upgrade possible in OpenProject 16.

What are we missing? Or is it a bug and DB Upgrade for docker all in one installation is not supported at the moment?

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Marc Walter 23 days ago

Same thing here as well, ideas are welcome:

sudo docker run --rm -it -v /var/lib/containers/openproject/pgdata:/var/openproject/pgdata -v /var/lib/containers/openproject/pgdata-next:/var/openproject/pgdata-next openproject/openproject:16 root ./docker/prod/postgres-db-upgrade
-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=13 PGBIN=/usr/lib/postgresql/13/bin PGCONF_FILE=/etc/postgresql/13/main/postgresql.conf
Current and new postgres version are identical. Aborting.

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Katja Krüger 5 days ago

I used the image 16.0.1 and went one step forward:

docker run --rm -it -v xxxx:/var/openproject/pgdata -v openproject-pgdata-next:/var/openproject/pgdata-next openproject/openproject:16 root ./docker/prod/postgres-db-upgrade
-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=13 PGBIN=/usr/lib/postgresql/13/bin PGCONF_FILE=/etc/postgresql/13/main/postgresql.conf
Empty cluster found in /var/openproject/pgdata-next. Generating cluster...
chown: warning: '.' should be ':': ‘postgres.postgres’
bash: line 1: /usr/lib/postgresql/17/bin/initdb: No such file or directory

ONE hour ago a new image was released

docker pull openproject/openproject:16.1 perhaps you try it with that one?

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Marc Walter 5 days ago

I stopped the container. Then i did the following via command line:

docker pull openproject/openproject:16.1 
sudo docker run --rm -it -v /var/lib/XXXXXX/openproject/pgdata:/var/openproject/pgdata -v /var/lib/XXXXX/openproject/pgdata-next:/var/openproject/pgdata-next openproject/openproject:16.1 root ./docker/prod/postgres-db-upgrade

The database upgraded worked, it was all "OK".

I use it with portainer and stacks. So i restarted everything with this configuration. I don't specify any SQL version.

version: '3.9'
services:
  openproject:
    hostname: XXXXX-OPEN-01
    image: openproject/openproject:16.1.0

    ports:
      - 8181:80
      
    container_name: openproject

    environment:
      - PUID=1000
      - PGID=1000
      - SECRET_KEY_BASE=XXXXXXXXXXXXXXXX
    volumes:
      - /var/lib/XXXXXXX/openproject/pgdata:/var/openproject/pgdata
      - /var/lib/XXXXXXX/openproject/assets:/var/openproject/assets
    restart: unless-stopped

The installation ist updated and works again, so far good.

But the SQL version is still the old one.

Produktversion

refs/tags/v16.1.0

Core Version

OpenProject 16.1.0

PostgreSQL-Version

13.21

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Hubertus Paar 5 days ago

Well the upgrade was succesfully but now I am getting this:

psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"
connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"
-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=17 PGBIN=/usr/lib/postgresql/17/bin PGCONF_FILE=/etc/postgresql/17/main/postgresql.conf
-----> Starting the all-in-one OpenProject setup at /app/docker/prod/supervisord...
-----> Database cluster already exists, not modifying.
waiting for server to start..... done
server started
Trying to contact PostgreSQL server instance or waiting for it to come online.
Waiting for postgres server, 10 remaining attempts...
Waiting for postgres server, 9 remaining attempts...
Waiting for postgres server, 8 remaining attempts...
Waiting for postgres server, 7 remaining attempts...
Waiting for postgres server, 6 remaining attempts...
Waiting for postgres server, 5 remaining attempts...
Waiting for postgres server, 4 remaining attempts...
Waiting for postgres server, 3 remaining attempts...
Waiting for postgres server, 2 remaining attempts...
Waiting for postgres server, 1 remaining attempts...
Unable to contact postgres server:
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"
connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"

** Press ANY KEY to close this window **

My Proects became not accessable.

Can anyone help me here? Thank you.

refs/tags/v16.1.0

Core Version

OpenProject 16.1.0

PostgreSQL-Version

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Marc Walter 4 days ago

Hubertus

Interesting you have the new PostgreSQL version. Can you give us more details about your compose, does ist look like mine?

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Hubertus Paar 1 day ago

Hi,

i first upgraded the all-in-one container to 16.1, then I followed the original instructions https://www.openproject.org/docs/installation-and-operations/misc/migration-to-postgresql17/

"sudo docker run --rm -it -v /var/lib/containers/openproject/pgdata:/var/openproject/pgdata -v /var/lib/containers/openproject/pgdata-next:/var/openproject/pgdata-next openproject/openproject:16 root ./docker/prod/postgres-db-upgrade"

and changed to the pgdata-next directory

But now I have the above problem. I even tried to switch back to postgressql 13 by downgranding to openproject 16.0 and change pgdata directory to original location, but here it just stops working.

I hope I could explain it clearly.

Any help is appreciated.

Thank you.

PS: I filled in a bug report

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Frank Wintergreen about 19 hours ago

Experiencing the same issue - successfully upgraded postgres once I went to Openproject v16.1, then renamed my host postgres-next folder to postgres and relaunched, same 'password authentication failed for user "openproject"' error. It's not port 5433 vs 5432 by any chance?

-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=17 PGBIN=/usr/lib/postgresql/17/bin PGCONF_FILE=/etc/postgresql/17/main/postgresql.conf
-----> Starting the all-in-one OpenProject setup at /app/docker/prod/supervisord...
No cluster configuration found for postgres 17. Creating configuration.
Creating new PostgreSQL cluster 17/main ...
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

...

Ver Cluster Port Status Owner    Data directory              Log file
17  main    5433 down   postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
-----> Database cluster already exists, not modifying.

...

       Unable to contact postgres server:
       psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "openproject"
       connection to server at "127.0.0.1", port 5432 failed: FATAL:  password authentication failed for user "openproject"

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Jürgen Weiss about 5 hours ago

Also, same issue here using 16.1 image and trying to update from PostgreSQL 13 to 17.

Unable to contact postgres server: psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject" connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Sebastian Drozda about 4 hours ago

Hello, same problem.

First I updated 16.0.0 to 16.1.0 then I tried to do PG upgrade, after succesfull upgrade Im getting that password login failed. Not sure if this is usfull but new PG configuration have all defaults, dose not have my PG 13 memory tunings.

Upgrade log:

docker run --rm -it \
  -v /mnt/Main/Services/OpenProject/Database/pgdata:/var/openproject/pgdata \
  -v /mnt/Main/Services/OpenProject/Database/pgdata-next:/var/openproject/pgdata-next \
  openproject/openproject:16 root ./docker/prod/postgres-db-upgrade
-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=13 PGBIN=/usr/lib/postgresql/13/bin PGCONF_FILE=/etc/postgresql/13/main/postgresql.conf
Empty cluster found in /var/openproject/pgdata-next. Generating cluster...
chown: warning: '.' should be ':': ‘postgres.postgres’
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "C.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/openproject/pgdata-next ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

    /usr/lib/postgresql/17/bin/pg_ctl -D /var/openproject/pgdata-next -l logfile start

Performing Consistency Checks
-----------------------------
Checking cluster versions                                     ok
Checking database user is the install user                    ok
Checking database connection settings                         ok
Checking for prepared transactions                            ok
Checking for contrib/isn with bigint-passing mismatch         ok
Checking data type usage                                      ok
Checking for user-defined encoding conversions                ok
Checking for user-defined postfix operators                   ok
Checking for incompatible polymorphic functions               ok
Creating dump of global objects                               ok
Creating dump of database schemas
                                                              ok
Checking for presence of required libraries                   ok
Checking database user is the install user                    ok
Checking for prepared transactions                            ok
Checking for new cluster tablespace directories               ok

If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.

Performing Upgrade
------------------
Setting locale and encoding for new cluster                   ok
Analyzing all rows in the new cluster                         ok
Freezing all rows in the new cluster                          ok
Deleting files from new pg_xact                               ok
Copying old pg_xact to new server                             ok
Setting oldest XID for new cluster                            ok
Setting next transaction ID and epoch for new cluster         ok
Deleting files from new pg_multixact/offsets                  ok
Copying old pg_multixact/offsets to new server                ok
Deleting files from new pg_multixact/members                  ok
Copying old pg_multixact/members to new server                ok
Setting next multixact ID and offset for new cluster          ok
Resetting WAL archives                                        ok
Setting frozenxid and minmxid counters in new cluster         ok
Restoring global objects in the new cluster                   ok
Restoring database schemas in the new cluster
                                                              ok
Copying user relation files
                                                              ok
Setting next OID for new cluster                              ok
Sync data directory to disk                                   ok
Creating script to delete old cluster                         ok
Checking for extension updates                                notice

Your installation contains extensions that should be updated
with the ALTER EXTENSION command.  The file
    update_extensions.sql
when executed by psql by the database superuser will update
these extensions.

Upgrade Complete
----------------
Optimizer statistics are not transferred by pg_upgrade.
Once you start the new server, consider running:
    /usr/lib/postgresql/17/bin/vacuumdb --all --analyze-in-stages
Running this script will delete the old cluster's data files:
    ./delete_old_cluster.sh
bash: line 1: ./analyze_new_cluster.sh: No such file or directory

Start log after upgrade:

2025-06-23 16:24:36,335 WARN received SIGTERM indicating exit request
2025-06-23 16:24:36,336 INFO waiting for postgres, apache2, web, worker, memcached to die
2025-06-23 16:24:37,419 INFO stopped: memcached (exit status 0)
2025-06-23 16:24:39,422 WARN stopped: worker (terminated by SIGTERM)
2025-06-23 16:24:39,423 INFO waiting for postgres, apache2, web to die
[19957] === puma shutdown: 2025-06-23 16:24:39 +0000 ===
[19957] - Goodbye!
[19957] - Gracefully shutting down workers...
Exiting
2025-06-23 16:24:40,067 WARN stopped: web (terminated by SIGTERM)
2025-06-23 16:24:40,068 WARN stopped: apache2 (terminated by SIGTERM)
2025-06-23 16:24:40.068 UTC [19955] LOG: received smart shutdown request
2025-06-23 16:24:43,071 INFO waiting for postgres to die
2025-06-23 16:24:46,075 INFO waiting for postgres to die
-----> Existing PostgreSQL cluster found in /var/openproject/pgdata.
-----> Setting PGVERSION=17 PGBIN=/usr/lib/postgresql/17/bin PGCONF_FILE=/etc/postgresql/17/main/postgresql.conf
-----> Starting the all-in-one OpenProject setup at /app/docker/prod/supervisord...
No cluster configuration found for postgres 17. Creating configuration.
Creating new PostgreSQL cluster 17/main ...
/usr/lib/postgresql/17/bin/initdb -D /var/lib/postgresql/17/main --auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "C.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /var/lib/postgresql/17/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default "max_connections" ... 100
selecting default "shared_buffers" ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner Data directory Log file
17 main 5433 down postgres /var/lib/postgresql/17/main /var/log/postgresql/postgresql-17-main.log
-----> Database cluster already exists, not modifying.
waiting for server to start.... done
server started
Trying to contact PostgreSQL server instance or waiting for it to come online.
Waiting for postgres server, 10 remaining attempts...
Waiting for postgres server, 9 remaining attempts...
Waiting for postgres server, 8 remaining attempts...
Waiting for postgres server, 7 remaining attempts...
Waiting for postgres server, 6 remaining attempts...
Waiting for postgres server, 5 remaining attempts...
Waiting for postgres server, 4 remaining attempts...
Waiting for postgres server, 3 remaining attempts...
Waiting for postgres server, 2 remaining attempts...
Waiting for postgres server, 1 remaining attempts...
Unable to contact postgres server:
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"
connection to server at "127.0.0.1", port 5432 failed: FATAL: password authentication failed for user "openproject"

My messy compose:

services:
  openproject:
    container_name: OpenProject
    image: 'openproject/openproject:16'
    restart: always
    hostname: 'OpenProject'
    environment:
      - OPENPROJECT_SECRET_KEY_BASE=censored1
      - OPENPROJECT_HOST__NAME=op.example.net
      - OPENPROJECT_HTTPS=true
      - adminpass=censored2
      - OPENPROJECT_LOG__LEVEL=warn
    labels:
      - "traefik.enable=true"
      - "traefik.http.routers.openproject.rule=Host(`op.example.net`)"
      - "traefik.http.services.openproject.loadbalancer.server.port=8080"
      - "traefik.http.routers.openproject.middlewares=mwSecure-Headers@file"
    volumes:
      - '/mnt/Main/Services/OpenProject/Database/pgdata:/var/openproject/pgdata'
      - '/mnt/Main/Services/OpenProject/assets:/var/openproject/assets'
    networks:
      - traefik_net

  pgadmin:
    container_name: OpenProject_PGAdmin
    image: dpage/pgadmin4
    restart: "no"
    #network_mode: "bridge"
    environment:
      PGADMIN_DEFAULT_EMAIL: admin@example.net
      PGADMIN_DEFAULT_PASSWORD: censored2
    ports:
      - "9021:80"
    volumes:
      - "/mnt/Main/Services/OpenProject/Database/pgadmin:/var/lib/pgadmin"
    networks:
      - internet_net
      - traefik_net

networks:
  traefik_net:
    name: traefik_net
    external: true
  internet_net:
    driver: bridge

RE: OpenProject 16 Docker (All-in-One) PostgreSQL Upgrade - Added by Marc Walter about 1 hour ago

Hi @all

I made now some tests and probably the difference ist, that I didn't switched back but now the docs have changed and it's strange at the moment how that should work.

I have a working old database in: /var/lib/containers/openproject/pgdata. This database starts with Postgresql 13.21 and with:

"sudo docker run --rm -it -v /var/lib/containers/openproject/pgdata:/var/openproject/pgdata -v /var/lib/containers/openproject/pgdata-next:/var/openproject/pgdata-next openproject/openproject:16 root ./docker/prod/postgres-db-upgrade"

I only have temporal container which updates and works well but also with Postgresql 13.21.

For me it's not clear how that should work and does the image delivery both versions? Why?

Top Menu

Side Menu