Design, tune, operate and secure production databases: normalisation, indexing, backups, replication, access control, monitoring and safe migrations.

Database Administration

Install Docker Desktop (Windows/macOS) or Docker Engine (Linux), then confirm it works with `docker version`. If you prefer a native install, install PostgreSQL 17 from postgresql.org or your package manager instead.

Start the server: `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`. If port 5432 is already in use, change the left side, for example `-p 5433:5432`.

Connect with `docker exec -it pg-practice psql -U postgres` and run `SELECT version();`, `SHOW data_directory;`, `SHOW config_file;` and `SHOW hba_file;`. Write down each answer.

Run `SHOW max_connections;` and `SHOW shared_buffers;`, then `ALTER SYSTEM SET log_min_duration_statement = '250ms';` and `SELECT pg_reload_conf();`. Confirm with `SHOW log_min_duration_statement;`.

Create the database and roles: `CREATE DATABASE shop;`, `CREATE ROLE shop_owner NOLOGIN;`, `CREATE ROLE shop_app LOGIN PASSWORD 'app-practice-pw';`, `CREATE ROLE shop_readonly NOLOGIN;` and `CREATE ROLE analyst_ana LOGIN PASSWORD 'ana-practice-pw' IN ROLE shop_readonly;`.

Run `\c shop`, then `CREATE SCHEMA app AUTHORIZATION shop_owner;` and `GRANT USAGE ON SCHEMA app TO shop_app, shop_readonly;`. Check the result with `\du` and `\dn+`.

Open a second terminal and log in as the app role: `docker exec -it pg-practice psql -U shop_app -d shop -h localhost` (enter its password if asked; the image trusts connections from inside the container). Try `CREATE TABLE app.test (id int);` and note that it is refused, because shop_app does not own the schema.

Look at the authentication rules: `docker exec pg-practice cat /var/lib/postgresql/data/pg_hba.conf | grep -v '^#'`. Identify which lines use `trust` and which use `scram-sha-256` (the image trusts local socket connections; that is acceptable only for a practice container).

Stop the server with `docker stop pg-practice` and start it again with `docker start pg-practice`. Reconnect and confirm the `shop` database is still there (the volume kept it). Keep this server for the next lessons; when you finish the course, remove it with `docker rm -f pg-practice` and `docker volume rm pg-practice-data`.

## 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:

```bash
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?":

```sql
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:

```sql
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:

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

Detailed `GRANT`s 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.


Run a practice PostgreSQL server, find its configuration files, and create owner, application and read-only roles.

Installing and configuring PostgreSQL

PostgreSQL (1) Installation and Create Your First Database ( SQL shell / PgAdmin4 )

Group roles and users in PostgreSQL: super clear and thorough demo for beginners

Create practice data: `docker exec -it pg-practice psql -U postgres -d shop` and run `CREATE TABLE public.orders (id bigint PRIMARY KEY, customer_id int NOT NULL, total_cents int NOT NULL, ordered_at timestamptz NOT NULL DEFAULT now());` then `INSERT INTO public.orders SELECT g, (g % 50) + 1, (g * 37) % 10000, now() - (g || ' minutes')::interval FROM generate_series(1, 10000) g;`.

Record the facts you will verify later: `SELECT COUNT(*), SUM(total_cents) FROM public.orders;`. Write both numbers down.

Take a logical backup inside the container: `docker exec pg-practice pg_dump -U postgres -d shop -Fc -f /tmp/shop.dump`, and back up the roles: `docker exec pg-practice pg_dumpall -U postgres --globals-only -f /tmp/globals.sql`.

Copy the backups off the server to your machine: `docker cp pg-practice:/tmp/shop.dump ./shop.dump` and `docker cp pg-practice:/tmp/globals.sql ./globals.sql`. Check the file sizes are not zero.

Simulate the mistake: in psql on `shop`, run `DELETE FROM public.orders WHERE customer_id = 7;` and confirm the count dropped.

Restore beside the original, not over it: `docker exec pg-practice createdb -U postgres shop_restore`, then `docker exec pg-practice pg_restore -U postgres -d shop_restore --no-owner /tmp/shop.dump`. Note how long it takes.

Verify: connect to `shop_restore` and run the same `SELECT COUNT(*), SUM(total_cents) FROM public.orders;`. The numbers must match the ones you wrote down.

Repair only what was lost: in `shop`, run `CREATE EXTENSION IF NOT EXISTS dblink;` then `INSERT INTO public.orders SELECT * FROM dblink('dbname=shop_restore user=postgres', 'SELECT id, customer_id, total_cents, ordered_at FROM public.orders WHERE customer_id = 7') AS t(id bigint, customer_id int, total_cents int, ordered_at timestamptz);` and check the totals again.

Write a five-line restore runbook: backup location, restore commands, verification queries, time taken and one thing that surprised you. Then clean up: `docker exec pg-practice dropdb -U postgres shop_restore` and delete the local dump files when you no longer need them.

## Why it matters

Hardware fails, people run `DELETE` without a `WHERE`, ransomware encrypts disks, and a bad migration can corrupt a column. A DBA's most important promise is: **we can get the data back**. A backup that has never been restored is only a hope; recovery is what counts.

## Start with two numbers

- **RPO (recovery point objective):** how much data can you afford to lose? "Up to 24 hours" allows nightly dumps; "under 5 minutes" needs continuous log archiving.
- **RTO (recovery time objective):** how long can you be down while restoring? A 2 TB dump can take hours to load; a standby replica can take over in minutes.

Agree these with the business first; they decide the method and the cost.

## Logical vs physical backups

**Logical backups** export SQL-level data and definitions.

```bash
# One database, custom format (compressed, restorable selectively)
pg_dump -U postgres -d shop -Fc -f shop_2026-10-23.dump

# Roles and other cluster-wide objects are NOT in pg_dump
pg_dumpall -U postgres --globals-only -f globals.sql
```

Restore into a **new, empty** database, never straight over the live one:

```bash
createdb -U postgres shop_restore
pg_restore -U postgres -d shop_restore --no-owner shop_2026-10-23.dump
```

Logical dumps are portable across versions and machines, good for small to medium databases, migrations and copying single tables (`pg_restore -t orders`). They are slow for very large databases, and you can only recover to the moment the dump was taken.

**Physical backups** copy the database files plus the **write-ahead log (WAL)**. `pg_basebackup` takes a consistent copy of a running cluster. If you also archive every WAL segment (`archive_mode = on` and an `archive_command`, or a tool such as pgBackRest, Barman or WAL-G), you can replay the log up to any moment: **point-in-time recovery (PITR)**. In recovery you set `restore_command` and a target such as `recovery_target_time = '2026-10-23 14:05:00'`, and create a `recovery.signal` file (PostgreSQL 12+). This is how you undo "someone dropped a table at 14:06" with minutes of loss.

MySQL has the same split: `mysqldump` (logical) versus physical tools such as Percona XtraBackup or MySQL Enterprise Backup, plus binary logs for point-in-time recovery. Managed cloud databases offer automated snapshots and PITR windows; you still own testing them.

## Where backups live: 3-2-1

- **3** copies of the data,
- on **2** different kinds of storage,
- with **1** copy off-site (another region or provider), ideally immutable or offline so ransomware or an angry admin cannot delete it.

Encrypt backups, because a dump contains everything, including personal data. Restrict who can read them and log access. Apply your retention policy and data protection rules to backups too.

## Worked example: a restore test

A good restore test is scripted and repeatable:

1. Take the backup: `pg_dump -Fc -d shop -f shop.dump`.
2. Record facts to check: row counts of key tables and a checksum-like query, for example `SELECT COUNT(*), SUM(total_cents) FROM app.orders;`.
3. Restore into `shop_restore` on a separate server or container.
4. Run the same queries there and compare.
5. Time the whole thing. That is your real RTO for this method.
6. Write down what went wrong (missing roles, extensions, permissions) and fix the runbook.

Doing this monthly, or automatically after each backup, turns "we have backups" into "we can recover".

## Replication is not a backup

A replica copies every change, including the accidental `DROP TABLE`, within milliseconds. Replicas protect against hardware loss, not against mistakes. Keep real backups with history as well.

## Common mistakes

- Never testing a restore.
- Forgetting globals (roles, tablespaces) and extensions, so the restore fails on permissions.
- Storing backups on the same disk or account as the database.
- Restoring over production in a panic. Restore beside it, verify, then copy back only what is needed.
- No monitoring: a backup job that silently failed for three weeks.
- Keeping dumps of real customer data on laptops.

## Quick recap

- RPO and RTO choose the method: logical dumps, physical base backups plus WAL for PITR, or both.
- `pg_dump -Fc` plus `pg_dumpall --globals-only`, restored with `pg_restore` into a new database.
- Follow 3-2-1, encrypt, monitor backup jobs, and test restores on a schedule.

## Try it

In the **Do** tab, back up a practice database, "lose" some data, restore it beside the original and prove the counts match. Use practice data only.


Plan backups around RPO and RTO, take logical dumps, restore them into a separate database and prove the recovery works.

Backup and recovery

Backing Up a Postgres Database with pg_dump and pg_restore

PostgreSQL Backup & Point-In-Time Recovery

## Why it matters

A single database server is a single point of failure. When it dies, the application dies with it. **Replication** keeps one or more copies of the database up to date so that reads can be spread out and, when the main server fails, another one can take over. **High availability (HA)** is the overall design (replicas, failover automation, connection routing, monitoring) that keeps downtime within the agreed limits.

## Core vocabulary

- **Primary:** the server that accepts writes.
- **Replica / standby:** a server that receives the primary's changes. A *hot standby* also serves read-only queries.
- **Failover:** promoting a replica to become the new primary after the old one fails. A planned version is a **switchover**.
- **Replication lag:** how far behind the primary a replica is, in bytes of log or seconds.

## How PostgreSQL replicates

**Physical streaming replication** ships the write-ahead log (WAL), the same log used for crash recovery, from the primary to each standby, which replays it. The standby is a byte-for-byte copy of the whole cluster: same version, same databases. It is set up by taking a `pg_basebackup` of the primary and configuring `primary_conninfo`. Replication slots stop the primary from deleting WAL a standby still needs (watch them: an abandoned slot fills the disk).

**Logical replication** publishes changes to selected tables (`CREATE PUBLICATION` / `CREATE SUBSCRIPTION`) as row-level operations. It can cross major versions and replicate a subset of tables, which makes it useful for upgrades and feeding other systems. It does not copy schema changes automatically.

MySQL has the same ideas: binary-log replication (asynchronous or semi-synchronous) and Group Replication / InnoDB Cluster. SQL Server has Always On availability groups. Managed services (Amazon RDS Multi-AZ, Azure, Cloud SQL) run a standby and fail over for you.

## Synchronous vs asynchronous

- **Asynchronous** (the default): the primary commits and replies immediately; replicas catch up shortly after. Fast, but if the primary dies, the last few transactions may never reach a replica. That potential loss is your RPO.
- **Synchronous:** the primary waits until at least one standby confirms it has the WAL before reporting commit (`synchronous_standby_names`, `synchronous_commit`). No committed data is lost on failover, but every write pays a network round trip, and if the synchronous standby is unavailable, writes can stall. Teams often run one synchronous standby nearby and further asynchronous ones.

## Reading from replicas

Replicas can serve reporting and read-heavy traffic. Because of lag, a user who just saved a profile may read the old value from a replica. Route "read your own writes" to the primary, or accept and design for slight staleness. Monitor lag on the primary with:

```sql
SELECT application_name, state, sync_state, replay_lag
FROM pg_stat_replication;
```

## Failover, done safely

When the primary fails:

1. **Detect** it reliably (several health checks, not one missed ping).
2. **Fence** the old primary so it cannot accept writes if it comes back (stop it, cut its network, or revoke its virtual IP).
3. **Promote** the most up-to-date replica (`pg_ctl promote` or `SELECT pg_promote();`).
4. **Redirect** clients: update DNS, a virtual IP, a proxy (HAProxy, PgBouncer) or a service endpoint.
5. **Rebuild** the old primary as a new replica (`pg_rewind` can help) before you rely on redundancy again.

Skipping step 2 risks **split brain**: two servers both accepting writes, with diverging data that is painful to reconcile. Tools such as **Patroni** (with etcd or Consul for consensus) or managed cloud HA automate detection, fencing and promotion. Whatever you use, rehearse failovers regularly; an untested failover often fails when you need it.

## Measuring availability

Availability targets come in "nines": 99.9% allows about 8.8 hours of downtime a year, 99.99% about 53 minutes. Each extra nine needs more automation, more replicas and more practice. Remember that planned maintenance, such as upgrades, usually counts too.

## Common mistakes

- Treating replicas as backups (they copy mistakes instantly).
- Ignoring replication lag until a replica is hours behind and useless for failover.
- Manual failover with no fencing, causing split brain.
- Forgetting that applications cache connections and DNS; clients must reconnect to the new primary.
- Leaving abandoned replication slots that fill the primary's disk with WAL.

## Quick recap

- Streaming replication copies the whole cluster via WAL; logical replication copies chosen tables.
- Asynchronous is fast but can lose the last transactions on failover; synchronous trades write latency for zero loss.
- Safe failover: detect, fence, promote, redirect, rebuild. Automate it and rehearse it.

## Try it

The **Do** tab quiz tests replication and failover decisions.


Explain streaming and logical replication, synchronous vs asynchronous commits, replica lag, and how a primary fails over safely.

Replication and high availability

Database Replication Explained (in 5 Minutes)

Database Replication & Sharding Explained

Install and configure PostgreSQL, back up and restore with tested recovery, and understand replication, failover and high availability.