Brew install PostgreSQL on a Mac, and where the data folder lands

brew install postgresql succeeds and then two things go wrong in quick succession. Typing psql says the command is not found, or worse, it connects and reports a major version nobody asked for. Neither is a broken install. Both follow from one fact that the command line hides: there is no formula called postgresql. The name is an alias, it points at a different formula every year or so, and the formula it points at is deliberately not linked into the places a shell looks.

Getting this right at install time is worth a few minutes, because the thing that is hardest to change later is not the binary. It is the data directory, whose name, locale and encoding are all decided in the first thirty seconds.

The name is an alias, and it moves

Homebrew's core tap contains no file named postgresql. It contains postgresql@18, postgresql@17, postgresql@16, postgresql@15, postgresql@14, postgresql@13 and postgresql@12. The aliases postgresql and postgres are both attached to postgresql@18, whose current stable version is 18.6.

That attachment is not permanent. The PostgreSQL Global Development Group releases a new major version containing new features about once a year, and when it does, the alias follows. A command written down in a project's setup notes therefore installs a different major version depending on when someone runs it, and nothing in the command says so.

The same policy sets the other end of each formula's life. PostgreSQL supports a major version for 5 years after its initial release, after which a final minor version ships and the software is end of life. Homebrew encodes those dates directly in the formulae, citing the versioning policy page as the source. postgresql@18 carries a deprecation date of 14 November 2030 and a disable date of 14 November 2031. postgresql@17 is dated 8 November 2029, postgresql@16 9 November 2028. postgresql@14 is already marked deprecated as of 12 November 2025 with the reason recorded as unsupported.

Write the version into the command. brew install postgresql@18 says what will be installed this year and next year, and it is also what the log of a failed CI run needs to contain in order to be readable six months later.

Keg-only, and the versioned commands nobody mentions

Every versioned PostgreSQL formula declares keg_only :versioned_formula. Homebrew's own definition of that state is precise: the formula is installed only into the Cellar and is not linked into the default prefix, which means most tools will not find it. brew info <formula> prints why a formula was made keg-only along with instructions for including it in PATH, and brew link can force the links in at the risk of shadowing macOS software.

Read that on its own and it sounds like nothing is usable without editing a shell file. The formula does something less obvious. Its post install step links the executables from the keg into the prefix with the major version appended, so a keg-only PostgreSQL 18 still puts psql-18, initdb-18, pg_dump-18 and the rest of the suite on PATH with no configuration at all. A machine with two versions installed ends up with psql-17 and psql-18 side by side in the prefix, each pointing into its own Cellar directory.

What is not guaranteed is a bare psql. On a machine where an older, non keg-only formula was linked at some point, that plain name can still resolve to the older version, which is the cause of the more confusing failure: a client that connects fine and then refuses a dump because the server is newer than the tool.

Approach	What to type	Trade-off
Versioned names	psql-18	Works immediately, unambiguous, ugly in scripts
PATH addition	add $(brew --prefix)/opt/postgresql@18/bin	Plain psql works, one version wins per shell
brew link	brew link postgresql@18	Plain names everywhere, can shadow other copies

The middle row is the usual answer for a laptop with one version. The top row is the right answer on a machine that has to talk to servers of several ages, because it makes the version part of the command rather than part of the environment.

Where the data folder lands

The data directory is var/<formula name> inside the Homebrew prefix. For PostgreSQL 18 on Apple silicon that is /opt/homebrew/var/postgresql@18, and on Intel /usr/local/var/postgresql@18, following Homebrew's documented prefixes of /opt/homebrew for macOS on Apple silicon and /usr/local for macOS on Intel. The log file sits next to it at var/log/[email protected].

The install does not leave the cluster for the user to create. The formula's caveats state that it has created a default database cluster with a specific command:

initdb --locale=en_US.UTF-8 -E UTF-8 $HOMEBREW_PREFIX/var/postgresql@18

Two decisions are baked in there. The locale is en_US.UTF-8 and the encoding is UTF-8. Neither can be changed on an existing cluster, so a project that needs a different collation has to initialise its own directory with initdb-18 rather than adjust the one Homebrew made.

Because the directory is named after the formula, versions do not collide. A machine can hold var/postgresql@17 and var/postgresql@18 at the same time, each with its own data, and installing the newer formula neither reads nor moves the older directory. The consequence catches people out on the way up: after installing the new major version and starting it, every database from the old one appears to be gone. It is not gone. It is in the other directory, and nothing has been migrated yet.

The two clusters do share one thing, which is the default port. Only one of them can be listening on 5432 at a time, so the answer to "why did the new server not start" is often that the old one is still registered as a service.

Running it, and what brew services actually writes

brew services start postgresql@18 starts the server and registers it to launch at login. brew services run postgresql@18 starts it without registering, which is the better choice on a machine where the database should not come up on every boot. Homebrew manages these through launchctl: without sudo it operates on ~/Library/LaunchAgents, started at login, and with sudo on /Library/LaunchDaemons, started at boot.

The service definition is worth reading once, because it answers where the log goes and how a crash is handled. It runs opt/postgresql@18/bin/postgres with -D pointing at the versioned data directory, sets LC_ALL to en_US.UTF-8, writes both standard output and errors to var/log/[email protected], and allows 120 seconds for a stop to complete. There is a real difference between versions here: the PostgreSQL 18 formula asks launchd to restart the service only when it crashed, while the 17 and 14 formulae ask for it to be kept alive unconditionally. On a machine where a database that was stopped on purpose keeps coming back, the version of the formula is the thing to check.

The rest of the subcommands cover the cases that come up. brew services stop --keep stops the server without unregistering it, brew services kill stops it immediately but keeps the registration, and brew services info --json reports the state in a form a script can read. Environment variables can be added or overridden per service by creating $HOMEBREW_USER_CONFIG_HOME/services/<formula>.env, which defaults to ~/.homebrew/services/<formula>.env, in KEY=value form one per line, taking effect on the next brew services restart and persisting across upgrades.

One failure has its own line in the caveats because it is common after an unclean shutdown. If the service will not start with a postmaster.pid lock file error and no postgres process is running, the stale file at $HOMEBREW_PREFIX/var/postgresql@18/postmaster.pid is what to remove.

When only the client is wanted

A Mac that talks to a database somewhere else does not need a server. The libpq formula provides the Postgres C API library along with the client programs, currently at 18.6, with libpq@17 and libpq@16 available as versioned alternatives. It is keg-only too, and its recorded reason is different and more informative: it conflicts with PostgreSQL. Installing both and linking both is what produces a psql whose version nobody can account for.

Keeping the client, the server and the versioned symlinks straight is mostly a matter of being able to see them. The Cellar, the opt links and the var directories are three ordinary folders, and reading their sizes and targets next to a shell prompt in a file manager with a built-in terminal is faster than reconstructing the layout from command output.

What a major upgrade actually costs

Minor upgrades are free. PostgreSQL's documentation states plainly that pg_upgrade is not required for minor version upgrades, for example from 12.7 to 12.8, so moving from 18.5 to 18.6 is brew upgrade postgresql@18 and a restart. The data directory is untouched.

Major upgrades are a different operation, and the reason is architectural rather than procedural. Major versions make complex changes, so the contents of the data directory cannot be maintained in a backward compatible way. A dump and reload, or pg_upgrade, is required. Intervening versions can be skipped, so 16 to 18 in one step is supported.

pg_upgrade works by creating new system tables and reusing the old user data files, which is why it is fast compared with a dump and reload. Its arguments name both installations explicitly: -b or --old-bindir and -B or --new-bindir for the executable directories, -d or --old-datadir and -D or --new-datadir for the cluster directories. On a Homebrew machine those four paths are exactly the opt/postgresql@17/bin, opt/postgresql@18/bin, var/postgresql@17 and var/postgresql@18 paths described above, which is the practical payoff of knowing the layout.

Three options matter on a first attempt. -c or --check checks the clusters only and changes no data, and the documentation notes that it also outlines any manual adjustments needed afterwards, so it is the correct first run every time. -k or --link uses hard links instead of copying files, which is dramatically faster and, in exchange, means the old cluster can no longer be restarted once linking has begun. -j sets how many processes run at once. Upgrades are supported from 9.2 and later up to the current major release.

The step most often skipped is the one after the upgrade. Unless --no-statistics is given, pg_upgrade transfers most optimizer statistics, but not all of them, and it prints instructions at the end. The documented sequence is vacuumdb --all --analyze-in-stages --missing-stats-only to generate minimal statistics quickly for relations without any, then vacuumdb --all --analyze-only so every relation has current cumulative statistics, with --jobs on both to speed things up. A database that feels slower after a successful major upgrade is usually a database whose statistics were never regenerated.

When the new cluster has been verified, pg_upgrade leaves a script that deletes the old cluster's data directories. On the Homebrew side, brew cleanup --prune-prefix clears the dead symlinks left in the prefix after the old formula is uninstalled, which the formula caveats mention specifically.

What to change first

Replace brew install postgresql with brew install postgresql@18 everywhere it is written down, including in setup notes and CI files, so the alias moving next year does not silently change the major version. Then check whether a plain psql on the machine points at the version actually being used, and prefer the version suffixed commands if more than one is installed. Reading the Cellar, the opt links and the var directories side by side while running the commands that touch them is the working shape Atriens is built for.