John Gordon | Tech Guide: PostgreSQL

Overview

PostgreSQL (often just Postgres) is a powerful, free, open-source relational database system. It grew out of the POSTGRES project at the University of California, Berkeley in the 1980s and has been developed by a worldwide community for more than 30 years. Postgres is known for closely following the SQL standard, for reliability, and for advanced features such as JSON data, full-text search, and extensions like PostGIS for maps.

The current version is PostgreSQL 18; PostgreSQL 19 is in its final testing and is expected shortly. In this guide you will install PostgreSQL and its graphical tool pgAdmin on your computer, then create and query a database with both the psql command line and pgAdmin.

Main idea: Install the PostgreSQL server, remember the password you set for the postgres user, then connect with psql or pgAdmin to localhost on port 5432.

Is There a "Developer Edition"?

No. Unlike SQL Server, PostgreSQL has only one edition, and it is completely free for any use: learning, development, and production. The PostgreSQL license lets you use, modify, and distribute it at no cost. A "developer setup" simply means installing PostgreSQL on your own computer, which is what this guide does.

Option	What it is	Cost
PostgreSQL (community)	The official open-source database. Covered in this guide.	Free
EDB installer	The recommended Windows and Mac installer, built by the company EDB. It installs the same free community PostgreSQL plus pgAdmin.	Free
Commercial distributions (EDB Postgres Advanced Server and others)	Company-supported versions with extra features and paid support.	Paid
Cloud services (Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL, Supabase, Neon)	PostgreSQL hosted for you. Many offer free tiers.	Free tier to paid

Requirements

A 64-bit Windows, macOS, or Linux computer.
Administrator privileges to install software.
About 1 GB of free disk space.
Internet access.

Important Notes

During installation you set a password for the postgres superuser. Write it down. You need it every time you connect.
PostgreSQL runs in the background as a service and starts automatically with your computer.
The default port is 5432. If another program already uses it, the installer suggests a different port; note it if so.
Your class may require a specific major version. If so, install that version instead of the newest one.

PostgreSQL Installation Instructions

Choose the tab for your operating system:

Installing PostgreSQL

Click the tab above for your operating system. Allow about 15–20 minutes.

The guide covers these major tasks:

Download the installer.
Choose components (server, pgAdmin, command-line tools).
Set the postgres password.
Keep the default port, 5432.
Finish the installation.
Connect and confirm the version.

Install PostgreSQL on Windows

Go to postgresql.org/download/windows and click Download the installer. This opens EDB's download page.
Download the Windows x86-64 installer for PostgreSQL 18 (or the version your class requires).
Double-click the downloaded .exe file and click Yes if Windows asks for permission.
Work through the installer screens:
- Installation Directory: keep the default and click Next.
- Select Components: keep all four checked: PostgreSQL Server, pgAdmin 4, Stack Builder, and Command Line Tools.
- Data Directory: keep the default.
- Password: enter and confirm a strong password for the postgres superuser. Write it down.
- Port: keep 5432.
- Advanced Options: keep the default locale.
Review the summary, then click Next to install. This takes a few minutes.
On the final screen, uncheck Launch Stack Builder at exit (you don't need extra tools for this guide) and click Finish.

The Start menu now has a PostgreSQL 18 folder containing pgAdmin 4 and SQL Shell (psql).

Install PostgreSQL on macOS

There are two good options. Choose one.

Option A: EDB installer (includes pgAdmin)

Go to postgresql.org/download/macosx and click Download the installer.
Download the macOS installer for PostgreSQL 18. It works on both Apple silicon and Intel Macs.
Open the downloaded .dmg and double-click the installer inside. Enter your Mac password when asked.
Follow the same screens as on Windows: keep the default components (Server, pgAdmin 4, Stack Builder, Command Line Tools), set and write down the postgres password, and keep port 5432.
On the final screen, uncheck Launch Stack Builder at exit and click Finish.

Open pgAdmin 4 and SQL Shell (psql) from the PostgreSQL 18 folder in Applications.

Option B: Postgres.app (simplest)

Download Postgres.app from postgresapp.com and drag it to Applications.
Open Postgres.app and click Initialize to create and start a server.
Follow the instructions on postgresapp.com to add its command-line tools to your PATH.

Postgres.app connects with your Mac username and no password by default. It doesn't include pgAdmin; install pgAdmin separately from pgadmin.org if you want it.

Install PostgreSQL on Linux

Use your distribution's package manager. It may install a slightly older major version; the PostgreSQL Linux downloads page shows how to add the official repository for the newest one.

Ubuntu and Debian

sudo apt update
sudo apt install postgresql
sudo systemctl status postgresql     # should show "active"

Fedora

sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

Set the postgres password

On Linux, the postgres database user has no password at first; you connect as the matching Linux user instead. Set a password so tools like pgAdmin can connect:

sudo -u postgres psql
ALTER USER postgres PASSWORD 'choose-a-strong-password';
\q

pgAdmin for Linux is available from pgadmin.org.

Confirm the installation

Open SQL Shell (psql) from the PostgreSQL folder (Windows and Mac EDB installer), or run sudo -u postgres psql on Linux.
SQL Shell asks for Server, Database, Port, and Username. Press Enter at each one to accept the defaults shown in brackets (localhost, postgres, 5432, postgres).
Enter your postgres password. Nothing appears as you type; this is normal.
At the postgres=# prompt, enter:
```
SELECT version();
```

Check yourself: psql prints a line beginning PostgreSQL 18 (or the version you installed).

If You Can't Connect

password authentication failed for user "postgres": the password is wrong. It is the one you set in the installer, not your computer's password.
Connection refused: the PostgreSQL service isn't running. On Windows, start postgresql-x64-18 in the Services app; on Linux, run sudo systemctl start postgresql.

Beginner Example: A Small Store Database

You will create a database in psql, add two related tables, query them, and then view the same data in pgAdmin.

Step 1: Create a database and connect to it

At the postgres=# prompt in psql:

CREATE DATABASE store;
\c store

Commands that start with a backslash, such as \c (connect), are psql meta-commands, not SQL, and don't need a semicolon. The prompt changes to store=#.

Step 2: Create tables

CREATE TABLE customers (
    customer_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(100) UNIQUE,
    city        VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(customer_id),
    order_date  DATE NOT NULL DEFAULT CURRENT_DATE,
    total       NUMERIC(10,2) NOT NULL CHECK (total >= 0)
);

GENERATED ALWAYS AS IDENTITY numbers new rows automatically. REFERENCES creates a foreign key, and CHECK rejects negative totals.

Step 3: Insert data

INSERT INTO customers (name, email, city) VALUES
    ('Ana Lopez',    'ana@example.com',   'Salt Lake City'),
    ('Ben Carter',   'ben@example.com',   'Ogden'),
    ('Chloe Nguyen', 'chloe@example.com', 'Salt Lake City');

INSERT INTO orders (customer_id, order_date, total) VALUES
    (1, '2026-09-02', 120.50),
    (1, '2026-10-11',  45.00),
    (2, '2026-10-03', 310.25),
    (3, '2026-10-20',  89.99);

Step 4: Query the data

-- Customers in Salt Lake City
SELECT name, email FROM customers WHERE city = 'Salt Lake City';

-- Orders with customer names
SELECT c.name, o.order_date, o.total
FROM orders AS o
JOIN customers AS c ON c.customer_id = o.customer_id
ORDER BY o.order_date;

-- Total spent per customer
SELECT c.name, COUNT(o.order_id) AS orders, SUM(o.total) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
GROUP BY c.name
ORDER BY total_spent DESC;

The last query returns:

     name     | orders | total_spent
--------------+--------+-------------
 Ben Carter   |      1 |      310.25
 Ana Lopez    |      2 |      165.50
 Chloe Nguyen |      1 |       89.99
(3 rows)

Step 5: Explore with psql meta-commands

Try each of these. \dt lists the tables, \d orders describes the orders table, \l lists all databases, \? shows all meta-commands, and \q quits psql:

\dt
\d orders
\l
\q

Step 6: View the same data in pgAdmin

Open pgAdmin 4. The first time, it may ask you to set a master password, which protects saved passwords inside pgAdmin. Choose one and remember it.
In the Object Explorer on the left, expand Servers → PostgreSQL 18 and enter your postgres password. Check Save Password if you like.
Expand Databases → store → Schemas → public → Tables. You see customers and orders.
Right-click customers and choose View/Edit Data → All Rows to see the data in a grid.
Select the store database, then choose Tools → Query Tool. Type a query, such as SELECT * FROM orders;, and press F5 (or click the play button) to run it.

Postgres.app users: in pgAdmin, register a server with Host localhost, Port 5432, and your Mac username.

Check yourself: In pgAdmin's Query Tool, SELECT COUNT(*) FROM orders; returns 4, and SELECT SUM(total) FROM orders; returns 565.74.

Common Errors

relation "customers" does not exist: you are connected to the wrong database. In psql, run \c store; in pgAdmin, open the Query Tool from the store database.
insert or update on table "orders" violates foreign key constraint: the customer_id doesn't exist in customers.
The prompt shows store-# instead of store=#: psql is waiting for the rest of a statement. Type ; and press Enter.

Next Steps

Compare PostgreSQL with MySQL, SQL Server, and SQLite.
Connect to PostgreSQL from Python (the psycopg package) or PHP (PDO).
Read the official PostgreSQL tutorial.

Final Notes:

PostgreSQL has a single edition that is free for development and production.
On Windows and Mac, the EDB installer sets up the server, pgAdmin, and psql together.
Remember the postgres password; you need it to connect to localhost:5432.
Use psql meta-commands (such as \dt and \c) to explore, and SQL for everything else.

Back to Tech Guides Top of Page

Install PostgreSQL and pgAdmin

Disclaimer & Important Cautions

Overview

Is There a "Developer Edition"?

Requirements

Important Notes

PostgreSQL Installation Instructions

Installing PostgreSQL

Install PostgreSQL on Windows

Install PostgreSQL on macOS

Option A: EDB installer (includes pgAdmin)

Option B: Postgres.app (simplest)

Install PostgreSQL on Linux

Ubuntu and Debian

Fedora

Set the postgres password

Confirm the installation

If You Can't Connect

Beginner Example: A Small Store Database

Step 1: Create a database and connect to it

Step 2: Create tables

Step 3: Insert data

Step 4: Query the data

Step 5: Explore with psql meta-commands

Step 6: View the same data in pgAdmin

Common Errors

Next Steps