Developers using Postgres on Mac — I’d suggest using Postgres.app, instead of Homebrew to install and manage databases. Postgres.app allows you to run specific, and multiple versions easily and won’t auto-upgrade your Postgres to a version you didn’t want.
I really like Homebrew in general for package management on macOS. That said, there is one key behavior of Brew that really irks me, which is that by default it will upgrade all your installed packages any time you do a new install or update! This is terrible for databases in particular. It is very likely you don’t want your DB version updated, as you should have that version match what your deployed software uses. Additionally, often when updating DB versions, it means you need to update the datastore, which can sometimes be a little time consuming or tedious. The version argument is the big one for me though. I typically am deploying production software to the cloud, where there are specific versions of databases provided by the cloud vendors. I want my local environment to use at least the same major version so I get no surprises, and equivalent behavior.
For example, AWS Aurora Postgres Serverless is version 10 of Postgres. But even the regular RDS versions aren’t the latest (i.e. what you’d get with a default Brew install). Combine this with the PostGIS extension, and there are considerable differences between versions 10, 11, and 12 for example. Brew also doesn’t really allow you (easily anyway) to install multiple different versions. And finally, I’ve found the DB upgrade scripts included there don’t work (at least in the upgrades I’ve tried — which is backed up by various threads on Stack Overflow and similar complaining of the same).
Postgres.app to the rescue! It’s a small little app that manages Postgres installs and DBs on macOS. You can have multiple different versions, and the app provides a simple way to start/stop the DB’s. It also of course will never upgrade the version out from under you. Here’s how they describe it:
Postgres.app has a beautiful user interface and a convenient menu bar item. You never need to touch the command line to use it — but of course we do include all the necessary command line tools and header files for advanced users.
Postgres.app can install minor updates automatically, so you get bugfixes as soon as possible.
Another thing that is nice was when I needed to move the database data from one hard drive to another. This just amounted to me stopping the DB, moving the data (Postgres.app shows you directly where it lives), updating the data location in Postgres.app, and restarting the DB.
While Postgres.app is generally excellent, I did run into two minor issues.
This is only somewhat Postgres.app related, or possibly not even, but it was a hiccup I had with it, that I didn’t have with a Brew install. That is, installing osm2pgsql. This is a GIS tool for loading OpenStreetMap data into Postgres. Normally this is a fairly easy install, or on Linux systems, it’s usually easily installed with apt-get or yum, etc. But I found it didn’t build properly by default. Turns out there was a simple fix though. When you build osm2pgsql, use the
--ignore-dependencies flag if you run into trouble with it saying you haven’t installed all the dependencies, yet you have. Apologies that I don’t have example output of the failure, as I did this a while ago, but I wanted to put something out there in case others run into this.
It doesn’t always start…
The other issue I ran into is that after a reboot, the databases aren’t always started. But, as it turns out, this was my error — you need to click the “Automatically start server” checkbox for the particular Postgres instance as seen here:
Note that this is NOT the setting in Postgres.app’s preferences that says “Automatically start Postgres on login.” That simply starts Postgres.app on login, but won’t start up the actual Postgres server(s) you have.
As an alternative, you could use Docker to run your DB. I personally just prefer not to have a bunch of Docker images running all the time, and find the DB management easier when it’s running directly on my machine.
You could also use cloud DB’s, but of course that requires a network connection and will certainly cost you more, as well as be slower than running locally.
That’s it. If you develop/work on macOS and are using Postgres, check it out. You’ll at minimum have a more stable Postgres install, with the option to easily install additional versions.