Install PostgreSQL

With SQLite, your program opens a file and that's the whole database. PostgreSQL splits the job in two:

• The server: a background process, called postgres, that owns the data directory. Nothing else is allowed to touch those files. It listens for connections on port 5432.
• A client: any program that connects to the server and sends it SQL. The one that ships with PostgreSQL is psql, a command-line shell. Your Python, Go, or Node program is a client too, through a driver library.

Because many clients can connect at once, the server has to know who each one is. It keeps its own list of roles, which are PostgreSQL's user accounts, and they are separate from the accounts on your computer. A fresh install has exactly one role, a superuser. On Linux it's called postgres; on macOS with Homebrew it's named after you; on Windows it's postgres with a password you choose during the install. Step 06 is about getting from that one role to a role of your own.

A server that owns the data, clients that ask it questions, and a list of roles that decides who gets in.

These commands are for Ubuntu and Debian, including WSL and a Raspberry Pi. Your distribution already has a postgresql package, but it's pinned to whatever version was current at release time: Ubuntu 24.04 ships PostgreSQL 16. The PostgreSQL project runs its own apt repository, called PGDG, with every supported version. A helper script sets it up:

# 1. install the helper package that ships the setup script
sudo apt install -y postgresql-common

# 2. add the repository (press Enter when it asks)
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

# 3. install the server and the psql client
sudo apt update
sudo apt install -y postgresql-18

The Debian packaging does more than copy files. It creates a Linux user called postgres, creates a database cluster in /var/lib/postgresql/18/main, and starts the server. A cluster is one data directory run by one server process. Check it's up:

pg_lsclusters

online on port 5432 means the server is running. It also starts on every boot. Jump to Step 05.

With Homebrew installed, ask for the version by number. brew install postgresql without one gives you an older default.

brew install postgresql@18
brew services start postgresql@18

The install creates a cluster in /opt/homebrew/var/postgresql@18, and brew services starts the server now and at every login. There's one extra step. Versioned formulas are keg-only, which means Homebrew doesn't put psql on your PATH, so that two versions can sit side by side. Add it yourself:

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

echo 'export PATH="/usr/local/opt/postgresql@18/bin:$PATH"' >> ~/.zshrc

Run the one for your Mac, then open a new terminal. Homebrew made your macOS username the superuser role, not postgres, so you'll skip most of Step 06.

The Windows build comes from EDB's graphical installer. You can download it from postgresql.org/download/windows, or have winget fetch the same installer:

winget install PostgreSQL.PostgreSQL.18

Either way, the installer asks a few questions. Accept the defaults except for one:

• Password for the postgres superuser. Pick one and write it down. There's no email reset: this password is the only way into the server until you create another role.
• Port: leave it at 5432.
• Stack Builder: untick it at the end. It installs optional extras you don't need yet.

The server is installed as a Windows service and starts with Windows. The installer does not put psql on your PATH. Add C:\Program Files\PostgreSQL\18\bin through Settings → System → About → Advanced system settings → Environment Variables, or run this once in PowerShell:

$p = [Environment]::GetEnvironmentVariable("Path", "User")
[Environment]::SetEnvironmentVariable("Path", "$p;C:\Program Files\PostgreSQL\18\bin", "User")

Then close PowerShell and open a new window. Windows only gives the new PATH to programs started after the change.

First check that the client is installed and on your PATH:

psql --version

On Ubuntu, the version line ends with the package name in brackets, for example (Ubuntu 18.6-1.pgdg24.04+2). Any 18.x means the client is in place. Now try to connect:

psql

On Linux this fails, and the error is the most useful thing on this page:

Read it carefully, because it's good news. connection to server worked: the server is running and answered. It then refused to let you in, because psql gave your login name as the role name, and no role called you exists yet. On macOS the same command instead says database "you" does not exist: the role exists, but there's no database with that name yet. On Windows, psql asks for a password for your Windows username and then fails with password authentication failed, for the same reason as on Linux: no role by that name. All three are fixed in the next step.

If it says connection refused or No such file or directory

That's a different problem: nothing is listening, so the server isn't running. Start it with sudo systemctl start postgresql on Linux or brew services start postgresql@18 on macOS. On Windows, open Services, find postgresql-x64-18, and click Start.

You could do everything as the superuser, but that's like using root for everyday work. Instead, make a normal role that can create databases, and give it a database with the same name as the role. psql connects to a database named after your role unless you say otherwise, so after this step a bare psql will just work.

Linux

Only the postgres Linux user can log in as the postgres role, so run the admin commands as that user with sudo -u postgres:

# a role with your login name that may create databases
sudo -u postgres createuser --createdb $USER

# a database with the same name, owned by you
sudo -u postgres createdb --owner=$USER $USER

macOS

Homebrew already made your role, and it's a superuser, so just create the database:

createdb

With no arguments, createdb creates a database named after you.

Windows

Log in as postgres with the password from the installer, and create the role and database in SQL. Swap you for your own name:

psql -U postgres

CREATE ROLE you WITH LOGIN CREATEDB PASSWORD 'pick-a-password';
CREATE DATABASE you OWNER you;
\q

From then on, connect with psql -U you, or set the PGUSER environment variable to you so a bare psql works.

Everyone: connect

psql

The prompt is the database name followed by =>. It shows =# instead when you're a superuser, which is what you'll see on macOS. It's a useful reminder of how much damage a typo could do. Confirm who and where you are:

SELECT current_user, current_database();

At the you=> prompt, create a table. Everything up to the semicolon is one statement, so you can paste the whole block. psql changes the prompt to you(> while a statement is still open.

CREATE TABLE people (
    id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL,
    city    text,
    age     integer CHECK (age >= 0),
    joined  date NOT NULL DEFAULT current_date
);

Each column has a type, and some have rules the server will enforce for you:

• GENERATED ALWAYS AS IDENTITY: the server numbers the rows 1, 2, 3… itself. This is the modern replacement for the older serial type.
• NOT NULL: a row without a name is refused.
• CHECK (age >= 0): a negative age is refused. SQLite would accept 'twelve' in an integer column; PostgreSQL won't.
• DEFAULT current_date: leave joined out and you get today's date.

Add three rows. Strings take single quotes; double quotes are for names of tables and columns.

INSERT INTO people (name, city, age) VALUES
    ('Ada',   'London',   36),
    ('Linus', 'Helsinki', 21),
    ('Grace', 'New York', 45);

The 3 is the number of rows inserted. The 0 is a leftover from older versions that you can ignore. Now read them back:

SELECT id, name, city, age FROM people ORDER BY id;

Filter and sort with WHERE and ORDER BY:

SELECT name, age FROM people WHERE age > 30 ORDER BY age DESC;

Update one row and delete another. Always write the WHERE clause first: an UPDATE or DELETE without one changes every row in the table.

UPDATE people SET city = 'Portland' WHERE name = 'Linus';
DELETE FROM people WHERE name = 'Grace';

Now try to break one of the rules from Step 07:

INSERT INTO people (name, age) VALUES ('Ken', -5);

The server refused the row, so the bad data never reached the table. Look at the DETAIL line: the row would have been id 4. Insert a valid row and ask for its id back with RETURNING:

INSERT INTO people (name, city, age) VALUES ('Ken', 'Berkeley', 83) RETURNING id, name;

It's 5, not 4. The failed insert used up number 4, and identity numbers are never handed out twice, even when the row they were meant for is rejected. That's by design: it means the server never has to lock the counter while it waits to see whether each insert succeeds. Expect gaps in ids, and never rely on them being consecutive.

Commands starting with a backslash are for psql itself, not SQL, so they don't need a semicolon. These are the ones you'll use every day:

\dt

Backslash commands talk to psql. Everything that ends in a semicolon goes to the server.

You have a server, a role, a database, and a table with rules the server enforces. Minor releases, such as 18.6 to 18.7, are security and bug fixes: take them through apt upgrade, brew upgrade, or winget, with no other steps. Moving to a new major version such as 19 changes the on-disk format and needs pg_upgrade, so read the release notes first.