brew upgrade postgres?
If so, I feel your pain. Hopefully I can help.
mv /usr/local/var/postgres/ /usr/local/var/postgres.old # Get old versions brew tap petere/postgresql brew install email@example.com brew install firstname.lastname@example.org # Install 9.6 db initdb /usr/local/var/postgres/ # Stop the running 9.6 instance sudo brew services stop postgres # Migrate to new version # may need to look in to /usr/local/Cellar to get exact directories pg_upgrade -b /usr/local/Cellaremail@example.com/9.3.16/bin/ -B /usr/local/Cellar/postgresql/9.6.2/bin/ -d /usr/local/var/postgres.old/ -D /usr/local/var/postgres # Drink a coffee (or beer) or three or six while there's a migration # Start up the server brew services start postgres # Verify server is running psql
I had two old databases that used PostGIS. They caused the migration to fail. Attempts to get postgis to install on the 9.3 version of postgres failed. Unfortunately I don’t have a fix for that, but can tell you how to at least delete the offending databases if you don’t care about them, like I didn’t. If you do need the postgis enabled databases, the documentation for the tap indicates that you can use a utility called pex to install things, but I didn’t bother figuring that out.
To delete the old offending tables:
brew unlink firstname.lastname@example.org brew link -f email@example.com # Need to start manually beacuse the files aren't where we expect pg_ctl -D /usr/local/var/postgres.old/ -l /usr/local/var/postgres/server.log start psql # Do DROP DATABASE etc pg_ctl -D /usr/local/var/postgres.old/ stop brew unlink firstname.lastname@example.org brew link -f email@example.com
Brew has given more than enough I can’t be too mad at it for too long, but I’m a little disappointed that:
It silently upgraded readline which introduced a bunch of errors to old versions of software. I actually thought this blew up when I did an OS upgrade to OSX.
It makes no attempt to warn me that upgrading from 9.3 requires some serious manual intervention, the second time it’s silently updated a version of software to a version that’s incompatible with everything I have installed.
It allows no way out-of-the-box at this point for me to install the 9.3 binaries to do the upgrade from 9.3 to 9.4.
It seems to have broken old ways of installing old software by checking out an old commit of a particular brew file. Even though I tracked down the commit for 9.3, we now seem to autoupgrade and always install 9.6.2.
It’s inexplicable (to me) deprecations of vast swaths of homebrew commands. I’m sure the developers have their reasons and if I was on top of things it would make sense, but it’s frustrating to find four alternate solutions on StackOverflow that should magically fix your problem only to be told politely by brew, “Sorry, that command just doesn’t work anymore. Try again!”
It would be nice if there was some sort of
Are you sure?(Y/N) prompt for these more disruptive upgrades, and wish the 9.3 version of postgres would have floated around a bit more so I could have fixed the problem without resorting to third-party taps.
For not properly investigating the broken readline stuff I’d been dealing with off and on for a bit and ‘fixed’ by rebuilding my rubies in rvm.
Running brew commands nilly willy.
Not having a set up where it wasn’t a problem to blow away my dev dbs and start from scratch. I should have either had backups, or been able to work from clean databases without affecting my productivity.
Who’s homebrew tap saved the day. Thanks Peter!
That twelve character bug fix worked! I’m off to my next coding adventure. Maybe now would be a good time to upgrade to Sierra. What’s the worst that could happen?
I just tried installing pg_top, a utility that lets you view active connections to your database. It should be a simple tool, but brew decides to:
Upgrade from 9.6.2 to 10.4 without saying anything!
Restart the service instead of leaving the old background one in place!
Makes no attempt to migrate existing databases to the new version!
Fortunately, this time brew was at least kind enough to keep the old version around. I was able to fix it with:
brew switch postgres 9.6.2 brew services postgres stop brew services postgres start
Brew, please quit auto-upgrading services in a way that leaves things in an inconsistent state. If I need to manually migrate between major postgres versions, you shouldn’t just automatically update when I’m installing a small utility that has postgres as a dependency.