Upgrading to PostgreSQL 15.4 and/or 16

[dabure]

Hi

Do you have any plans to upgrade the supported PostgreSQL version to 15.4 and/or version 16 (from 15.3)?

I'm currently doing local development with version 15.4 and have encountered bugs in my Fly.io-hosted application that arose from differences between Postgres 15.3 and 15.4. I realise I could fork this repo and deploy an upgrade myself, but I would prefer to avoid that.

I could also solve the issue by downgrading locally. However, it's a real challenge to install a version of Postgres 15.3 for local development on Mac. The Postgres website itself does not provide any links for installing version 15.3. It is not available via brew (brew install [email protected] does not work), via Postgres.app, or via EDB.

[davissp14]

I'm currently doing local development with version 15.4 and have encountered bugs in my Fly.io-hosted application that arose from differences between Postgres 15.3 and 15.4. I realise I could fork this repo and deploy an upgrade myself, but I would prefer to avoid that.

Hey there, could you provide some additional information on the issues you encountered?

[dabure]

I'm currently doing local development with version 15.4 and have encountered bugs in my Fly.io-hosted application that arose from differences between Postgres 15.3 and 15.4. I realise I could fork this repo and deploy an upgrade myself, but I would prefer to avoid that.

Hey there, could you provide some additional information on the issues you encountered?

Hi Shaun, thanks for the swift reply.

I am using SQLAlchemy in a Python application and either (a) SQLAlchemy compiled different SQL code for the different versions, or (b) Postgres 15.3 did not support the same SQL syntax as 15.4. I solved the issue by changing my approach and unfortunately do not have the stacktrace left. However, the issue was related to an insert statement where Postgres was instructed to do nothing on conflict – it worked fine locally, but the staging envioronment, with Postgres 15.3 installed, complained about the SQL it got from the application.

The root issue is that I am developing towards a different Postgres version locally than I have deployed in my staging and production environments. This amplifies the risk that the application behaves differently locally and when deployed, leading me to detect bugs later than I optimally would like to.

[rickclare]

@dabure As an aside, have you tried using asdf for managing multiple versions of Postgres on the same local development machine? (it can also handle many other dependencies such as Elixir, Erlang, Rust, Ruby, Redis, Python...)

I've been using asdf on MacOS for several years and it works well for me (including installing multiple versions of Postgres locally)

See:

• https://asdf-vm.com/guide/introduction.html
• https://github.com/asdf-vm/asdf
• https://github.com/asdf-vm/asdf-plugins
• https://github.com/smashedtoatoms/asdf-postgres

[dabure]

@rickclare I have not, thanks for the recommendation and the helpful links. I see it even comes with its own ballad, so guess I'll have to give it a spin!

[sandrotanner]

Sorry for the bump but the current version (15.3) is almost a year old and the latest version contains among other things security fixes ( https://www.postgresql.org/docs/release/15.6/ ).
Is an upgrade planned soon?

[carldr]

There was an update a couple of days ago which bumped the version to 15.6.

[hbriese]

Are there plans to support 16?

[carldr]

@hbriese I have a fork at https://github.com/statuspet/postgres-flex-citus which has support for PostgreSQL 16, I've added some information in the README on how to get it up on Fly. I don't attempt to solve the problem of upgrading from PostgreSQL 15 if you have an existing cluster, I've not tried it, and I assume it's not automatic.

The same repository also has Dockerfiles which add Citus support, both for PostgreSQL 15 and 16.

[lilouartz]

@ndarilek any word on pg16?

[hbriese]

@ndarilek any word on pg16?

#209

[davissp14]

As of today, new provisions will now default to 16.3. PG 15.7 has also been released for folks happy with continuing to use PG 15.

[lilouartz]

Is there a way to upgrade?

[davissp14]

fly image upgrade is available for minor version upgrades, but we do not currently offer automated upgrades across major versions.

If you're interested in going from PG 15 -> PG 16, your best bet would be to spin up a new PG and use fly pg import to move your data over.