Lesson 02 beginner

Installing PostgreSQL

Install PostgreSQL 18 on Ubuntu, Debian, Rocky Linux, macOS and Windows, or run it in Docker; check the server is up, open your first psql session, create a role for yourself and find the configuration files and data directory.

In this lesson you get a PostgreSQL 18 server running, connect to it and create a login for yourself so later lessons do not need sudo. Pick the section for your system; the Docker option works everywhere and is the quickest to throw away.

Which option to choose

OptionGood forNotes
Linux packages (PGDG)Servers, VMs, WSLOfficial repositories with every major version
Homebrew or Postgres.appmacOS laptopsRuns as your user
Windows installerWindows desktopsIncludes pgAdmin and Stack Builder
DockerAny OS, quick experimentsOne command to start, one to delete
Managed cloudProduction without running serversRDS, Cloud SQL, Azure, Supabase, Neon

Tip: Linux distributions ship their own Postgres packages, but they are often a version or two behind. The PGDG repositories run by the PostgreSQL project always have the current release and every minor update.

Ubuntu and Debian

The postgresql-common package includes a script that adds the PGDG repository for your release:

$ sudo apt install -y postgresql-common
$ sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
$ sudo apt install -y postgresql-18
$ pg_lsclusters

Debian-family packages create a cluster called 18/main and start it right away. pg_lsclusters lists each cluster with its version, port, status and data directory.

Rocky Linux, AlmaLinux and RHEL

$ sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
$ sudo dnf -qy module disable postgresql
$ sudo dnf install -y postgresql18-server
$ sudo /usr/pgsql-18/bin/postgresql-18-setup initdb
$ sudo systemctl enable --now postgresql-18

Unlike Debian, Red Hat packages do not create a cluster automatically: postgresql-18-setup initdb does that, and the systemd unit is named after the version.

macOS

With Homebrew:

$ brew install postgresql@18
$ brew services start postgresql@18
$ psql postgres

Homebrew creates the cluster under your own macOS user, so you are already a superuser. Postgres.app is a good alternative if you prefer a menu-bar app with several versions side by side.

Windows

Download the installer from the PostgreSQL website (it links to the EnterpriseDB build), run it and keep the defaults: port 5432, the postgres superuser and the password you choose. It installs the server as a Windows service, plus pgAdmin, a graphical client. Use SQL Shell (psql) from the Start menu to follow this course, or install Postgres inside WSL and use the Ubuntu instructions.

Docker

$ docker run -d --name orbit-db \
    -e POSTGRES_PASSWORD=change-me \
    -p 5432:5432 \
    -v orbit-pgdata:/var/lib/postgresql \
    postgres:18
$ docker exec -it orbit-db psql -U postgres

The named volume orbit-pgdata keeps your data when the container is removed. From the 18 image on, mount the volume at /var/lib/postgresql: the image keeps the data in a versioned folder below it so future upgrades are easier. Older tags used /var/lib/postgresql/data, which you will still see in many examples, including the Docker Tutorial.

Check that the server is running

pg_isready is the quickest health check and also works in scripts:

$ pg_isready -h localhost -p 5432
localhost:5432 - accepting connections

Its exit code is 0 when the server accepts connections, 1 while it is starting up, 2 when there is no response.

Your first psql session

On Linux, the packages create an operating-system user called postgres and a database superuser with the same name. Postgres’s peer authentication lets the OS user postgres log in as the database role postgres over the local socket, without a password:

$ sudo -u postgres psql

You are now at the psql prompt. The # at the end of the prompt means you are a superuser; ordinary roles see >. Ask the server which version it runs:

SHOW server_version;
SELECT current_user, current_database();
 current_user | current_database
--------------+------------------
 postgres     | postgres
(1 row)

Leave psql with \q (or Ctrl+D).

Create a role for yourself

Typing sudo -u postgres every time gets old. Create a role with the same name as your Linux user and a database with the same name; psql uses both by default:

$ sudo -u postgres createuser --createdb --createrole "$USER"
$ createdb "$USER"
$ psql

createuser and createdb are small wrappers around the SQL commands CREATE ROLE and CREATE DATABASE. Your role can create databases and other roles, which is all you need for this course, but it is not a superuser.

Warning: Do not use the postgres superuser for applications. Lesson 15 creates a dedicated role for Orbit with only the rights it needs.

Where things live

Debian / UbuntuRHEL / RockyDocker postgres:18
Data directory/var/lib/postgresql/18/main/var/lib/pgsql/18/data/var/lib/postgresql/18/docker
Main config/etc/postgresql/18/main/postgresql.conf/var/lib/pgsql/18/data/postgresql.confin the data directory
Access rules/etc/postgresql/18/main/pg_hba.conf/var/lib/pgsql/18/data/pg_hba.confin the data directory
Logs/var/log/postgresql//var/lib/pgsql/18/data/log/docker logs orbit-db
Servicepostgresqlpostgresql-18the container

You never have to memorise this. Ask the server:

SHOW data_directory;
SHOW config_file;
SHOW hba_file;

Starting, stopping and reloading

$ sudo systemctl status postgresql       # postgresql-18 on RHEL
$ sudo systemctl restart postgresql      # needed for some settings
$ sudo systemctl reload postgresql       # re-read config without dropping clients

By default the server only listens on localhost. Letting other machines connect takes two changes, listen_addresses in postgresql.conf and a rule in pg_hba.conf, which lesson 15 covers properly.

Quick reference

TaskCommand
Install on Ubuntu (PGDG)sudo apt install postgresql-18 after the repo script
Install on Rockydnf install postgresql18-server then postgresql-18-setup initdb
Run in Dockerdocker run -d -e POSTGRES_PASSWORD=... -v vol:/var/lib/postgresql postgres:18
Is it up?pg_isready
Connect as superusersudo -u postgres psql
Create your own rolesudo -u postgres createuser --createdb --createrole "$USER"
Where is the config?SHOW config_file;
Reload settingssudo systemctl reload postgresql

Exercise

On Ubuntu, sudo -u postgres psql logs you in without asking for a password. Why?

  1. The postgres role has an empty password
  2. Local socket connections use peer authentication, which trusts the matching operating-system user
  3. psql caches the password in ~/.pgpass during installation
  4. Superusers never need passwords
Show answer

Answer: B. pg_hba.conf on Debian-family systems uses the peer method for local connections: the kernel reports which OS user is connecting, and Postgres accepts it as the role with the same name.

Exercise

You run postgres:18 in Docker with -v pgdata:/var/lib/postgresql/data, copied from an older guide. What is the better mount point for the 18 image?

  1. /data
  2. /var/lib/postgresql/18/data
  3. /etc/postgresql
  4. /var/lib/postgresql
Show answer

Answer: D. Starting with the 18 image, the volume is declared at /var/lib/postgresql and the data lives in a versioned subfolder, which makes later major upgrades simpler.

Exercise

pg_isready exits with status 1. What does that mean?

  1. The host name could not be resolved
  2. The server is running but rejecting connections, for example while it starts up
  3. The password was wrong
  4. The server is accepting connections normally
Show answer

Answer: B. 0 means accepting, 1 rejecting (usually starting up or shutting down), 2 no response and 3 that no connection attempt was made, for example because of bad parameters.

Key takeaways

  • Use the PGDG repositories on Linux to get the current major version and every minor update.
  • Docker is the fastest way to experiment; mount a named volume at /var/lib/postgresql for the 18 image.
  • pg_isready checks the server, sudo -u postgres psql gets you a superuser prompt on Linux.
  • Create a role named after your OS user so psql connects without extra flags.
  • SHOW data_directory, SHOW config_file and SHOW hba_file tell you where everything lives.

FAQ

I forgot the postgres password on Windows. What now?

Edit pg_hba.conf in the data directory, temporarily change the method for local connections to trust, restart the service, connect with psql and run ALTER ROLE postgres PASSWORD '...';. Then set the method back to scram-sha-256 and restart again.

Can I install several major versions on one machine?

Yes. The PGDG packages are versioned, so 17 and 18 can be installed side by side. Each cluster needs its own port: Debian gives the second one 5433 automatically, which pg_lsclusters shows.

psql says “role does not exist”. What happened?

psql connects as a role with the same name as your OS user unless you pass -U. Either connect with sudo -u postgres psql or create the role as shown in this lesson.

Should I use a managed database instead?

For production it is often the better choice: backups, failover, patching and monitoring are handled for you. Learn on your own installation first, because managed services still expect you to understand roles, indexes, vacuum and query plans.

What is pgAdmin, and do I need it?

pgAdmin is a free graphical client for browsing databases and running queries. It is optional. This course uses psql because it is available everywhere, including on servers without a desktop, and because its commands work the same on every platform.

↑ ↓ navigate Enter open Esc close