Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Why does everyone run ancient Postgres versions?

neon.tech

Post reply on HN
Print view View original

361–370 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#361

Post by Tostino »

enraged_camel wrote: post #341
ttfkam wrote: post #335

Earlier quoted context omitted.

Upgrading from v11 to v16 is not materially different in Postgres from v14 to v16. Same tools. Same strategies.

We are planning to upgrade from 11 to 17 soon. Even thinking about it is giving me ulcers. Our infra provider said we actually need to upgrade to 13 first, and then to 17. They did not provide a reason.

I went through a postgres 10 > 16 upgrade recently. What made it easier was just doing a test run of the upgrade process.

Did a restore to a stage environment, worked on my upgrade scripts until I was happy (deployed to VMs with ansible, so manual work to write the upgradeprocessfor me), restored again and ran the upgrade process fresh, and then tested my application, backup scripts, restores, etc. Had everything working entirely smoothly multiple times before pulling the trigger in production.

No stress at all when we did it in prod.

Re: Why does everyone run ancient Postgres versions?

#362

Post by stickfigure »

evanelias wrote: post #52
cosmotic wrote: post #47

Earlier quoted context omitted.

It's not just DDL that isn't transactional, there's a whole bunch of other things that aren't. And they break the transactionality silently. It's like an obstical course where bumping into something might be fatal.

What specific non-DDL things are you referring to here? Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES. This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html . Hardly an "obstical course".

"Aside from missing his head, the patient appears to be in fine shape."

Re: Why does everyone run ancient Postgres versions?

#363

Post by hu3 »

sgarland wrote: post #260
erik_seaberg wrote: post #81

Earlier quoted context omitted.

Early MySQL versions made egregious design choices like quietly ignoring missing foreign keys and enum typos, truncating long strings, and randomly choosing rows from groups. https://web.archive.org/web/20230922210124/https://grimoire....

Yeah, it was bad. What kills me is SQLite has its own absurd set of gotchas [0] yet is seen as amazing and wonderful by devs. PKs can have NULLs? Sure! Strings can have \0 in the middle of them? Why not? FKs aren’t enforced by default? Yeah, who needs referential integrity, anyway? My only conclusion is that the majority of devs don’t actually read documentation, and rely purely on the last blog post they read to inf…

Understanding and memorizing shortcomings and quirks takes time and effort.

Most devs just go with whatever the influencer du jour says is good.

"Nobody ever got fired for choosing insert_currently_hyped_tech_here"

Re: Why does everyone run ancient Postgres versions?

#364

Post by evanelias »

stickfigure wrote: post #362
evanelias wrote: post #52

Earlier quoted context omitted.

What specific non-DDL things are you referring to here? Aside from DDL, the only other major ones are manipulating users/grants, manipulating replication, a small number of other administrative commands, and LOCK TABLES. This is all documented very clearly on https://dev.mysql.com/doc/refman/8.4/en/implicit-commit.html . Hardly an "obstical course".

"Aside from missing his head, the patient appears to be in fine shape."

That hardly seems equivalent. Why do you need to e.g. reconfigure replication inside of a transaction in the first place?

The lack of transactional DDL is a totally valid complaint, but the non-DDL stuff is just a total head-scratcher to me. Aside from DDL, implicit commits have literally never impacted me in my 21 years of using MySQL.

Re: Why does everyone run ancient Postgres versions?

#365

Post by ttfkam »

evanelias wrote: post #321
ttfkam wrote: post #299

Earlier quoted context omitted.

> none of them could articulate what part of its feature set they actually needed to use. Transactional DDL: migration errors never leave the database in an intermediate/inconsistent state. Range types + exclusion constraint: just no way to do this in MySQL without introducing a race condition. Writeable CTEs: creating insert/update/delete pipelines over multiple tables deterministically. Seriously though, the RETURN…

That's a good list [1]. A handful of these are already doable in modern MySQL and/or MariaDB though. JSON can often be used in place of arrays, and JSON_TABLE in both MySQL and MariaDB converts JSON into tabular data. MySQL supports multi-valued indexes over JSON, where each row can have multiple index entries (or no entries, e.g. partial index). MariaDB has built-in convenience types for ipv4, ipv6, and uuid. Or in…

> JSON can often be used in place of arrays

This is like storing UUIDs as text. You lose type information and validation. It's like storing your array as a comma-delimited string. It can work in a pinch, but it takes up more storage space and is far more error prone.

> convenience types for ipv4, ipv6, and uuid.

That's nice to see. A shame you have to decide ahead of time whether you're storing v6 or v4, and I don't see support for network ranges, but a definite improvement.

> MariaDB supports RETURNING.

That's honestly wonderful to see. Can these be used inside of CTEs as well for correlated INSERTs?

Re: Why does everyone run ancient Postgres versions?

#366

Post by jlokier »

gramakri2 wrote: post #258
eru wrote: post #246

Earlier quoted context omitted.

Of course, sqlite is even easier on the sys-admins, (but not necessarily the right tool for the job.)

How does one backup a sqlite without stopping the app or disrupting service? I couldn't find a simple answer to this

There is a generic way to do it that works with SQLite and other databases, including Postgres. On Linux, take a filesystem or block device coherent snapshot, take your backup of all the SQLite files from the snapshot, then delete the snapshot.

The app or service continues to run without disruption, and the backup is a coherent database image. Perhaps you have other state in addition to the database, or multiple databses. For example cache files. This covers them too, if they are all in the same snapshot.

There are many ways to take a snapshot: ZFS and btrfs offer a filesystem command, and any Linux filesystem, such as ext4, can be snapshotted with LVM or LVM-thin. Well known cloud providers like AWS, GCP, Azure also provide ways to snapshot block devices, through their APIs. However, to ensure a coherent image, it may be necessary to use the Linux `fsfreeze` command around API calls.

The database backup files can have incomplete transactions, but if the files are restored it will be as if the OS was abruptly stopped at the moment of the snapshot. SQLite and other good databases are designed to recover well from this sort of abrupt stop, without corrupting the database. They clean up incomplete transactions on recovery.

Re: Why does everyone run ancient Postgres versions?

#367

Post by ttfkam »

Tostino wrote: post #361
enraged_camel wrote: post #341

Earlier quoted context omitted.

We are planning to upgrade from 11 to 17 soon. Even thinking about it is giving me ulcers. Our infra provider said we actually need to upgrade to 13 first, and then to 17. They did not provide a reason.

I went through a postgres 10 > 16 upgrade recently. What made it easier was just doing a test run of the upgrade process. Did a restore to a stage environment, worked on my upgrade scripts until I was happy (deployed to VMs with ansible, so manual work to write the upgradeprocessfor me), restored again and ran the upgrade process fresh, and then tested my application, backup scripts, restores, etc. Had everything wor…

Yep, that was our strategy as well: just keep iterating until the script run cleanly from start to finish without errors.

Re: Why does everyone run ancient Postgres versions?

#368

Post by justinclift »

icedchai wrote: post #323
justinclift wrote: post #277

Earlier quoted context omitted.

If you use Docker, then the pgautoupgrade project might be your kind of thing: https://github.com/pgautoupgrade/docker-pgautoupgrade

Thank you for this! I was looking for an "easy" way to upgrade a dev container DB.

Thanks. There's a small group of us (PG DevOps type people) who have been working on it for over a year now, and it's come together pretty well.

It doesn't yet automatically upgrade people's PG extensions, but that's on the ToDo list and has initial code in a PR. So that'll likely start happening in a few weeks too. :)

Re: Why does everyone run ancient Postgres versions?

#369

Post by cafard »

chasil wrote: post #17

In Oracle, ALTER TABLE MOVE in 8i was a godsend, finally enabling a table reorganization without export/import. My timid management forbade an upgrade from Oracle 7.3.4 until 2013. It was agony to remain on that museum piece for as long as we did. I am upgrade-minded, but my management is not. I always lose. I am retiring in two years. I will not miss their problems, not at all. Edit: Oracle 10g was the last release…

Seriously, Oracle 7.3.4 in 2013? I am impressed.
Post reply on HN