Install PostgreSQL on MacOS (Apple Silicon m1/m2)

Introduction

When developing applications, you often need to store and manage data in databases. This article shows how to install PostgreSQL on macOS and connect it to your applications.

We will cover installation methods, terminal usage, connecting from apps, and basic PostgreSQL commands.

Installation

You can install PostgreSQL in two ways:

• Through the installer from the official site
• Using the Homebrew package manager (recommended)

Both methods allow you to fully install PostgreSQL on your system.

Through the installer

Go to the official PostgreSQL website and download the version you need.

• Select required version (e.g., v14.2) and open the directory
• Download <strong>postgresql-[version].tar.gz</strong>
• Unzip and open the folder
• Check the INSTALL file for step-by-step instructions
• Follow instructions in terminal to install

Through Homebrew

Before installing, check and update Homebrew:

• Update: `brew update`
• Upgrade: `brew upgrade`
• Fix issues: `brew doctor`

Steps to install PostgreSQL with Homebrew:

• `brew install postgresql`
• `brew services start postgresql` (start service)
• `brew services stop postgresql` (stop service)
• `brew services list` (check service status)
• Login: `psql postgres`
• List users: `\du`
• Exit: `\q`

Extras

• Manual start: `/opt/homebrew/opt/postgresql/bin/postgres -D /opt/homebrew/var/postgres`
• Start/stop with pg_ctl: `pg_ctl -D /opt/homebrew/var/postgres start|stop`

Errors

Common errors you may encounter during setup.

Bootstrap failed: 5: Input/output error

Appears after `brew services start postgresql`.

Fix: run `launchctl remove homebrew.mxcl.postgresql` then `brew services start postgresql`.

Commands

Authorization

• Default user: `psql postgres`
• Specific user: `psql postgres -U [user_name]`
• Exit: `\q`

Databases

• List DBs: `\l`
• Create: `create database [name];`
• Drop: `drop database [name];`
• Rename: `alter database [old] rename to [new];`

Import DB:

• `psql -h [host] -d [db] -U [user] -f ~/path/to/file.sql`
• `psql [db] < ~/path/to/file.sql`

Export DB: `pg_dump -U [user] -h [host] [db] >> export_file.sql`

Users

• Create user with password: `create role [name] with login password 'pass';`
• Encrypted password: `create role [name] with login encrypted password 'pass';`
• Without password: `create role [name] with login;`
• Delete user: `drop user [name];`
• Rename user: `alter user [old] rename to [new];`
• Change password: `alter user [name] with password 'new_pass';`

Tables

Create table example:

    create table accounts (
  user_id serial PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_on TIMESTAMP NOT NULL,
  last_login TIMESTAMP
);
  

Drop table: `drop table accounts;`

Connecting to a database in projects

PostgreSQL runs on port 5432 by default. Connect using IP and port (e.g., localhost:5432).

Use ENV files to store connection details (IP, port, username, password).

• Create user: `CREATE ROLE MyNewUserName WITH LOGIN PASSWORD 'MyNewUserPassword';`
• Grant DB creation: `ALTER ROLE MyNewUserName CREATEDB;`
• Login as new user: `psql postgres -U MyNewUserName`