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

271–280 of 452 posts

Re: Why does everyone run ancient Postgres versions?

#271

Post by kardianos »

noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

There are two primary issues with PostgreSQL:

* Upgrades

* Database Protocol

MS SQL Server does better on both of these fronts: Upgrades, when running MS SQL Server in a container, is rebooting the database with the new database engine version. The DBMS sees the old files, upgrades them in place and starts the DB completely seamlessly 100% of the time.

The MS SQL Server protocol, TDS, supports native named parameters, executes the T-SQL PL inline with SQL. TDS also supports inline query cancellations because the protocol is framed, and both T-SQL and TDS supports returning multiple result sets from a single query.

But yes, whenever I see PG docs saying, major versions require an export and re-load, I shudder.

Re: Why does everyone run ancient Postgres versions?

#272

Post by [deleted] »

literalAardvark wrote: post #268
forinti wrote: post #255

Earlier quoted context omitted.

I have always compiled from source so that I can have more than one version in place. The installation takes less than 1GB and is quite easy and fast to compile and I've never had any issues with upgrades. If you forget to compile an extension, you can do it later, copy the .so to the right folder, and you don't even have to restart the cluster. If you don't want to use pg_upgrade, you can dump from one cluster and p…

> It couldn't be easier If you have a trivial database, yes. If you have a large, complicated database that takes hours/days to restore and has an HA SLA, very hell no.

[deleted]

Re: Why does everyone run ancient Postgres versions?

#274

Post by JeremyNT »

elric wrote: post #110

Lots of dogmatism in this discussion, it seems. A couple of things: 1. Most psql deployments are not exposed to the interwebz, they are typically only accessible to the applications that need them by virtue of network setup (firewalls etc). This limits the attack vector to whatever the application does. Good. 2. Distro vendors (RHEL et al) often stick to major psql release for the lifecycle of the OS version. If the…

I think this is a really good take. It all boils down to "if it ain't broke don't fix it."

No matter how easy it is, it takes more effort and thought to do an upgrade than it does to not do an upgrade at all, and for most users the upside is basically invisible if it exists at all.

Re: Why does everyone run ancient Postgres versions?

#275

Post by justinclift »

The_Colonel wrote: post #108
o11c wrote: post #100

Earlier quoted context omitted.

From what I can tell, MySQL is supposed to be safe since 2018 if you have no data from before 2010. The fact that you still can't use DDL in transactions makes life exceedingly painful , but it's technically safe if you write your migration code carefully enough.

Lack of transactional DDL is certainly painful, but not unique for MySQL. Oracle doesn't support it either.

Both databases are owned by the same company. Wonder if it's something inherent to the company which has keep that limitation going?

Re: Why does everyone run ancient Postgres versions?

#276

Post by DonnyV »

One of the many things I love about Mongodb is the upgrade process. Shutdown server, delete old exes, copy new ones and start server. Done

Any needed updates to databases are done by the new exe, automatically. Obviously backup everything before you do the upgrade. I've never had one fail.

Re: Why does everyone run ancient Postgres versions?

#277

Post by justinclift »

buro9 wrote: post #162

Upgrading isn't automatic. Let me check what I'm on... brb... Postgres 14. Because it's not automatic I leave it, I leave it until it's so unsupported that I must upgrade the whole system, then I build a new system with a new Postgres and I migrate the old to the new. I want, so badly, for Postgres to just automatically update itself, that a new binary just works with the data directory of an old version, and that if…

If you use Docker, then the pgautoupgrade project might be your kind of thing:

https://github.com/pgautoupgrade/docker-pgautoupgrade

Re: Why does everyone run ancient Postgres versions?

#278

Post by ktosobcy »

noirscape wrote: post #156

Because the actual process of upgrading Postgres is terrible. I say this as someone who absolutely loves using it, but the actual process of upgrading Postgres is something that takes significant downtime, is error-prone and you're often better off just dumping all the databases and reimporting them in a new folder. (A good idea in general since it'll vacuum and compact indexes as well if you do it, combining a few m…

This! I already mentioned this a while back and was basically berated (by someone that seemed like a dev/close to dev) that current setup is just fine and because of postgress complexity (extensions) it has to be done that way... and while I like postgress a lot it's quite annoying that the upgrade is such a terrible experience... :|

Re: Why does everyone run ancient Postgres versions?

#279

Post by p5v »

It’s always a trade-off of how much you’ll get from an upgrade, vs. how much time, effort, and pain you’ll have to invest to do the switch. Postgres is at a stage, where a single version can easily outlive the duration of the software you’ve built with it. Let’s be honest here, a vast majority of software doesn’t need a fraction of the requirements we think it does, frankly, because no one uses it to the extent that latest and greatest features would make the experience more pleasant.

Re: Why does everyone run ancient Postgres versions?

#280

Post by comprev »

Dalewyn wrote: post #128
atoav wrote: post #123

Earlier quoted context omitted.

Also: 5. If your IT department is spread thin already and that old version is running fine, the incentive to potentially create more work for yourself is not gigantic.

One of the first laws of the universe that a good engineer learns is: Do not fix what is not broken. And no, being old is not broken.

Preventative maintenance is a thing in both software and hardware world.

It's often lower risk to apply frequent smaller changes than wait years for one enormous "bundled" upgrade.

Post reply on HN