Setting Up PostgreSQL on macOS: A Fresh Start Guide

If you've ever faced the frustration of setting up a database from scratch, you're not alone. I remember the first time I installed PostgreSQL on my Mac, a mix of excitement and confusion. "Where do I even start? How do I create users? And how do I connect my app to it?"

If that sounds familiar, don't worry. In this guide, I'll walk you through how to start fresh with PostgreSQL on macOS using Homebrew, including how to launch the service, create a new database and user, and connect everything to your app — step by step, in a way that just makes sense.

Note: This guide focuses on macOS, but many of the PostgreSQL commands will work similarly on Linux. Windows users might want to look into PostgreSQL's official installer or WSL.

Why PostgreSQL?

PostgreSQL is a powerful, open-source relational database used by startups and giants alike. It's reliable, scalable, and has features that can grow with your project, making it a great choice whether you're just starting out or levelling up your skills. Plus, it's completely free and has excellent community support.

Step 1: Make Sure PostgreSQL Is Installed

Since you're on macOS, Homebrew is your friend for installing and managing software like PostgreSQL. If you don't have Homebrew installed yet, head to brew.sh and follow the installation instructions.

Open your Terminal and check if PostgreSQL is installed and what version you have:

psql --version

If it shows something like psql (PostgreSQL) 17.x, you're good to go!

If it says "command not found," you need to install PostgreSQL:

brew install postgresql@17

After installation, you might need to add PostgreSQL to your PATH. Homebrew will usually show you the command to run, but it typically looks like this:

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

Step 2: Start the PostgreSQL Service

Now that PostgreSQL is installed, it needs to be running so your app can talk to it. Think of this like starting your car before you can drive somewhere.

Use Homebrew to start PostgreSQL:

brew services start postgresql@17

This command tells macOS to launch PostgreSQL in the background and keep it running, even after you restart your computer.

To check that it's running:

brew services list | grep postgresql

You should see something like postgresql@17 started.

If you ever need to manage the service later, these commands will help:

# Restart PostgreSQL
brew services restart postgresql@17

# Stop PostgreSQL 
brew services stop postgresql@17

# Check status
brew services info postgresql@17

Step 3: Access PostgreSQL for the First Time

PostgreSQL creates a default superuser that matches your macOS username when it's first installed. Let's connect to the default postgres database:

psql postgres

If successful, you'll see a prompt like this:

postgres=#

Congrats — you're inside the PostgreSQL command line interface! The # symbol indicates you're connected as a superuser.

Troubleshooting: If you get a connection error, make sure PostgreSQL is running (Step 2) and try specifying your username explicitly:

psql -U $(whoami) postgres

Step 4: Create a New User and Database

For your projects, it's a security best practice to create a dedicated user and database rather than using the default superuser for everything. This limits potential damage if something goes wrong.

At the postgres=# prompt, run the following commands. Feel free to replace myapp_user and myapp_db with your own names, and choose a secure password:

-- Create a new user with a password
CREATE USER myapp_user WITH PASSWORD 'your_secure_password_here';

-- Create a new database owned by this user
CREATE DATABASE myapp_db OWNER myapp_user;

-- Grant all privileges on the database to the new user
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

-- Grant the user permission to create databases (useful for testing)
ALTER USER myapp_user CREATEDB;

Security tip: Use a strong password with a mix of letters, numbers, and symbols. You can generate one using:

openssl rand -base64 32

Once done, exit the PostgreSQL prompt:

\q

Step 5: Test Your New User Connection

Let's make sure everything works by connecting to your new database as your new user:

psql -U myapp_user -d myapp_db -h localhost

You'll be prompted for the password you set. If you see a prompt like myapp_db=>, you're successfully connected! The > symbol (instead of #) shows you're connected as a regular user, not a superuser.

Try a simple command to make sure everything works:

SELECT version();

This should show your PostgreSQL version information.

Exit when you're done:

\q

Step 6: Set Up Your Application Configuration

Your app needs connection details to talk to PostgreSQL. The exact setup depends on your framework, but here are the most common patterns:

Environment Variables (.env file)

Create or update your .env file:

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_USER=myapp_user
DB_PASSWORD=your_secure_password_here
DB_NAME=myapp_db

# Optional: Full connection string format
DATABASE_URL=postgresql://myapp_user:your_secure_password_here@localhost:5432/myapp_db

TypeORM Configuration

If you're using TypeORM with Node.js, your datasource.ts might look like this:

import { DataSource } from "typeorm";
import { config } from "dotenv";

config(); // Load environment variables

export const AppDataSource = new DataSource({
  type: "postgres",
  host: process.env.DB_HOST || "localhost",
  port: parseInt(process.env.DB_PORT || "5432"),
  username: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  synchronize: process.env.NODE_ENV === "development", // Only in development!
  logging: process.env.NODE_ENV === "development",
  entities: ["src/entities/**/*.ts"],
  migrations: ["src/migrations/**/*.ts"],
});

Django Configuration

For Django users, update your settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': os.environ.get('DB_NAME'),
        'USER': os.environ.get('DB_USER'),
        'PASSWORD': os.environ.get('DB_PASSWORD'),
        'HOST': os.environ.get('DB_HOST', 'localhost'),
        'PORT': os.environ.get('DB_PORT', '5432'),
    }
}

Step 7: Essential PostgreSQL Commands to Remember

Now that you're set up, here are some handy commands you'll use regularly:

# Connect to your database
psql -U myapp_user -d myapp_db -h localhost

# Connect using a connection string
psql "postgresql://myapp_user:password@localhost:5432/myapp_db"

Once connected, these SQL commands are useful:

-- List all databases
\l

-- List all tables in current database
\dt

-- Describe a table structure
\d table_name

-- List all users
\du

-- Switch to a different database
\c database_name

-- Show current connection info
\conninfo

-- Get help
\h

-- Quit
\q

Troubleshooting Common Issues

"psql: command not found": PostgreSQL isn't in your PATH. Try the PATH export command from Step 1.

"connection refused": PostgreSQL service isn't running. Check with brew services list | grep postgresql.

"password authentication failed": Double-check your username and password. Remember, PostgreSQL is case-sensitive.

"database does not exist": Make sure you created the database in Step 4, or check the database name for typos.

Next Steps

With PostgreSQL running, you're ready to start building! Consider learning about:

• Database migrations: How to version-control your database schema changes
• Connection pooling: For better performance in production apps
• Backup strategies: Using pg_dump to backup your data
• Performance monitoring: Understanding EXPLAIN and query optimization
• Extensions: PostgreSQL has powerful extensions like PostGIS for geographic data

Wrapping Up

Starting fresh with PostgreSQL might feel daunting at first, but once you've set up your database and user correctly, you unlock a reliable backend foundation for any app you build.

You've learned how to:

• Verify and install PostgreSQL 17 with Homebrew
• Start and manage the PostgreSQL service
• Create a new database user and database with proper permissions
• Connect to your database as that user
• Configure your app to connect to PostgreSQL
• Use essential PostgreSQL commands for da

The best part? This setup will serve you well whether you're building a simple prototype or preparing for production. PostgreSQL scales with your ambitions.

Happy coding, and welcome to the world of robust database management!