Bump default Postgres from 9.6 to 14

[chadwhitacre]

Previous installment: https://github.com/getsentry/self-hosted/issues/1097

I learned last week that we've completed the Postgres upgrade for SaaS from 9.6 the whole way to 14. Time to bump the default version in self-hosted to match. What docs do we need? What are the upgrade steps? What other considerations exist?

[aminvakil]

Let's have this here https://github.com/tianon/docker-postgres-upgrade/tree/master/9.6-to-14, we may need it.

[chadwhitacre]

Back to gosu after all. :P

[chadwhitacre]

FYI we'll be doing something similar in the local dev env, cc: @beezz to coordinate as it makes sense.

[github-actions[bot]]

This issue has gone three weeks without activity. In another week, I will close it.

But! If you comment or otherwise update it, I will reset the clock, and if you label it Status: Backlog or Status: In Progress, I will leave it alone ... forever!

---

"A weed is but an unloved flower." ― Ella Wheeler Wilcox 🥀

[beezz]

fyi: Just opened a PR with an upgrade script https://github.com/getsentry/sentry/pull/38733

[chadwhitacre]

Thanks @beezz. Will this script live in the sentry repo forever, or will it be removed at some predictable point down the line, or ... ?

@ethanhs @hubertdeng123 Can we look over the script and see if it will work for self-hosted as well?

My thinking is that if it will be suitable for self-hosted and at a stable URL then we can link to it from our self-hosted docs, etc. as the recommended path for upgrading.

I'm inclined to say that the db upgrade should be manual rather than trying to make it automatic in the install.sh script. What does everyone think?

[emmatyping]

I'm inclined to say that the db upgrade should be manual rather than trying to make it automatic in the install.sh script. What does everyone think?

Yeah I think we may want to ask people to do the upgrade manually.

[beezz]

Will this script live in the sentry repo forever, or will it be removed at some predictable point down the line, or ... ?

I would like to remove it a few weeks after pg 14 becomes the default dev environment version.

[chadwhitacre]

I would like to remove it a few weeks after pg 14 becomes the default dev environment version.

Okay, in that case @ethanhs @hubertdeng123 let's plan to maintain our own version of it as we will want to keep that around longer (a year or two? forever? move it to a gist after a while?)

[hubertdeng123]

Yep, that's what I was thinking. We should provide users the script to upgrade for a bit longer

[roock]

FWIW Upgrading to PostgreSQL 14 requires updated wal2json PostgreSQL extension in https://github.com/getsentry/wal2json

[patsevanton]

The final release for PostgreSQL 9.6 is slated for November 11th, 2021. https://www.postgresql.org/support/versioning/

PR https://github.com/getsentry/sentry/pull/38733 is closed. Any news about migration to new Postgresql ?

[chadwhitacre]

I've added this to https://github.com/getsentry/team-ospo/issues/64, maybe we can prioritize this next quarter.

[patsevanton]

I can test you alfa version Sentry with new PostgreSQL.

[meotimdihia]

Please do it soon. The server storage was full because we can't repack, I think many people will have the same problem.

[shkarface]

Is there any progress on this upgrade for self-hosted installations?

[chadwhitacre]

No progress, no. To be clear, though, people are successfully running Self-hosted Sentry on Postgres 14 (see https://github.com/getsentry/self-hosted/issues/1097 for details). This ticket is about changing the default in the repo, but there's nothing in Sentry stopping you from upgrading on your own. In fact, even when we switch the default we'll almost certainly leave it to you to manually upgrade older installations. I believe in the past we've tried to automate Postgres upgrades, that feels risky to me and I'm inclined to not take that on again.

tl;dr Go upgrade! :-)

[chadwhitacre]

I've updated the ticket description to reflect that you should go upgrade. Anyone have a good write-up on steps for this? Let's collect some collective wisdom and put it in the docs.

[BYK]

I believe in the past we've tried to automate Postgres upgrades, that feels risky to me and I'm inclined to not take that on again.

It was not risky and it worked out quite well. I'd say it is the responsibility of this repo to provide smooth upgrades once the default becomes 14. This section in self-hosted docs implicitly says that upgrades should be care-free unless you are skipping over a "hard-stop".

[chadwhitacre]

Just posted internally by @beezz re: dev sentry:

The time has finally come. Next week we are upgrading the PostgreSQL version in the development environment and CI from 9.6 to 14.

If you want to start using PostgreSQL version 14 right now and don't need to keep any data, do the following:

$ docker stop sentry_postgres; docker rm sentry_postgres && docker volume rm sentry_postgres
$ export PG_VERSION=14
$ sentry devservices up postgres
$ make reset-db

If you want to keep data, use the migration script from sentry repo:

./scripts/upgrade-postgres.sh

In both cases, save the export PG_VERSION=14 in your shell configuration file so that the next time you run sentry devservices, the correct PostgreSQL is used.

Why are we doing it?

There are multiple reasons.

• PostgreSQL version 9.6 support ended on 11 Nov 2021, meaning we are currently developing against an unmaintained version.
• In SaaS production, we have been running all our clusters on version 14 for some time now.
• The upgrade allows us to use new shiny features like native logical replication, table partitioning, and more.

[hubertdeng123]

We should be able to copy the upgrade script to our scripts in self-hosted and it should just work. I attempted to do that successfully a while back. I'd imagine we'd want to bump the default Postgres version here soon since starting next week PG 14 will be used everywhere.

[hubertdeng123]

It would be super similar to the script here: https://github.com/getsentry/self-hosted/blob/1920b23d7d0beb552bdfaa8eb17f53af2ab6971b/install/upgrade-postgres.sh

But with 9.6 -> 14

if [[ -n "$(docker volume ls -q --filter name=sentry-postgres)" && "$(docker run --rm -v sentry-postgres:/db busybox cat /db/PG_VERSION 2>/dev/null)" == "9.6" ]]; then
  docker volume rm sentry-postgres-new || true
  # If this is Postgres 9.6 data, start upgrading it to 14 in a new volume
  docker run --rm \
  -v sentry-postgres:/var/lib/postgresql/9.6/data \
  -v sentry-postgres-new:/var/lib/postgresql/14/data \
  tianon/postgres-upgrade:9.6-to-14

We can delete this upgrade script after, since the script will always run for the version we declare as the hard-stop. @BYK does that sound similar to the way it was handled in the past?

[hubertdeng123]

Although it looks like we deleted those scripts a while after declaring certain versions as hard stops in the past 😅https://github.com/getsentry/self-hosted/issues/1705

[BYK]

@hubertdeng123 yup, you'll just revive this script but for 9.6->14.

It even has the version check built-in so it should "just work™" 😅

[roock]

To everyone running Postgresql 14: How are you integrating the wal2json extension? The current version uses seems not compatible with postgres 14 afaik. Is the wal2json functionality even already used inside Sentry?

[chadwhitacre]

Last I heard, Sentry should function fine without wal2json. I'm checking internally to see if this is still true.

[hubertdeng123]

FWIW I tested the upgrade on a branch in self-hosted yesterday, seemed to work fine

[beezz]

To everyone running Postgresql 14: How are you integrating the wal2json extension? The current version uses seems not compatible with postgres 14 afaik. Is the wal2json functionality even already used inside Sentry?

@roock The wal2json extension works with pg14 just fine. Are you running into some problems?

[roock]

@beezz I haven't tried running Sentry with Postgres 14 (or any newer version than the default version yet).

When I investigated in this issue a few months ago, I found some commits in the wal2json upstream repository (which are not present in the sentry fork) that would suggest that the Sentry fork is not ready yet for newer Postgresql versions. I wasn't able to find those commits again today.

[hubertdeng123]

put up a PR for this!

@BYK if you could take a look at it 🙏