7 ms·

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it

by morley 4y ago

I hate to ask a stupid question, but I'm new to administering a Postgres database. Do admins usually upgrade their DBs with each major release? I'm guessing it's highly contextual and depends on how easy it is to do so, but I've heard about places that never upgrade until it's a huge problem for them to do so (in order to avoid an even worse problem.)

ianbutler 4y ago
I don't think I've worked in an environment where we've upgraded for every release. New projects may start on that newer version, but generally speaking for older projects there's a cost calculation done for the newer features versus the lift required to do the upgrade and ensure it doesn't introduce any regressions. Someone already mentioned the view permissions shift here which looks at caller permissions versus the view creator permissions, that will be compelling in a lot of cases so like this is something that I'd probably raise internally for my team for a few of the apps we maintain and then have a back and forth with the principals, if we like it then go through our current list of work with a PM and our manager and see if it makes sense to do now with the current pipeline of work etc.
wahnfrieden 4y ago
.1+ releases might be best with pg for quality assurance
dewey 4y ago
This probably depends on your database, but with Postgres we usually don't stay on the old version for too long as the updates are usually very smooth and painless.
- aprdm 4y ago
  How do you upgrade and how much data do you have ?
  - dewey 4y ago
    Mostly in the 1TB range and usually by doing the replication / promotion dance. I know it gets more complicated if you have larger ones or with zero write downtime but in our case that's usually acceptable.
tmountain 4y ago
In my experience, it’s always been a huge decision to upgrade based on lots of analysis and factors. I have never worked in a company where the team routinely upgrades when a major version comes out, as it generally doesn’t make business sense. Just my 2c…
wink 4y ago
My experience is mostly that you either upgrade because it has a feature you need (then you try it out fast), if it has some performance or other nice thing (then you go for the .1) or you don't really care then you upgrade when your current version goes EOL or if you upgrade your underlying server OS (if we assume something like Ubuntu LTS, and they'd go with 14 instead of 12 or something).
- efxhoy 4y ago
  When a new version reaches RDS we start planning for the upgrade. We like the new features so we try to stay as current as we can.
jeltz 4y ago
No, but as a PostgreSQL fan I upgrade the databases every major release for my side projects. I always wait until .1 or .2 is released though.
- jacooper 4y ago
  If only they made the upgrade automatic. I use it in a container mainly, and MariaDB has an option to enable auto upgrades, but postgres requires you to manually upgrade every time.
  - jeltz 4y ago
    For small databases with lax uptime requirements I find it pretty easy to upgrade. Just stop the database, run pg_upgrade_cluster (a Debian tool) and create a new replica. For large databases where you care about uptime upgrading is usually a huge project anyway, since you have to make sure that nothing breaks due to the new version (check query plans, check if the new version changed anything, etc). You also probably want to use logical replication for the upgrade. There are things that PostgreSQL can improve of course. As an aside I recently was involved in an upgrade from 9.6 where we used logical replication and pg_dump (yes, we really used pg_dump on a 20+ TB database). The reason for using pg_dump was because we have some tables which bloated very fast meaning we could not hold a snapshot open on the master long enough to do the initial data copy so the initial copy was done with pg_dump from a paused replica. And to not lose any data during pg_dump/pg_restore we kept a logical replication slot alive (but unused until pg_restore was complete). That was an interesting project, especially since Googling did not turn up anyone who had done the same.
    - 86____ 4y ago
      I'd genuinely love to read about that if you're able to share more?
    - jtc331 4y ago
      Isn’t keeping a logical slot alive holding a snapshot?
_jal 4y ago
We are heavy on-prem Postgres users. We currently run all major versions from 10-13, and are upgrading some clusters to 14 very soon. Old versions are mostly to support internal tools that we're not upgrading for various reasons, and will go away when the apps that use them do. We generally stay one major version behind on the main production clusters. The major reason is usually just scheduling - PG upgrades are large projects that touch a lot of things, take a lot of prep and impact every part of our business, so scheduling it is always a fraught negotiation. If you don't have a lot of complexity, I recommend finding a cadence that doesn't hurt, but sticking with it. Once you get far enough behind, it becomes harder to upgrade, mostly because you also weren't upgrading surrounding tooling, so you end up changing a lot of things at once. As your environment grows in complexity and use, at some point I think you'll find that nontechnical concerns start to dominate these decisions.
film42 4y ago
Corruption of database storage is the biggest fear so most upgrades are done cautiously. Historically postgres versions were released much much slower so the upgrade cadence was slower too. That said, we've been upgrading more frequently, especially since a lot of performance features are being released. So far our upgrades have been executed flawlessly for our cluster of postgres servers (~30 postgres chains each made up of 4x large dedicated servers).
- jeff-davis 4y ago
  Postgres has released a new major version each year for a long time. The only change is that versions used to be X.Y.Z, and now it just used X.Y. Now, X gets incremented for every major version, so it seems like it's moving faster.
  - jsmith99 4y ago
    The difference is that in place upgrades aren't possible for major versions.
    - jeff-davis 4y ago
      pg_upgrade is the way to do in-place upgrade between major versions: https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
- jeltz 4y ago
  Jeff is right, PostgreSQL has released a new major yearly the last almost 25 years.
  - tinus_hn 4y ago
    That would only be possible if they started with version -10!
    - alvherre 4y ago
ellisv 4y ago
Our current policy is to update to the N-1 release. So we're currently running v13 since v14 was the latest but will soon upgrade to v14 since v15 is now the latest.
briffle 4y ago
It takes us a few months to upgrade, with planning, testing, co-ordinating downtime, etc. We upgrade every other year, to keep us from falling too far back. Very excited for this year, as we'll be using logical replication to migrate, which should make this much less risky. (since the old systems are untouched). In the past, we used pg_upgrade for in place upgrades, and even though we took snapshots before, still seemed a bit stressfull when it happened.
zhann_dc 4y ago
We do. Though we make sure to stay on the previous major. So we’ll only upgrade to PostgreSQL 15 when 16 is released.
clepto 4y ago
As someone who maintains Postgres 9.2, 9.4, and 9.6 databases. No, they do not. (It is not my will that these databases aren’t on newer versions, I would very much like them to be)
- thewataccount 4y ago
  Just curious, is the issue a time thing, or is it on a system that can't easily upgrade it - or is it a breaking changes thing that would require refactors? Basically I'm just curious what broke for you
  - tcgv 4y ago
    One main challenge is to upgrade a live large database smoothly, without downtime. I believe minor version upgrades are easily enough since they maintain disk format compatibility and you can simply spin up a new replica server under the new postgres version and then when it's synched with the main database you can make the switch and kill the old version. I'm not so sure about migrating to a new major version (ex: 9.x to 15.x).
    - whartung 4y ago
      Thing is, in this specific context, as I understand it, this is much easier to do post 9.x. Notably, Postgres has more formal support for logical replication. Logical replication is distinct from the normal replication in that it forwards actual SQL to the replica rather than binary write ahead logs. The SQL is portable across versions more than WALs are. So with logical replication you can copy a live db to a new version. Then you just need some (ideally) brief down time to swap out the servers. There are solutions for 9.x, but it got much better later on.
jeff-davis 4y ago
The version support is five years: https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ As long as you stay well ahead of that, you are fine. However, I'd strongly recommend testing your applications with new versions as they come out, or in beta (even better). You may learn about new features, find bugs in your application, find bugs in your migration processes, etc. If you bring any problems to the community right away, then by the time you actually want to upgrade, the problems may already be solved in the newer release.
- Uninen 4y ago
  According to https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ the "Final Release" (whatever that means) for v14 is November 2026, and for v15 is November 2024. I wonder if it's a typo or some LTS thing.
  - cheald 4y ago
    Almost certainly a typo. PG releases get 5 years of support.
fdr 4y ago
I don't unless I want to use something. The main consideration is hedging against an unusual problem in a new version vs. my software. This may depend on your situation. For example, if you were running a large IT org with a few staff members with a lot of installed software, the cycle to process and check correct operation of each software under your management may be long enough that you'd start soon: by the time you are working on the last few systems under your responsibility, EOL for that Postgres version may be in sight. You may also be working on a very new software project, where bugs are of lower consequence and you'd like to forestall the friction of an upgrade motivated by EOL. Or, you may find yourself in a lull in useful development and this is a good way to get ahead of maintenance, effectively reducing pressure in the future. In this case, I'd also consider upgrading...after a few months. Even Postgres has somewhat iffy .0 releases. My own experience is in vertically integrated teams where we control all the software, so we typically upgrade every second or third version, leaving over a year to resolve issues before EOL of the version we're on.
phamilton 4y ago
We try to upgrade at least once a year, during our slow season (ed-tech, so summer time is < 1/10th peak traffic). We use AWS Aurora with Postgres and we autoscale our cluster. Because most upgrades have a few perf improvements we have found that we save a bit of money every time we upgrade, either in reduced CPU (which translates to running fewer instances) or in better memory efficiency (which boosts buffer_cache_hit_ratio and therefore reduces IO).
thewataccount 4y ago
I manage two separate projects at my work, I would never upgrade to a .0 update, but we do every update it either when there's features we want, over every ~2~3 years for the general improvements. We use django and some other stuff that has very decent support for postgres, and in my experience postgres updates rarely have breaking changes that require much if any changes for us. This also keeps us from being "trapped" on an old version. I reiterate from all my systems management - do not update to a .0 release of anything unless there is a critical security update and you're forced to. EDIT: Oh directly for the question - eh depends. Every 2 to 3 max, and usually after it's tested by others more. And new projects generally use whatever is newest unless it's a .0 release.
- frant-hartm 4y ago
  Note that vendors know this and some release few patch versions quickly with only few patches to make people move to newest version (and get the community test it by using it). No idea if Postgres is one of these.
  - alvherre 4y ago
    Postgres has a strict schedule of quarterly bugfix releases, and only deviates from that when serious bugs occur (very rare). So, no.
tomputer 4y ago
Where I work we've recently upgraded many PostgreSQL servers from 9.x/10.x/11.x to 14.x. A very cool PostgreSQL feature is that, if you install a second instance on the same server, it will by default use a different path and port to run the second instance. root@example:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 14 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log Now you can dump and import databases on the second instance and test your applications with the new version. If it all works as expected, use pg_upgrade[1] to migrate everything to the new instance and switch the ports after the migration. I have only done this migration process on Debian systems but in my experience it worked fantastic! [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
nijave 4y ago
Depends on the app but usually don't major version upgrade unless the current version is losing support or there's a compelling new feature. I wouldn't run on the latest until a few point releases are out (say .3 or .4). I haven't worked on anything recently with a good performance testing system so that weighs in a bit, too (there's not a high degree of confidence the app won't hit a weird perf regression or edge case) If it's using a managed service, that weighs in, too. Something like AWS RDS Postgres usually has a 6 month lag + give it some additional time to work out any AWS control plane "surprises"

by morley 4y ago

ianbutler 4y ago

I don't think I've worked in an environment where we've upgraded for every release. New projects may start on that newer version, but generally speaking for older projects there's a cost calculation done for the newer features versus the lift required to do the upgrade and ensure it doesn't introduce any regressions. Someone already mentioned the view permissions shift here which looks at caller permissions versus the view creator permissions, that will be compelling in a lot of cases so like this is something that I'd probably raise internally for my team for a few of the apps we maintain and then have a back and forth with the principals, if we like it then go through our current list of work with a PM and our manager and see if it makes sense to do now with the current pipeline of work etc.

wahnfrieden 4y ago

.1+ releases might be best with pg for quality assurance

dewey 4y ago

This probably depends on your database, but with Postgres we usually don't stay on the old version for too long as the updates are usually very smooth and painless.

aprdm 4y ago
How do you upgrade and how much data do you have ?
- dewey 4y ago
  Mostly in the 1TB range and usually by doing the replication / promotion dance. I know it gets more complicated if you have larger ones or with zero write downtime but in our case that's usually acceptable.

tmountain 4y ago

In my experience, it’s always been a huge decision to upgrade based on lots of analysis and factors. I have never worked in a company where the team routinely upgrades when a major version comes out, as it generally doesn’t make business sense. Just my 2c…

wink 4y ago

My experience is mostly that you either upgrade because it has a feature you need (then you try it out fast), if it has some performance or other nice thing (then you go for the .1) or you don't really care then you upgrade when your current version goes EOL or if you upgrade your underlying server OS (if we assume something like Ubuntu LTS, and they'd go with 14 instead of 12 or something).

efxhoy 4y ago
When a new version reaches RDS we start planning for the upgrade. We like the new features so we try to stay as current as we can.

jeltz 4y ago

No, but as a PostgreSQL fan I upgrade the databases every major release for my side projects. I always wait until .1 or .2 is released though.

jacooper 4y ago
If only they made the upgrade automatic. I use it in a container mainly, and MariaDB has an option to enable auto upgrades, but postgres requires you to manually upgrade every time.
- jeltz 4y ago
  For small databases with lax uptime requirements I find it pretty easy to upgrade. Just stop the database, run pg_upgrade_cluster (a Debian tool) and create a new replica. For large databases where you care about uptime upgrading is usually a huge project anyway, since you have to make sure that nothing breaks due to the new version (check query plans, check if the new version changed anything, etc). You also probably want to use logical replication for the upgrade. There are things that PostgreSQL can improve of course. As an aside I recently was involved in an upgrade from 9.6 where we used logical replication and pg_dump (yes, we really used pg_dump on a 20+ TB database). The reason for using pg_dump was because we have some tables which bloated very fast meaning we could not hold a snapshot open on the master long enough to do the initial data copy so the initial copy was done with pg_dump from a paused replica. And to not lose any data during pg_dump/pg_restore we kept a logical replication slot alive (but unused until pg_restore was complete). That was an interesting project, especially since Googling did not turn up anyone who had done the same.
  - 86____ 4y ago
    I'd genuinely love to read about that if you're able to share more?
  - jtc331 4y ago
    Isn’t keeping a logical slot alive holding a snapshot?

_jal 4y ago

We are heavy on-prem Postgres users. We currently run all major versions from 10-13, and are upgrading some clusters to 14 very soon. Old versions are mostly to support internal tools that we're not upgrading for various reasons, and will go away when the apps that use them do. We generally stay one major version behind on the main production clusters. The major reason is usually just scheduling - PG upgrades are large projects that touch a lot of things, take a lot of prep and impact every part of our business, so scheduling it is always a fraught negotiation. If you don't have a lot of complexity, I recommend finding a cadence that doesn't hurt, but sticking with it. Once you get far enough behind, it becomes harder to upgrade, mostly because you also weren't upgrading surrounding tooling, so you end up changing a lot of things at once. As your environment grows in complexity and use, at some point I think you'll find that nontechnical concerns start to dominate these decisions.

film42 4y ago

Corruption of database storage is the biggest fear so most upgrades are done cautiously. Historically postgres versions were released much much slower so the upgrade cadence was slower too. That said, we've been upgrading more frequently, especially since a lot of performance features are being released. So far our upgrades have been executed flawlessly for our cluster of postgres servers (~30 postgres chains each made up of 4x large dedicated servers).

jeff-davis 4y ago
Postgres has released a new major version each year for a long time. The only change is that versions used to be X.Y.Z, and now it just used X.Y. Now, X gets incremented for every major version, so it seems like it's moving faster.
- jsmith99 4y ago
  The difference is that in place upgrades aren't possible for major versions.
  - jeff-davis 4y ago
    pg_upgrade is the way to do in-place upgrade between major versions: https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html
jeltz 4y ago
Jeff is right, PostgreSQL has released a new major yearly the last almost 25 years.
- tinus_hn 4y ago
  That would only be possible if they started with version -10!
  - alvherre 4y ago

ellisv 4y ago

Our current policy is to update to the N-1 release. So we're currently running v13 since v14 was the latest but will soon upgrade to v14 since v15 is now the latest.

briffle 4y ago

It takes us a few months to upgrade, with planning, testing, co-ordinating downtime, etc. We upgrade every other year, to keep us from falling too far back. Very excited for this year, as we'll be using logical replication to migrate, which should make this much less risky. (since the old systems are untouched). In the past, we used pg_upgrade for in place upgrades, and even though we took snapshots before, still seemed a bit stressfull when it happened.

zhann_dc 4y ago

We do. Though we make sure to stay on the previous major. So we’ll only upgrade to PostgreSQL 15 when 16 is released.

clepto 4y ago

As someone who maintains Postgres 9.2, 9.4, and 9.6 databases. No, they do not. (It is not my will that these databases aren’t on newer versions, I would very much like them to be)

thewataccount 4y ago
Just curious, is the issue a time thing, or is it on a system that can't easily upgrade it - or is it a breaking changes thing that would require refactors? Basically I'm just curious what broke for you
- tcgv 4y ago
  One main challenge is to upgrade a live large database smoothly, without downtime. I believe minor version upgrades are easily enough since they maintain disk format compatibility and you can simply spin up a new replica server under the new postgres version and then when it's synched with the main database you can make the switch and kill the old version. I'm not so sure about migrating to a new major version (ex: 9.x to 15.x).
  - whartung 4y ago
    Thing is, in this specific context, as I understand it, this is much easier to do post 9.x. Notably, Postgres has more formal support for logical replication. Logical replication is distinct from the normal replication in that it forwards actual SQL to the replica rather than binary write ahead logs. The SQL is portable across versions more than WALs are. So with logical replication you can copy a live db to a new version. Then you just need some (ideally) brief down time to swap out the servers. There are solutions for 9.x, but it got much better later on.

jeff-davis 4y ago

The version support is five years: https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ As long as you stay well ahead of that, you are fine. However, I'd strongly recommend testing your applications with new versions as they come out, or in beta (even better). You may learn about new features, find bugs in your application, find bugs in your migration processes, etc. If you bring any problems to the community right away, then by the time you actually want to upgrade, the problems may already be solved in the newer release.

Uninen 4y ago
According to https://www.postgresql.org/support/versioning/ https://www.postgresql.org/support/versioning/ the "Final Release" (whatever that means) for v14 is November 2026, and for v15 is November 2024. I wonder if it's a typo or some LTS thing.
- cheald 4y ago
  Almost certainly a typo. PG releases get 5 years of support.

fdr 4y ago

I don't unless I want to use something. The main consideration is hedging against an unusual problem in a new version vs. my software. This may depend on your situation. For example, if you were running a large IT org with a few staff members with a lot of installed software, the cycle to process and check correct operation of each software under your management may be long enough that you'd start soon: by the time you are working on the last few systems under your responsibility, EOL for that Postgres version may be in sight. You may also be working on a very new software project, where bugs are of lower consequence and you'd like to forestall the friction of an upgrade motivated by EOL. Or, you may find yourself in a lull in useful development and this is a good way to get ahead of maintenance, effectively reducing pressure in the future. In this case, I'd also consider upgrading...after a few months. Even Postgres has somewhat iffy .0 releases. My own experience is in vertically integrated teams where we control all the software, so we typically upgrade every second or third version, leaving over a year to resolve issues before EOL of the version we're on.

phamilton 4y ago

We try to upgrade at least once a year, during our slow season (ed-tech, so summer time is < 1/10th peak traffic). We use AWS Aurora with Postgres and we autoscale our cluster. Because most upgrades have a few perf improvements we have found that we save a bit of money every time we upgrade, either in reduced CPU (which translates to running fewer instances) or in better memory efficiency (which boosts buffer_cache_hit_ratio and therefore reduces IO).

thewataccount 4y ago

I manage two separate projects at my work, I would never upgrade to a .0 update, but we do every update it either when there's features we want, over every ~2~3 years for the general improvements. We use django and some other stuff that has very decent support for postgres, and in my experience postgres updates rarely have breaking changes that require much if any changes for us. This also keeps us from being "trapped" on an old version. I reiterate from all my systems management - do not update to a .0 release of anything unless there is a critical security update and you're forced to. EDIT: Oh directly for the question - eh depends. Every 2 to 3 max, and usually after it's tested by others more. And new projects generally use whatever is newest unless it's a .0 release.

frant-hartm 4y ago
Note that vendors know this and some release few patch versions quickly with only few patches to make people move to newest version (and get the community test it by using it). No idea if Postgres is one of these.
- alvherre 4y ago
  Postgres has a strict schedule of quarterly bugfix releases, and only deviates from that when serious bugs occur (very rare). So, no.

tomputer 4y ago

Where I work we've recently upgraded many PostgreSQL servers from 9.x/10.x/11.x to 14.x. A very cool PostgreSQL feature is that, if you install a second instance on the same server, it will by default use a different path and port to run the second instance. root@example:~# pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 11 main 5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log 14 main 5433 online postgres /var/lib/postgresql/14/main /var/log/postgresql/postgresql-14-main.log Now you can dump and import databases on the second instance and test your applications with the new version. If it all works as expected, use pg_upgrade[1] to migrate everything to the new instance and switch the ports after the migration. I have only done this migration process on Debian systems but in my experience it worked fantastic! [1] https://www.postgresql.org/docs/current/pgupgrade.html https://www.postgresql.org/docs/current/pgupgrade.html

nijave 4y ago

Depends on the app but usually don't major version upgrade unless the current version is losing support or there's a compelling new feature. I wouldn't run on the latest until a few point releases are out (say .3 or .4). I haven't worked on anything recently with a good performance testing system so that weighs in a bit, too (there's not a high degree of confidence the app won't hit a weird perf regression or edge case) If it's using a managed service, that weighs in, too. Something like AWS RDS Postgres usually has a 6 month lag + give it some additional time to work out any AWS control plane "surprises"