CertGrid CertGrid

PostgreSQL

Roles, databases, backups, replication and service troubleshooting.

47 published guides

32 of 47 guides need path access. Unlock PostgreSQL path

47-guide PostgreSQL learning path · 47 published · about 740 min of reading · 10 learning tracks · reviewed 27 August 2026

Foundations5 guides

Install it, find the cluster, and connect with psql.

  1. Installation 15 min

    PostgreSQL Installation on Ubuntu 26.04

    One apt command installs PostgreSQL 18 and starts a cluster.

    Updated 27 Aug 2026

  2. Configuration 14 min

    PostgreSQL Roles and Peer Authentication

    Create a role with your own name and the connection that failed a moment ago succeeds with no password at all. pg_hba.conf explains why - and why the same role needs a password over TCP.

    Updated 27 Aug 2026

  3. Hands-on Lab 13 min

    PostgreSQL psql Client

    psql's backslash meta-commands are a second language beside SQL - \l, \du, \dt, \conninfo - and pg_settings is how you read the server's configuration as a table rather than a file.

    Updated 27 Aug 2026

  4. Troubleshooting 14 min

    PostgreSQL Databases, Schemas and search_path

    \dt says the database has no tables.

    Updated 27 Aug 2026

  5. Configuration 13 min

    PostgreSQL Cluster and Service Management

    Ubuntu wraps PostgreSQL in two layers - a systemd unit per cluster and a postgresql umbrella that controls none of them directly.

    Updated 27 Aug 2026

Schemas and Data5 guides

Databases, schemas, tables and the SQL that fills them.

  1. Hands-on Lab 15 min

    PostgreSQL Tables and Data Types

    Seven columns, each a deliberate choice: identity rather than serial, text rather than varchar(n), numeric for money, timestamptz for time, and native array and jsonb types that MySQL has no equivalent for.

    Updated 27 Aug 2026

  2. Troubleshooting 15 min

    PostgreSQL Constraints and Error Messages

    Four inserts that fail, and the point is not that they fail but how much the server tells you: the constraint by name, the failing value, the entire failing row, and a HINT naming the syntax that would have worked.

    Updated 27 Aug 2026

  3. Concepts 14 min

    PostgreSQL Timestamps and Time Zones

    The same instant stored in both timestamp types, then read in a different session timezone.

    Updated 27 Aug 2026

  4. Hands-on Lab 13 min

    PostgreSQL UPSERT with ON CONFLICT

    `INSERT ...

    Updated 27 Aug 2026

  5. Hands-on Lab 15 min

    PostgreSQL JSON and Array Columns

    jsonb and text[] are real column types with real operators - containment, extraction, membership, expansion - so semi-structured data stays queryable in SQL rather than becoming a string the database cannot see into.

    Updated 27 Aug 2026

Configuration5 guides

postgresql.conf, memory, checkpoints and autovacuum.

  1. Configuration 12 min Paid

    PostgreSQL Configuration Files

    Three files matter and Debian puts them somewhere upstream documentation does not expect.

    Updated 27 Aug 2026

  2. Concepts 13 min Paid

    PostgreSQL Settings and Contexts

    pg_settings.context answers the question every configuration change starts with: does this need a restart, a reload, or nothing at all.

    Updated 27 Aug 2026

  3. Configuration 14 min Paid

    PostgreSQL ALTER SYSTEM and postgresql.auto.conf

    SET lasts one session, ALTER SYSTEM writes a file the server owns.

    Updated 27 Aug 2026

  4. Hands-on Lab 14 min Paid

    PostgreSQL Reload, Restart and pending_restart

    Change shared_buffers, reload, and the value does not move - but pending_restart flips to t.

    Updated 27 Aug 2026

  5. Troubleshooting 13 min Paid

    PostgreSQL Configuration Precedence

    pg_settings.source names where every live value came from - default, configuration file, or a session override.

    Updated 27 Aug 2026

Roles and Authentication5 guides

Roles, pg_hba.conf, and the file that decides who gets in.

  1. Hands-on Lab 13 min Paid

    PostgreSQL Roles and Attributes

    PostgreSQL has one object where other databases have two: a role is a user when it can log in and a group when it cannot.

    Updated 27 Aug 2026

  2. Troubleshooting 16 min Paid

    PostgreSQL Password Authentication

    A remote client with the right password is still refused - because no pg_hba rule covers it.

    Updated 27 Aug 2026

  3. Troubleshooting 17 min Paid

    PostgreSQL pg_hba.conf Rule Order

    The same two rules, the same client, opposite outcomes - decided entirely by which line comes first. pg_hba.conf stops at the first match, and a reject below a permit rule does nothing at all.

    Updated 27 Aug 2026

  4. Troubleshooting 16 min Paid

    PostgreSQL Database, Schema and Table Privileges

    Three separate refusals before a role can read one table: CONNECT on the database, USAGE on the schema, SELECT on the table.

    Updated 27 Aug 2026

  5. Hands-on Lab 16 min Paid

    PostgreSQL Default Privileges and Role Membership

    A grant covers the tables that exist when you make it, and nothing created afterwards. ALTER DEFAULT PRIVILEGES fixes the future; a group role fixes the past - and you usually need both.

    Updated 27 Aug 2026

Backup and Restore5 guides

pg_dump, base backups, WAL archiving and point-in-time recovery.

  1. Hands-on Lab 14 min Paid

    PostgreSQL Logical Backups with pg_dump

    pg_dump backs up one database and not the roles that own it - the dump contains zero CREATE ROLE statements.

    Updated 27 Aug 2026

  2. Hands-on Lab 14 min Paid

    PostgreSQL Database Restore

    The database is dropped for real - which first requires evicting every connection - and rebuilt from the dump.

    Updated 27 Aug 2026

  3. Troubleshooting 15 min Paid

    PostgreSQL Custom Format Dumps and Selective Restore

    A custom-format dump carries a table of contents, so you can restore one table out of it.

    Updated 27 Aug 2026

  4. Hands-on Lab 15 min Paid

    PostgreSQL Physical Backups with pg_basebackup

    A physical copy of the entire cluster taken over the replication protocol while it runs - every database, every role, ready to start as a server.

    Updated 27 Aug 2026

  5. Configuration 17 min Paid

    PostgreSQL WAL Archiving and Recovery Readiness

    A base backup restores last night.

    Updated 27 Aug 2026

Replication7 guides

A streaming standby, promotion, cascading and logical replication.

  1. Configuration 14 min Paid

    PostgreSQL Replication Prerequisites

    Four things before a standby exists: the right wal_level (already the default), a role with REPLICATION, a pg_hba rule for the special replication database, and a slot so the primary keeps the WAL the standby will need.

    Updated 27 Aug 2026

  2. Hands-on Lab 17 min Paid

    PostgreSQL Streaming Standby Creation

    One pg_basebackup -R builds the standby, writes its connection settings and drops a standby.signal file.

    Updated 27 Aug 2026

  3. Hands-on Lab 15 min Paid

    PostgreSQL Replication Monitoring

    The primary and the standby each have their own view, and neither is complete.

    Updated 27 Aug 2026

  4. Troubleshooting 16 min Paid

    PostgreSQL Replication Slots and WAL Retention

    A slot guarantees a standby can always catch up.

    Updated 27 Aug 2026

  5. Troubleshooting 16 min Paid

    PostgreSQL Promotion and Timelines

    pg_promote() turns a standby into a primary in seconds.

    Updated 27 Aug 2026

  6. Hands-on Lab 20 min Paid

    PostgreSQL Cascading Replication

    A standby that feeds another standby.

    Updated 27 Aug 2026

  7. Hands-on Lab 21 min Paid

    PostgreSQL Logical Replication

    Replicate one table into a writable database on another cluster.

    Updated 27 Aug 2026

Performance5 guides

EXPLAIN ANALYZE, index types, bloat and what vacuum is for.

  1. Hands-on Lab 16 min Paid

    PostgreSQL Query Plans with EXPLAIN

    EXPLAIN (ANALYZE, BUFFERS) gives estimates, actuals and the number that matters most - how many pages were touched.

    Updated 27 Aug 2026

  2. Hands-on Lab 17 min Paid

    PostgreSQL Index Types and Index Selection

    A partial index is a fifth the size and answers its query in 0.285 ms.

    Updated 27 Aug 2026

  3. Concepts 17 min Paid

    PostgreSQL VACUUM and Table Bloat

    An UPDATE of 133,333 rows leaves 133,333 dead tuples. VACUUM clears them and the table stays at exactly 47 MB.

    Updated 27 Aug 2026

  4. Configuration 14 min Paid

    PostgreSQL Query Statistics with pg_stat_statements

    One extension, one restart, and every statement the server runs is aggregated by shape with call counts and timings - finding the query that takes 5 ms and runs a hundred thousand times, which no slow-query threshold ever will.

    Updated 27 Aug 2026

  5. Troubleshooting 16 min Paid

    PostgreSQL work_mem and Sort Spill

    A sort spills to disk at the default work_mem.

    Updated 27 Aug 2026

Monitoring and Operations5 guides

The pg_stat views, the log, connections and pooling.

  1. Hands-on Lab 17 min Paid

    PostgreSQL pg_stat_activity and Session Control

    The first view anyone opens on a busy server - and the one that quietly lies to you.

    Updated 27 Aug 2026

  2. Hands-on Lab 18 min Paid

    PostgreSQL Locks and Blocked Queries

    pg_blocking_pids() names the session at fault in one column.

    Updated 27 Aug 2026

  3. Hands-on Lab 18 min Paid

    PostgreSQL Server Log Configuration

    logging_collector is off, pg_current_logfile() returns NULL, and there is still a log file.

    Updated 27 Aug 2026

  4. Hands-on Lab 17 min Paid

    PostgreSQL Statistics Views

    Cache hit ratio, sequential scans, dead tuples and unused indexes - and a reset that left the table reporting zero rows while holding four hundred thousand.

    Updated 27 Aug 2026

  5. Hands-on Lab 20 min Paid

    PostgreSQL Connection Limits and PgBouncer

    Fill every slot and read the error PostgreSQL actually gives - it is not the one everybody quotes.

    Updated 27 Aug 2026

Troubleshooting5 guides

Refused connections, stuck queries, a standby that fell behind.

  1. Hands-on Lab 19 min

    PostgreSQL Connection Failure Diagnosis

    Six ways a connection fails and the six different messages they produce - including the two that look like authentication problems and are not.

    Updated 27 Aug 2026

  2. Hands-on Lab 18 min

    PostgreSQL Startup Failure Diagnosis

    Three failures that all look identical to systemd, and three different places the real reason turns up - one of which is not the PostgreSQL log at all.

    Updated 27 Aug 2026

  3. Hands-on Lab 19 min

    PostgreSQL WAL Growth and Archive Failures

    A broken archive_command fills pg_wal while every query keeps working.

    Updated 27 Aug 2026

  4. Hands-on Lab 17 min

    PostgreSQL Transaction ID Wraparound

    The failure that takes a database offline to protect it.

    Updated 27 Aug 2026

  5. Hands-on Lab 18 min

    PostgreSQL Crash Recovery and Data Checksums

    kill -9 the whole cluster with a transaction open, then read what recovery did.

    Updated 27 Aug 2026

← Back to Learn