1. Courses
  2. Engineering Roles

Engineering Roles line, station 5 of 8

Database Administration with PostgreSQL

Keep production PostgreSQL fast, safe and recoverable: how the server works inside, tested backups, replication and failover, tuning, security, upgrades and on-call.

  • 6lessons
  • 4 hto read and do
  • Intermediatelevel
  • 6podcast episodes
  • 10checked exercises
Start the courseCheck what you know

Tools you'll use in this course

The lessons open these with a template ready. They work for your own job too, no course needed.

About this course

A database administrator (DBA) makes sure data is never lost, always available and fast enough. Even with managed cloud databases, someone has to design backups and prove they restore, plan upgrades, find the slow queries, size the disks and decide who may read what. This course teaches that job on PostgreSQL, the most widely used open-source database, for people who already write SQL.

You look after the databases behind Mangrove Market, a fictional grocery chain: the order database behind its delivery app, about 800 GB and busiest at the evening peak, and the store database fed by 320 stores. You learn what the server does with every write (processes, memory, WAL, MVCC and vacuum), set up backups with point-in-time recovery and time a restore drill, add a streaming replica and plan a failover, rank queries with pg_stat_statements and fix the worst with data, lock access down with roles, least privilege and row-level security, and plan a major version upgrade, bloat clean-up, capacity and the runbooks for the alerts that wake you at 3 a.m.

Every lesson says what a managed service (such as Amazon RDS, Google Cloud SQL or Azure Database for PostgreSQL) does for you and what stays your job. Lessons have diagrams, quick checks, a Build it section with real configuration and commands, auto-checked SQL exercises that run on a real PostgreSQL in your browser, flashcards and a podcast episode. The capstone makes the order database production-grade, with evidence.

What you will be able to do

  • Explain what PostgreSQL does with every write (processes, shared buffers, WAL, checkpoints, MVCC and vacuum) and spot the problems each one causes
  • Design backups with point-in-time recovery, monitor them, and prove them with timed restore drills against a stated RPO and RTO
  • Set up streaming replication, measure lag, and plan and test failover, on your own servers or a managed service
  • Find the queries that cost the most with pg_stat_statements and fix them with indexes, rewrites, statistics, autovacuum settings and connection pooling, backed by before and after numbers
  • Secure a database with roles, least privilege, pg_hba rules, TLS, row-level security and audit logs, and plan upgrades, capacity and on-call runbooks

Syllabus

6 stops, about 4 h in total. Each stop fills in when you complete the lesson.

  1. Inside a PostgreSQL server

    Most database alerts at 3 a.m. trace back to a few moving parts: processes, memory, the write-ahead log and row versions. This lesson opens the box, so you can read what Mangrove's order database is doing and predict what will go wrong before it does.

    35 minIntermediateEpisode2 exercises

    PostgreSQLMonitoringPerformance tuning

  2. Backups you have actually restored

    A backup job that prints "ok" every night tells you very little. This lesson turns the order database's backups into something you can prove: you will measure your real recovery numbers, know what each kind of backup leaves out, pick the right base backup and WAL for a restore, watch the archive, and run a timed restore drill every month.

    40 minIntermediateEpisode2 exercises

    PostgreSQLBackup and recoveryMonitoring

  3. Replication and failover

    A replica only helps on the bad night if it is close behind, its WAL is not filling the primary's disk, and everyone agrees who the primary is. This lesson gives you the operator's view of the order database's replicas: how they connect, how far behind they are, and how a failover happens without two primaries.

    35 minIntermediateEpisode1 exercise

    ReplicationMonitoringPostgreSQL

  4. Finding and fixing slow queries

    A DBA does not wait for someone to say "the app is slow". You keep the numbers that show where the database spends its time, catch the plans of the slow runs, and fix one thing at a time with before and after numbers. This lesson turns that into a weekly routine for Mangrove Market's order database.

    40 minIntermediateEpisode2 exercises

    Performance tuningPostgreSQLMonitoring

  5. Security and access

    Most database breaches need no clever attack: an app that logs in as the table owner, a shared superuser password, a pg_hba.conf line that lets anyone try. This lesson shows how PostgreSQL decides who may connect, what each role may touch and which rows it sees, so you can lock down Mangrove's order database and prove it.

    40 minAdvancedEpisode2 exercises

    Database securityPostgreSQL

  6. Upgrades, maintenance and on-call

    A working database still needs care: new versions, bloated tables, a slowly filling disk, and alerts at 3 a.m. This lesson shows how a DBA upgrades PostgreSQL safely, keeps tables lean, sees trouble weeks ahead, and answers the common alerts calmly.

    40 minAdvancedEpisode1 exercise

    UpgradesMonitoringPostgreSQL

  7. Capstone: Make Mangrove's order database production-grade

    A realistic project with a rubric and a model answer.

  8. Final exam and certificate

    10 questions drawn at random, 70% to pass.

Capstone project

Make Mangrove's order database production-grade

Mangrove Market's order database (PostgreSQL 16, about 800 GB, growing about 1.5 GB a day) runs on a single server. Nightly pg_dump files exist but nobody has ever restored one, there is no replica, the app connects as a superuser, and a few queries time out at the evening peak, when orders run at four times normal on festival days. Head office has set the targets: lose at most 5 minutes of orders (RPO) and be back within 1 hour (RTO) after losing the server. Build the evidence that the database meets them. Use a local PostgreSQL (a virtual machine or containers are fine) with a scaled-down copy of the order schema and generated data, and keep a log of every command and result.

You will hand in

  • A backup and restore runbook with physical backups, WAL archiving and point-in-time recovery, plus the log of one timed restore drill to a chosen moment
  • A replica setup with lag monitoring and a failover runbook, with the log of one tested switchover or failover
  • A tuning report: the top queries from pg_stat_statements, each change made with its reason, and before and after numbers
  • A role and access matrix with the SQL that creates it (owner, migration, app, read-only and audit roles, plus any row-level security policies)
  • An upgrade and capacity note: the path to the next major version, the disk forecast and the alerts with their runbook links
Open the project brief

Check what you know

Already know some of this? Answer 6 quick questions, one from each lesson, and we will mark the lessons you can skim. It takes about 3 minutes and nothing is graded.

Questions

What do I need to know first?

Comfortable SQL (SELECT with joins, indexes and transactions, as taught in SQL and Data Modeling) and basic Linux from the command line (the TechOps course). You do not need to have run a production database before.

Why only PostgreSQL?

Depth beats breadth for this job. The concepts (write-ahead logs, replication, MVCC, query plans, roles) map closely to MySQL, SQL Server and Oracle, so what you learn carries over even when the commands differ.

Do DBAs still exist with cloud databases?

Yes, though the job has shifted from installing servers to reliability, performance, security and cost. A managed service takes backups, patches and fails over for you, but someone still has to set targets, test restores, tune queries, design access and plan upgrades. Every lesson says which parts are which.

How long does the course take?

About 4 to 5 hours for the six lessons with their checkpoints and practice tasks, plus roughly 60 to 70 minutes of podcast episodes. Allow 6 to 10 hours for the capstone, since it includes real restore and failover drills.

Is it really free?

Yes. Every lesson, podcast episode, exercise, quiz, final exam and certificate is free. The labs and their templates for real work are free too. Plus and Premium add every lab challenge, weekly extras and a mentor on top, and they never lock the tools or the courses.

How does the final exam work?

You get 15 questions drawn at random from a larger bank. You need 70% to pass. If you do not pass, you can try again after 24 hours. The exam is graded on the server and you never see the answer key.