How to Create a PostgreSQL Database on Mac

This post was written by an engineer at QueryPlane. QueryPlane is an app builder for your database: bring your own postgres db and you can create interactive applications to share with other developers, coworkers or even your customers. If you’re interested in trying it out, get started here.

---

Setting up a PostgreSQL database on macOS is straightforward with Homebrew. This guide covers installing PostgreSQL, creating your first database, and connecting to it — all from the terminal.

In this post, we’ll cover:

• Installing PostgreSQL - Using Homebrew
• Starting the server - Running PostgreSQL as a background service
• Creating a database - Using createdb and psql
• Connecting - Verifying your setup
• Common issues - Troubleshooting Mac-specific problems

Installing PostgreSQL with Homebrew

Homebrew is the standard package manager for macOS. If you don’t have it installed:

/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

Then install PostgreSQL:

brew install postgresql@17

This installs PostgreSQL 17 (the latest stable release) along with all client tools including psql, createdb, pg_dump, and others.

Homebrew places the binaries in a versioned directory. To make them available on your PATH, follow the instructions Homebrew prints after installation, which typically look like:

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

Verify the installation:

psql --version
# psql (PostgreSQL) 17.x

Starting the PostgreSQL server

Homebrew can manage PostgreSQL as a background service:

brew services start postgresql@17

This starts PostgreSQL immediately and configures it to launch automatically on login. You can check the status with:

brew services list

To stop the server:

brew services stop postgresql@17

If you prefer to run PostgreSQL manually without auto-start:

pg_ctl -D /opt/homebrew/var/postgresql@17 start

On a fresh Homebrew installation, PostgreSQL creates a default superuser role matching your macOS username and a database with the same name. This means you can connect immediately without any additional configuration.

Creating a database

There are two ways to create a database: the createdb command-line tool and the SQL CREATE DATABASE statement.

Using createdb

The simplest approach:

createdb myapp

This creates a database named myapp owned by your current macOS user. You can specify additional options:

createdb --owner=myuser --encoding=UTF8 myapp

Using SQL in psql

Connect to PostgreSQL first, then run the SQL command:

psql postgres

CREATE DATABASE myapp;

The psql postgres command connects to the default postgres database, which always exists and serves as a convenient entry point for administrative tasks.

You can also set the owner and encoding in SQL:

CREATE DATABASE myapp
  OWNER myuser
  ENCODING 'UTF8'
  LC_COLLATE 'en_US.UTF-8'
  LC_CTYPE 'en_US.UTF-8';

Listing databases

To see all databases on your server:

psql -l

Or from within psql:

\l

Connecting to your database

Connect to the new database with psql:

psql myapp

You’re now in an interactive SQL session. Try a quick test:

CREATE TABLE test (id serial PRIMARY KEY, name text);
INSERT INTO test (name) VALUES ('hello');
SELECT * FROM test;

To quit psql:

\q

Connecting from an application

Most application frameworks use a connection string (also called a database URL). For a local PostgreSQL database, the connection string is:

postgresql://localhost:5432/myapp

Since the default Homebrew installation uses peer authentication (your macOS username, no password), this works without credentials. For applications that require explicit credentials, you can create a role with a password:

CREATE ROLE myuser WITH LOGIN PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

Then the connection string becomes:

postgresql://myuser:mypassword@localhost:5432/myapp

Creating additional roles

PostgreSQL uses roles for authentication and authorization. To create a new role:

psql postgres

-- Create a role with login capability
CREATE ROLE developer WITH LOGIN PASSWORD 'devpass';

-- Grant connect privilege on the database
GRANT ALL PRIVILEGES ON DATABASE myapp TO developer;

To create a superuser role (full administrative access):

CREATE ROLE admin WITH LOGIN PASSWORD 'adminpass' SUPERUSER;

Common issues on macOS

”command not found: psql”

The PostgreSQL binaries aren’t on your PATH. Add the Homebrew path:

echo 'export PATH="/opt/homebrew/opt/postgresql@17/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

On Intel Macs, replace /opt/homebrew with /usr/local.

”could not connect to server: No such file or directory”

The PostgreSQL server isn’t running. Start it:

brew services start postgresql@17

If that doesn’t work, check for a stale PID file:

rm -f /opt/homebrew/var/postgresql@17/postmaster.pid
brew services restart postgresql@17

“database does not exist”

When you run psql without arguments, it tries to connect to a database matching your macOS username. If that database doesn’t exist:

createdb $(whoami)

Port conflicts

If another PostgreSQL installation is using port 5432, you’ll get connection errors. Check what’s using the port:

lsof -i :5432

You can run PostgreSQL on a different port by editing the config:

# Find the config file
psql -c "SHOW config_file;"

# Edit postgresql.conf and change the port
# port = 5433

Alternative installation methods

Homebrew is the most common approach, but there are alternatives:

Postgres.app is a native macOS application that bundles PostgreSQL with a menu bar interface. You download the app, drag it to Applications, and click “Initialize” to start a server. It’s the easiest option if you prefer avoiding the command line for installation and server management.

The official PostgreSQL installer from EnterpriseDB provides a traditional graphical installer with a setup wizard. It includes pgAdmin 4 and Stack Builder for installing additional tools.

Both alternatives install the same PostgreSQL server and client tools. The difference is in how you manage the server lifecycle (start, stop, configure).

Wrapping up

The minimal steps to get a PostgreSQL database running on Mac: install with brew install postgresql@17, start with brew services start postgresql@17, create a database with createdb myapp, and connect with psql myapp. From there, you can create tables, load data, and connect your application.

If you need a GUI for managing your database, see our Top PostgreSQL GUI Clients post for options like pgAdmin, DBeaver, and TablePlus.