Installing and configuring PostgreSQL

Why it matters

Every other operations skill in this course needs a server you control: backups, monitoring, migrations and access control all start with "connect as an administrator". Installing PostgreSQL yourself also teaches where things live (data directory, configuration files, logs) and how roles work, which is exactly what you will be asked about on day one of a DBA job.

PostgreSQL is free, open source and widely used, and most of what you learn here maps to MySQL, SQL Server and managed cloud databases.

Three ways to run it

• Docker (recommended for practice). One command starts a disposable server, and deleting the container cleans everything up. Works on Windows, macOS and Linux once Docker Desktop or Docker Engine is installed.
• Native install. The official installers at postgresql.org (Windows and macOS via EDB's packages or Postgres.app) or your Linux package manager (sudo apt install postgresql on Ubuntu/Debian). Closer to how many servers run, but harder to remove cleanly.
• Managed cloud (Amazon RDS, Azure Database for PostgreSQL, Google Cloud SQL, Neon, Supabase). Great in production, but it hides the files you want to learn about and can cost money. Not needed for this course.

A practice server with Docker, pinned to major version 17 so the paths below match:

docker run --name pg-practice \
  -e POSTGRES_PASSWORD=change-me-practice \
  -p 5432:5432 \
  -v pg-practice-data:/var/lib/postgresql/data \
  -d postgres:17
docker exec -it pg-practice psql -U postgres

The -v volume keeps your data when the container restarts. The postgres account is the superuser: it bypasses every permission check, so use it only for administration. The password above is for a local practice container; never reuse it anywhere real.

Finding your way around

Inside psql, a few commands answer "where is everything?":

SELECT version();
SHOW data_directory;
SHOW config_file;      -- postgresql.conf
SHOW hba_file;         -- pg_hba.conf
SHOW listen_addresses;
SHOW max_connections;
SHOW shared_buffers;

Useful psql meta-commands: \l lists databases, \du lists roles, \dt lists tables, \c dbname connects to another database, \q quits.

The two key files:

• postgresql.conf holds server settings: memory (shared_buffers, work_mem), connections (max_connections), logging, write-ahead log and replication options. You can also run ALTER SYSTEM SET work_mem = '16MB';, which writes to postgresql.auto.conf. Many settings take effect after SELECT pg_reload_conf();; some (such as shared_buffers) need a restart.
• pg_hba.conf ("host-based authentication") decides who may connect from where, and how: each line lists a connection type, database, user, address range and method. Prefer scram-sha-256 for passwords; avoid trust anywhere except a throwaway local test.

Roles: users and groups are the same thing

In PostgreSQL a role can log in (a user), own objects, and be granted to other roles (a group). A common, safe pattern for one application:

CREATE DATABASE shop;
CREATE ROLE shop_owner NOLOGIN;                 -- owns the schema, no logins
CREATE ROLE shop_app LOGIN PASSWORD 'app-practice-pw';
CREATE ROLE shop_readonly NOLOGIN;              -- a group for reporting
CREATE ROLE analyst_ana LOGIN PASSWORD 'ana-practice-pw' IN ROLE shop_readonly;

Then connect to shop and set up a schema owned by the owner role:

\c shop
CREATE SCHEMA app AUTHORIZATION shop_owner;
GRANT USAGE ON SCHEMA app TO shop_app, shop_readonly;

Detailed GRANTs come in the Access control lesson. Note that since PostgreSQL 15, ordinary users can no longer create objects in the public schema by default, which is a safer starting point.

Configuration that matters on a real server

You will not tune a laptop container, but know the main settings:

• shared_buffers: PostgreSQL's own cache, often about a quarter of RAM on a dedicated server.
• work_mem: memory per sort or hash operation, per query node; large values multiplied by many connections can exhaust memory.
• max_connections: each connection is a process. Use a pooler (PgBouncer) instead of raising this a lot.
• Logging: log_min_duration_statement logs queries slower than a threshold.

Change one setting at a time, record why, and keep configuration in version control.

Common mistakes

• Letting applications connect as postgres.
• Opening port 5432 to the internet, or using trust in pg_hba.conf.
• Editing postgresql.conf and forgetting to reload, or reloading a setting that needs a restart.
• Upgrading the Docker image tag (for example to a new major version) on an existing data volume: major versions need pg_upgrade or dump and restore.

Quick recap

• Run a disposable practice server with Docker (postgres:17) or a native install.
• postgresql.conf holds settings; pg_hba.conf controls who connects and how.
• Roles are users and groups. Use a no-login owner role, a login role for the app and group roles for people.

Try it

Follow the Do tab to start your practice server, explore it and create the three roles. You will reuse this server for the rest of the course.