search

fly-apps / postgres-flex

Postgres HA setup using repmgr
87 stars 43 forks source link

Upgrading to PostgreSQL 15.4 and/or 16 #212

Closed dabure closed 2 months ago

dabure commented 12 months ago

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 postgresql@15.3 does not work), via Postgres.app, or via EDB.

davissp14 commented 12 months ago

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 commented 12 months ago

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 commented 9 months ago

@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:

dabure commented 9 months ago

@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 commented 7 months ago

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 commented 7 months ago

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

hbriese commented 6 months ago

Are there plans to support 16?

carldr commented 6 months ago

@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 commented 3 months ago

@ndarilek any word on pg16?

hbriese commented 3 months ago

@ndarilek any word on pg16?

209

davissp14 commented 2 months ago

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 commented 2 months ago

Is there a way to upgrade?

davissp14 commented 2 months ago

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.