PostgreSQL Architecture and Installation

PostgreSQL is the database behind most serious web applications, including PandaDev in production. A database administrator (DBA) keeps it available, secure, fast and recoverable. This track assumes basic SQL (see the SQL & Database Management track).

How it is organised

Each connection is a separate operating system process. That is why connection counts matter so much (module "Connections and Pooling").

Installing on Ubuntu

Use the official PostgreSQL repository to choose your major version:

sudo apt install -y postgresql-common
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
sudo apt install -y postgresql-16
sudo systemctl status postgresql

Files to know:

psql, the essential client

sudo -u postgres psql

\l                 -- list databases
\c pandadev        -- connect to one
\dt                -- list tables
\d users           -- describe a table
\du                -- list roles
\x auto            -- readable output for wide rows
\timing on         -- show how long each query takes
\q                 -- quit

Creating an application database

CREATE ROLE pandadev_app LOGIN PASSWORD 'use-a-long-random-password';
CREATE DATABASE pandadev OWNER pandadev_app;

The app connects with a URL:

postgresql://pandadev_app:PASSWORD@127.0.0.1:5432/pandadev

Generate the password with openssl rand -base64 32 and store it only in the environment (DATABASE_URL).

Managed or self-hosted?

Managed databases (Render Postgres, AWS RDS, Supabase, Neon) handle backups, patching and failover for you. Self-hosting costs less and teaches you more, but every task in this track becomes your job. Many teams start managed and only self-host with a specific reason. Either way, you need to understand everything here to use a database well.

Check yourself

• What is the WAL and why does it exist?
• Which file controls who may connect?
• Why does each connection matter more in PostgreSQL than in some other databases?