Pebellslearn by building
RoadmapsMatrixCoursesSandboxSign in

Course details

PostgreSQL

Learn how PostgreSQL works as a system, not just a query language. You will install a server, connect with psql, tune its settings, give people the right access and take backups you can actually restore — all on one small bike-rental database.

Ready to learn6 sections · 23 lessons
Start learning

Course curriculum

Introduction3 lessons

See what a relational database really is, where it shines and where it struggles, and how PostgreSQL compares with NoSQL stores and with other relational systems.

  1. Relational Databases12 min
  2. PostgreSQL vs NoSQL12 min
  3. PostgreSQL vs Other RDBMS12 min
Object Model4 lessons

Learn the nesting of cluster, database, schema and table, the relational vocabulary behind tables, rows and columns, and how data types, domains, constraints and NULL keep the data honest.

  1. Clusters, Databases and Schemas12 min
  2. Tables, Rows and Columns12 min
  3. Data Types and Domains12 min
  4. Constraints and NULL12 min
High Level Database Concepts4 lessons

Understand transactions and ACID, how MVCC lets readers and writers work side by side, how the write-ahead log makes commits survive crashes, and how a query travels from text to results.

  1. Transactions and ACID14 min
  2. MVCC15 min
  3. The Write-Ahead Log14 min
  4. How a Query Is Processed15 min
Installation, Setup and Configuring5 lessons

Install PostgreSQL from packages or Docker, connect and look around with psql, start and stop the server with the right tool, and change settings, logging and statistics without surprises.

  1. Installing with a Package Manager12 min
  2. Running PostgreSQL in Docker12 min
  3. Connecting with psql12 min
  4. Starting and Stopping the Server12 min
  5. Configuration, Logging and Statistics15 min
Security3 lessons

Give every person and program its own role, open exactly the privileges each one needs, and control who may connect and how they prove it with pg_hba.conf.

  1. Roles12 min
  2. Granting and Revoking Privileges14 min
  3. pg_hba.conf and Authentication15 min
Backups and Recovery4 lessons

Make logical backups with pg_dump and pg_dumpall, restore them in the right order with pg_restore, take physical backups with pg_basebackup for point-in-time recovery, and validate that every backup really works.

  1. Logical Backups with pg_dump14 min
  2. Restoring with pg_restore and pg_dumpall14 min
  3. Physical Backups with pg_basebackup15 min
  4. Validating Backups14 min