PostgreSQL without Homebrew on macOS
If you have ever worked with PostgreSQL on macOS, chances are you have used the version shipped by Homebrew. While Homebrew is definitely one of the simplest ways to manage software on macOS and Linux (yes, Homebrew works on Linux just as fine), the development experience with PostgreSQL installed with Homebrew can be pretty bad.
The first issue you may encounter when using PostgreSQL from Homebrew is that sometimes when you turn off your computer, PostgreSQL just won’t start when you boot it up again.
This can be solved by deleting a file called
postmaster.pid, which is the file where the PostgreSQL service stores the identifier of the server process.
This is somewhat problematic, and every time I encounter this problem, I have to specifically search for this StackOverflow thread to see where I can try to look for that file.
Another, potentially more serious issue, is that with some commands, Homebrew automatically upgrades all installed packages. Unlike apt or pacman, it doesn’t give you a heads-up about the packages it’s about to upgrade. This is fine with most software, but with PostgreSQL, the data format is slightly different for each major version.
Once Homebrew upgrades PostgreSQL to the next major version, you will find that your existing data folder is now incompatible with the version installed, and that you have to upgrade it (which is difficult) or purge all data folders and create a new cluster. This is made even more problematic by the fact that PostgreSQL’s data is spread across multiple folders and differs by system architecture. It is even worse when it happens in the middle of a busy workday.
One solution to this problem is using a Dockerized version of PostgreSQL. The easiest way to install Docker is using Homebrew:
brew install --cask docker
This will install the Docker desktop application using the official installer. After the installation, you will need to run the newly installed desktop app to configure your system.
Setting up services
Then, create a
docker-compose.yml in your home directory with the following contents:
version: '3.9' services: pg: ports: - 5432:5432 image: postgres:14.4-bullseye restart: unless-stopped environment: POSTGRES_USER: postgres POSTGRES_DB: postgres POSTGRES_PASSWORD: postgres volumes: - "./.data/pg:/var/lib/postgresql/data"
This service definition tells docker to run PostgreSQL 14.4 using the official Docker image, with the user
postgres, and default database
The version is explicitly specified in order to avoid unplanned upgrades.
The database server will be listening on port 5432.
All persistent storage will go into
~/.data/pg, so in case you break something, you can just remove the whole directory and start from scratch.
restart: unless-stopped tells docker to restart the container after reboot, unless you explicitly stop it.
You can start the services in the background using docker-compose:
docker compose -f $HOME/docker-compose.yml up -d
Using command line tools
With this approach, we don’t need the
postgresql Homebrew package installed on the system, and the database server will run just fine.
However, this way we do not get the commonly used command line tools like
createdb, and the like.
These are shipped in a separate package called
To install and link these programs:
brew install libpq brew link --force libpq
When PostgreSQL is running inside of a Docker container, we cannot connect to the server using a UNIX socket, and we need to resort to a network connection.
In order to run
psql with the host
localhost and user
postgres, we would have to use a command like this:
PGPASSWORD=postgres psql -U postgres -h localhost
Luckily, all default connection parameters can easily be set using environment variables. You are going to need at least the following:
export PGHOST=localhost export PGUSER=postgres export PGPASSWORD=postgres
You can teach your shell to automatically set these variables for each session by putting these lines in your shell configuration file (
~/.bashrc if you’re using bash,
~/.zshrc if you are using zsh), or using a tool like direnv.
Once you source this file, you can connect to PostgreSQL without any additional parameters:
karols-mbp:~$ psql psql (14.4) Type "help" for help. postgres=# select version(); version ----------------------------------------------------------------------------------------------------------------------------------- PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit (1 row)