upgrade postgres local container from 16 to 20 (or more recent)

[mlodic]

We need to take into account possible problems about the docker volumes. See: GreedyBear-Project/GreedyBear#766 (comment)

[NikhilRaikwar]

Hi @mlodic, I am interested in working on this issue. I've successfully tested the upgrade to Postgres 17 locally along with the volume mount fix mentioned in the GreedyBear discussion.

[NikhilRaikwar]

Hi @mlodic,

I've addressed this issue and submitted a PR: #3275

Summary of changes:

• Upgraded Postgres from 16-alpine to 17-alpine
• Fixed the volume mount path from /var/lib/postgresql/data/ to /var/lib/postgresql
• Added PGDATA=/var/lib/postgresql/pgdata configuration to prevent the "empty database" issue observed in GreedyBear

The fix has been tested locally and verified to work correctly. All verification screenshots are included in the PR.

Looking forward to your review!

[mlodic]

thanks but can you also test with the more recent version of postgres? Also, please add some data e showcase how they are visible first and afterwards

[NikhilRaikwar]

@mlodic I have updated the PR to use the latest postgres:18-alpine as requested.

To verify persistence with the new version:

1. I created a test_persistence table and inserted data:
   • Row 1 & 2 ("IntelOwl Data", "Data BEFORE Restart") were added before shutting down.
2. I performed a full restart of the stack (start test down -> start test up).
3. I inserted a new Row 3 ("Data AFTER Restart").

As shown in the updated screenshots in the PR description, the pre-existing data (Rows 1 & 2) was successfully preserved after the restart, confirming the volume mount fix works with Postgres 18.

[mlodic]

I had to revert the change.

This is the issue that appears when changing the postgres version from 16 to 18 in my local environment. That issue cannot be seen in the clean CI environment:

intelowl_postgres  | Error: in 18+, these Docker images are configured to store database data in a
intelowl_postgres  |        format which is compatible with "pg_ctlcluster" (specifically, using
intelowl_postgres  |        major-version-specific directory names).  This better reflects how
intelowl_postgres  |        PostgreSQL itself works, and how upgrades are to be performed.
intelowl_postgres  | 
intelowl_postgres  |        See also https://github.com/docker-library/postgres/pull/1259
intelowl_postgres  | 
intelowl_postgres  |        Counter to that, there appears to be PostgreSQL data in:
intelowl_postgres  |          /var/lib/postgresql
intelowl_postgres  | 
intelowl_postgres  |        This is usually the result of upgrading the Docker image without
intelowl_postgres  |        upgrading the underlying database using "pg_upgrade" (which requires both
intelowl_postgres  |        versions).
intelowl_postgres  | 
intelowl_postgres  |        The suggested container configuration for 18+ is to place a single mount
intelowl_postgres  |        at /var/lib/postgresql which will then place PostgreSQL data in a
intelowl_postgres  |        subdirectory, allowing usage of "pg_upgrade --link" without mount point
intelowl_postgres  |        boundary issues.
intelowl_postgres  | 
intelowl_postgres  |        See https://github.com/docker-library/postgres/issues/37 for a (long)
intelowl_postgres  |        discussion around this process, and suggestions for how to do so.

[NikhilRaikwar]

Hi @mlodic, apologies for the inconvenience!

Since the issue was reopened, I'd like to propose a fix for the migration error. The crash happens because the volume still contains Postgres 16 data, which is incompatible with 18.

To fix this seamlessly for everyone (avoiding manual docker volume rm commands), I can update docker-compose to use a new named volume (e.g., postgres_data_v18). This guarantees a fresh start for the new version.

Does this approach sound good to you?