website/docs: next release notes: add upgrade instructions for postgres to 16

[dominic-r]

Details

Provides documentation to upgrade authentik-postgresql to PG v16

closes #9628

---

Checklist

• [ ] Local tests pass (ak test authentik/)
• [ ] The code has been formatted (make lint-fix)

If an API change has been made

• [ ] The API schema has been updated (make gen-build)

If changes to the frontend have been made

• [ ] The code has been formatted (make web)

If applicable

• [ ] The documentation has been updated
• [ ] The documentation has been formatted (make website)

[netlify[bot]]

✅ Deploy Preview for authentik-storybook canceled.

Name	Link
🔨 Latest commit	3208e66e831605a22ef248b71ecf1ae87504338f
🔍 Latest deploy log	https://app.netlify.com/sites/authentik-storybook/deploys/6654c0661cc7d90008376512

[netlify[bot]]

❌ Deploy Preview for authentik-docs failed.

Name	Link
🔨 Latest commit	69f90994bbfa281a4b87a8120bb31d441a93b286
🔍 Latest deploy log	https://app.netlify.com/sites/authentik-docs/deploys/6654b16cf2eaf80009948b49

[codecov[bot]]

Codecov Report

All modified and coverable lines are covered by tests :white_check_mark:

Project coverage is 92.64%. Comparing base (fd66be9) to head (3208e66). Report is 57 commits behind head on main.

Additional details and impacted files

@@           Coverage Diff           @@
##             main    #9727   +/-   ##
=======================================
  Coverage   92.64%   92.64%           
=======================================
  Files         710      710           
  Lines       34706    34745   +39     
=======================================
+ Hits        32152    32189   +37     
- Misses       2554     2556    +2     

Flag	Coverage Δ
e2e	49.66% <ø> (-0.01%)	:arrow_down:
integration	25.50% <ø> (-0.02%)	:arrow_down:
unit	90.09% <ø> (+<0.01%)	:arrow_up:

Flags with carried forward coverage won't be shown. Click here to find out more.

:umbrella: View full report in Codecov by Sentry.
:loudspeaker: Have feedback on the report? Share it here.

[jehy]

Great PR! Just several hours ago I independently upgraded to postgres 16, there were no problems and it seems like authentik is working much faster.

[dominic-r]

Great PR! Just several hours ago I independently upgraded to postgres 16, there were no problems and it seems like authentik is working much faster.

Thanks! Out of curiosity, did you update with an existing database or did you recreate it? I'm currently facing the error psycopg.errors.DuplicateTable: relation "authentik_events_event" already exists and i'm looking for ways to make the upgrade easier. From wha I saw, it's impossible (unless i only saw the errors and not the success) to restore database from upgrade_backup_12.sql without purging it first due to already existing table errors.

[dominic-r]

also for project maintainers @BeryJu / @rissson, where should I put the markdown file once completed?

[rissson]

We'll probably include this in the release notes, so website/docs/releases/2024/next.md, which you can create from the template.

[rissson]

Great PR! Just several hours ago I independently upgraded to postgres 16, there were no problems and it seems like authentik is working much faster.

Yeah we've been testing 16 in CI pretty much since in came out, no issue. Interesting that it seems to be faster though!

[dominic-r]

Ok. thanks for the quick response. Regarding my issue, do you have any other suggestions or would you do it differently?

[rissson]

Great PR! Just several hours ago I independently upgraded to postgres 16, there were no problems and it seems like authentik is working much faster.

Thanks! Did you update with an existing database or did you recreate it? I'm currently facing the error psycopg.errors.DuplicateTable: relation "authentik_events_event" already exists and i'm looking for ways to make the upgrade easier. From wha I saw, it's impossible (unless i only saw the errors and not the success) to restore database from upgrade_backup_12.sql without purging it first due to already existing table errors.

You shouldn't need to re-create a database, restoring your dump file should be enough. pg_dump has arguments to delete objects if they already exist (-c, -C). The tables probably got re-created because authentik was running and thus executed its migrations as if it was a new install. As such, it should be shut down during the whole operation. The best way would be to cut off connections to postgresql completely, so network: none (don't remember the exact syntax) for a docker-compose installation, and firewalling for a standard postgres install.

[dominic-r]

I'll try that soon. Thanks!

[dominic-r]

Regarding K8s, someone would have to write the documentation as I don't really have any experience with it

[rissson]

Regarding K8s, someone would have to write the documentation as I don't really have any experience with it

We distribute postgres 15 with the helm chart, so we won't be upgrading that one soon.

[dominic-r]

@rissson I think this is ready for first review

[jehy]

@4d62ext

Thanks! Out of curiosity, did you update with an existing database or did you recreate it?

I simply exported my database to sql dump and then imported it to a new postgres instance, all went smoothly. May be you were running authentik with new database and it already created new database object before you imported dump?

[dominic-r]

@4d62ext

Thanks! Out of curiosity, did you update with an existing database or did you recreate it?

I simply exported my database to sql dump and then imported it to a new postgres instance, all went smoothly. May be you were running authentik with new database and it already created new database object before you imported dump?

Something like that. Rissson's solution was way easier to implement and I don't know how i skipped over it. I got an "invalid SCRAM password" error a few hours later but I don't think it's related.

[jehy]

I don't think that cutting connection to database is really simpler.

Step by step, my approach was:

1. Add new postgres16 instance to docker compose. It is not connected to authentik, so it stays empty.
2. Dump old database using docker and pg CLI
3. Stop old database to make sure that I won't accidentally break it, just to make sure I can roll back to it.
4. Import dump to new postgres instance
5. Change authentik database host to this new instance
6. Restart authentik

[rissson]

I don't think that cutting connection to database is really simpler.

It is what is usually recommended for postgres upgrades though. A nice addition with doing this is that you ensure that no new data is written by authentik to the database between the dump, and the database switch from the old to the new one.

[dominic-r]

@BeryJu something like this? tried adding headers like the example you provided

[dominic-r]

Great info and steps to have, thanks @4d62ext !

No problem and thanks for the quick review.

[rissson]

@BeryJu something like this? tried adding headers like the example you provided

[rissson]

I'm so sorry, I misscliked.

something like this? tried adding headers like the example you provided

Yes!

[dominic-r]

my bad, tried renaming branch on my fork

[dominic-r]

Thanks again @4d62ext !

no problem!