4 ms·
From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a
by mschuster91 4y ago
From an admin perspective: Updates are a hot, complex mess which means I put them off until it's no longer feasible to do so (=because some software requires a newer version).
MySQL is easy: apt-get update/docker stop && docker rm && docker run/kubectl apply, depending on your stack that is literally all you need to do.
PostgreSQL in contrast is hell. You have to shut down the existing database server, install the new one in parallel, manually do the upgrade (that involves copying the whole dataset), remove the old stuff, and then start back up again. Or you have to export the whole database into an SQL dump and import it on a fresh instance.
In any case, way more involved, way more likely to go bonkers, and way, WAY more downtime needed.
- reitanuki 4y agoYou do not need to copy the DB, see pg_upgrade
- mschuster91 4y agoThat depends on the circumstances. To quote the manpage: > If you use link mode, the upgrade will be much faster (no file copying) and use less disk space, but you will not be able to access your old cluster once you start the new cluster after the upgrade. Link mode also requires that the old and new cluster data directories be in the same file system. (Tablespaces and pg_wal can be on different file systems.) Clone mode provides the same speed and disk space advantages but does not cause the old cluster to be unusable once the new cluster is started. Clone mode also requires that the old and new data directories be in the same file system. This mode is only available on certain operating systems and file systems.
- reitanuki 4y agoI'm not familiar this much with MySQL then: if you upgrade your MySQL database, can you still roll back to the old version? If you can't then this is the same restriction when you use the `pg_upgrade` tool in link mode. (Note that one approach if concerned about the potential for downtime if it goes wrong might be to `pg_upgrade` your secondary in a hot-standby replication setup, then failover and `pg_upgrade` the former primary. In practice, `pg_upgrade` runs very quickly — seconds even on multi-terabyte databases — afaik it doesn't touch the actual data but just the metadata, but read the man pages for more intricate details on that :)).
- dpedu 4y ago+1 for administrative pains with postgres. My favorite example is that if you want to run a multi-node highly available postgres cluster (read: with automatic failover) you're going to have to use 3rd party software, whereas with mysql it's part of the core (group replication). I'm also not a fan of the difficult-to-remember backslash codes in the psql cli used to inspect databases (like \d+, and others) whereas mysql just uses queries ("describe table").