Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 90 ยท Getting Started with PostgreSQL

Installing PostgreSQL and Connecting

Install PostgreSQL from packages or Docker, understand initdb and services, learn pg_hba.conf basics, and connect with URIs and libpq environment variables.

A PostgreSQL server is just a program that listens on a network port, so getting started means three things: installing the program, creating a cluster (the folder of data files the server manages), and connecting to it with a client. This lesson walks through all three, then explains the settings that decide who is allowed in.

Everything about installing and operating a server happens outside your browser, so the commands here are read-only blocks that you run in your own terminal. The live examples are small Python programs that run in the page and mimic two pieces of the connection process: reading a connection string, and the first-match rule of pg_hba.conf. They run in your browser, with no server involved.

Ways to get a server

Pick whichever fits your situation. For learning, Docker or a local installer is the easiest.

Five ways to run PostgreSQL

Docker One command, nothing left on your machine.
OS packages apt, dnf or brew install with a service ready.
Installers Graphical installers for Windows and macOS.
Managed service The cloud runs backups and upgrades for you.

The same SQL and the same psql work with all of them.

1 / 2

Docker is the cleanest way to experiment:

Terminal
docker run --name pg -e POSTGRES_PASSWORD=change-me -p 5432:5432 \
  -v pgdata:/var/lib/postgresql -d postgres:18

docker exec -it pg psql -U postgres

POSTGRES_PASSWORD is required by the official image. The -v option keeps the data in a named volume so it survives removing the container. Images for version 18 and later keep data in a versioned folder under /var/lib/postgresql, which is why the volume is mounted there; older images used /var/lib/postgresql/data. Check the image's documentation page for the version you use.

Packages install the server, create a first cluster and start a service:

Terminal
# Debian / Ubuntu (the PostgreSQL project also runs its own apt repository for newer versions)
sudo apt install postgresql

# Fedora / RHEL family
sudo dnf install postgresql-server
sudo postgresql-setup --initdb
sudo systemctl enable --now postgresql

# macOS with Homebrew
brew install postgresql@18
brew services start postgresql@18

On Windows and macOS you can also use a graphical installer (the EnterpriseDB installer, or Postgres.app on macOS). Managed services from the big clouds hand you a host name, a user and a password, and take care of the rest.

Installing on macOS and Linux, step by step

The commands above are the short version. Here is what to do on each platform, including how to make sure the server is running and how to open your first session. Names and versions change over time, so treat the version number (18 below) as a placeholder for the release you want.

Debian and Ubuntu

Terminal
sudo apt update
sudo apt install postgresql                 # the version packaged by your release

# See which clusters exist and whether they are running
pg_lsclusters
# Ver Cluster Port Status Owner    Data directory
# 18  main    5432 online postgres /var/lib/postgresql/18/main

sudo systemctl status postgresql            # the service wrapper
sudo -u postgres psql                       # first connection, as the postgres OS user

The package creates a cluster called main and starts it. A newer version than your release offers comes from the PostgreSQL project's own apt repository: install postgresql-common and run its apt.postgresql.org.sh script (check the download page for the exact current instructions). Debian's tooling can run several clusters side by side: pg_createcluster, pg_ctlcluster and pg_dropcluster manage them, and each cluster gets its own port.

Fedora, RHEL and derivatives

Terminal
sudo dnf install postgresql-server
sudo postgresql-setup --initdb              # creates the cluster; packages do not do it for you
sudo systemctl enable --now postgresql      # start now and at every boot
sudo -u postgres psql

Packages from the PostgreSQL project's own yum repository are named by version (postgresql18-server), with matching service names (postgresql-18) and an initdb helper under /usr/pgsql-18/bin/. On these systems SELinux is usually enforcing, and moving the data directory to a custom path needs a matching SELinux context.

Arch and other distributions

Terminal
sudo pacman -S postgresql
sudo -iu postgres initdb -D /var/lib/postgres/data --locale=C.UTF-8 --encoding=UTF8
sudo systemctl enable --now postgresql

If your distribution does not do the initdb step for you, the package's post-install message says so. This is the same initdb described in the next section.

macOS

Homebrew:

Terminal
brew install postgresql@18
brew services start postgresql@18           # launchd keeps it running across restarts

# Homebrew's versioned formulas are "keg-only": add the programs to your PATH
echo 'export PATH="/opt/homebrew/opt/postgresql@18/bin:$PATH"' >> ~/.zshrc
# (on an Intel Mac the prefix is /usr/local/opt instead of /opt/homebrew/opt)

psql postgres                               # connects as your macOS user

Homebrew creates the cluster with your macOS user name as the superuser, not postgres, so psql postgres connects to the postgres database as you. brew services list shows what is running, and brew services stop postgresql@18 stops it.

Postgres.app is a self-contained application: drag it into Applications, open it, press Initialize, and use its menu to open a psql session. It also offers a command-line setup so the tools are available in a terminal. It is excellent when you want several versions side by side without package managers.

Check that it works

Whatever the platform, these three checks confirm each layer:

Terminal
psql --version                   # the client program is on your PATH
pg_isready                       # the server accepts connections on this machine
psql -c "SELECT version();" postgres

If pg_isready says "no response", the server is not running: start the service. If psql says role "yourname" does not exist, the cluster has no role with your OS user name (typical on Debian, Ubuntu and Fedora): connect as postgres with sudo -u postgres psql, then create a role for yourself:

SQL
-- PostgreSQL
CREATE ROLE yourname LOGIN SUPERUSER;       -- fine for a personal laptop
CREATE DATABASE yourname OWNER yourname;    -- so a bare "psql" has a database to open

Do not give SUPERUSER to application roles; see Roles and Privileges. Windows users follow the installer and Docker routes described earlier, and use the same psql commands afterwards.

What initdb does

A cluster is created by the initdb command. Package installers usually run it for you, but running it yourself shows what a cluster is:

Terminal
initdb -D /srv/pgdata \
  --encoding=UTF8 \
  --locale-provider=icu --icu-locale=en-US \
  --auth-local=peer --auth-host=scram-sha-256

pg_ctl -D /srv/pgdata -l /srv/pgdata/server.log start

-D names the data directory. initdb fills it with the system catalogs, the two template databases (template0 and template1), a database called postgres, and two key configuration files:

  • postgresql.conf holds server settings such as the port, memory and logging.
  • pg_hba.conf decides who may connect, from where and how they prove who they are.

Debian and Ubuntu keep the configuration files in /etc/postgresql/<version>/main/ and the data in /var/lib/postgresql/<version>/main/; other systems keep both in the data directory. Ask the server where its files are:

SQL
-- PostgreSQL
SHOW data_directory;
SHOW config_file;
SHOW hba_file;

Newer versions turn data checksums on by default in initdb, which lets the server detect silent disk corruption. By default the server listens only on localhost (listen_addresses) and on port 5432 (port), so a fresh server is not reachable from other machines until you change that and open the firewall. Managing the service uses the normal tools of your system, for example sudo systemctl restart postgresql, or pg_ctl when you manage the cluster by hand.

Who may connect: pg_hba.conf

HBA means host-based authentication. The file is a list of rules, one per line, and PostgreSQL uses the first rule that matches the connection:

TEXT
# TYPE  DATABASE  USER      ADDRESS         METHOD
local   all       all                       peer
host    all       all       127.0.0.1/32    scram-sha-256
host    shop      app_user  10.0.0.0/24     scram-sha-256
host    all       all       0.0.0.0/0       reject

Reading the columns from the left: the connection type (local is a Unix socket, host is TCP), which database and user the rule applies to (or all), the client address range, and the authentication method. peer trusts the operating-system user name on local connections, scram-sha-256 asks for a password checked with a modern challenge protocol, and reject refuses. If no rule matches, the connection is refused with a message that says there is no pg_hba.conf entry.

Because the first match wins, order matters: put specific rules above general ones. After editing the file, tell the server to re-read it without a restart:

SQL
-- PostgreSQL
SELECT pg_reload_conf();

This small program implements the same first-match logic, so you can see how rule order decides a connection's fate:

Python pg_hba.conf: the first matching rule wins

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line is instructive: app_user may reach shop from the 10.0.0.x network, but the same user asking for the reports database from the same address falls through to the catch-all reject rule. A real server does a few more checks, but the principle is identical. The authentication lesson goes deeper.

Creating a first role and database

On a fresh package install the only login is the superuser role postgres, and peer authentication means you reach it by being the matching operating-system user:

Terminal
sudo -u postgres psql

From there, create a login role and a database for your application instead of using the superuser for everything:

SQL
-- PostgreSQL
CREATE ROLE app_user LOGIN PASSWORD 'change-me';
CREATE DATABASE shop OWNER app_user;

The same can be done from the shell with createuser and createdb. Roles and privileges get a whole lesson later: Roles, Privileges and GRANT.

Connection strings and environment variables

Every PostgreSQL client uses the same C library, libpq, or an equivalent, so they all understand the same connection settings. You can write them as a URI:

TEXT
postgresql://app_user:[email protected]:5432/shop?sslmode=require

or as space-separated keywords, which avoids URL-encoding problems:

TEXT
host=db.example.com port=5432 dbname=shop user=app_user sslmode=require

Special characters in a URI password must be percent-encoded: @ becomes %40, / becomes %2F. The next program splits a URI the way a client does, including the defaults that apply when parts are missing:

Python Taking a connection URI apart

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The third example is a complete, valid connection string: everything omitted falls back to a default. Those defaults can also come from environment variables, which every libpq-based tool reads:

Variable Meaning
PGHOST Server host name or socket directory
PGPORT Port, default 5432
PGDATABASE Database name
PGUSER Role name, default is your OS user name
PGPASSWORD Password (convenient, but visible to other processes; prefer .pgpass)
PGSSLMODE disable, prefer (default), require, verify-full and so on
PGSERVICE A named bundle of settings from pg_service.conf

With these set, a bare psql connects with no arguments:

Terminal
export PGHOST=localhost PGUSER=app_user PGDATABASE=shop
psql

For passwords, create a password file, ~/.pgpass on Linux and macOS (%APPDATA%\postgresql\pgpass.conf on Windows), with one line per server in the form host:port:database:user:password. On Unix it must be readable only by you (chmod 600 ~/.pgpass) or it is ignored.

Once connected, a few queries confirm where you landed:

SQL
-- PostgreSQL
SELECT version();
SELECT current_database(), current_user, inet_server_port();

pg_isready is a tiny tool that only asks whether a server is accepting connections, handy in scripts and health checks:

Terminal
pg_isready -h localhost -p 5432
# localhost:5432 - accepting connections

If a connection fails, read the error. "Connection refused" means nothing is listening (server stopped, wrong port, or listen_addresses too narrow). "No pg_hba.conf entry" means a server is there but no rule allows you. "Password authentication failed" means the rule matched but the credentials were wrong.

Loading sample databases

Learning is much faster with a realistic database than with three-row tables. Several well-known sample databases exist for PostgreSQL: DVD-rental style schemas (such as Pagila, a PostgreSQL port of a classic MySQL sample), the Chinook music store, Northwind ports, and an airline-bookings demo database published by a PostgreSQL vendor with small, medium and large editions. They are separate community projects with their own licenses and download pages, so look up the current files and instructions in each project's README rather than relying on a file name from this lesson.

A sample database usually comes in one of two shapes, and you load each with a different tool.

A plain SQL script (.sql, or .sql.gz when compressed) is just text full of CREATE TABLE and INSERT or COPY statements. Create an empty database and feed it to psql:

Terminal
createdb sampledb
psql -d sampledb -v ON_ERROR_STOP=1 -f sample-schema.sql
psql -d sampledb -v ON_ERROR_STOP=1 -f sample-data.sql

# Compressed script
gunzip -c sample.sql.gz | psql -d sampledb -v ON_ERROR_STOP=1

-v ON_ERROR_STOP=1 makes psql stop at the first error instead of scrolling past hundreds of failures. Inside an interactive session the equivalent of -f is \i sample-schema.sql.

An archive made by pg_dump (custom format, a .tar file, or a directory) is not text and psql cannot read it. Use pg_restore:

Terminal
createdb sampledb
pg_restore --dbname=sampledb --no-owner --no-privileges --verbose sample.tar

# Custom and directory formats can restore in parallel
pg_restore --dbname=sampledb --jobs=4 sample.dump

# Look inside an archive without loading it
pg_restore --list sample.dump

--no-owner and --no-privileges stop the restore from trying to assign objects to roles that do not exist on your machine, which is the most common cause of errors. A downloaded .zip has to be unpacked first. If you are unsure which kind of file you have, file sample.dump tells you (a custom archive is reported as "PostgreSQL custom database dump"), and a plain script opens as readable text.

Some sample databases are installed as a dump you restore into a database that already has a particular name, and some scripts create the database themselves with CREATE DATABASE inside the script. Read the first lines of a script, or the README, before running it, as you would for any script you download.

Check what you loaded

SQL
-- PostgreSQL, in psql
\l                       -- databases
\c sampledb              -- connect to the new one
\dn                      -- schemas (some samples create their own)
\dt *.*                  -- tables in every schema
\d+ film                 -- columns of one table ("film" is an example name)

SELECT schemaname, relname, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC
LIMIT 10;

n_live_tup is an estimate that may read 0 right after a load; run ANALYZE; and look again. Then explore: count rows, read the foreign keys, and write the joins you would need for a real report. Practice sets and projects are in PostgreSQL Exercises and Project: Bookings.

Make your own practice data

When you cannot find a suitable download, generate rows with generate_series and random(); see Bulk Loading and Set-Returning Functions:

SQL
-- PostgreSQL
CREATE TABLE practice_orders AS
SELECT g AS id,
       (random() * 999 + 1)::int          AS customer_id,
       round((random() * 200)::numeric, 2) AS amount,
       now() - (random() * 365) * interval '1 day' AS created_at
FROM generate_series(1, 100000) AS g;

Whichever way you load data, the next program helps you pick the right command: it looks at a file name and prints the matching load command. It is a helper written for this lesson, running in your browser, not a PostgreSQL tool.

Python Which load command for which file?

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the first column of a pg_hba.conf line, TYPE, distinguish?

    local means a Unix-domain socket, host means TCP/IP. The other columns pick the database, user, address and authentication method.
  2. 2. Two pg_hba.conf rules both match a connection. Which one applies?

    PostgreSQL stops at the first matching line, so specific rules must be placed above general ones.
  3. 3. You run psql with no arguments, and PGHOST, PGUSER and PGDATABASE are set. Where does psql get its settings?

    libpq clients read the PG* environment variables and fall back to built-in defaults, such as port 5432 and your OS user name.
  4. 4. Why is a -v volume mounted in the docker run command?

    Without a volume, the cluster lives in the container's own filesystem and disappears with the container.

Summary

  • A server is installed from Docker, OS packages, installers or a managed service; the cluster is created by initdb (usually done for you).
  • The two key files are postgresql.conf (settings) and pg_hba.conf (who may connect), and SHOW data_directory; tells you where they live.
  • pg_hba.conf rules are checked top to bottom and the first match wins; reload with SELECT pg_reload_conf();.
  • Connect with a URI, a keyword string or PG* environment variables; keep passwords in ~/.pgpass, not in scripts.
  • Create a dedicated login role and database for each application instead of using postgres.
  • Defaults: port 5432, listening on localhost only, scram-sha-256 password hashing.

Next: psql: The Command-Line Client, the tool you will use all day.