Database Management · Advanced level

Advanced level · Module 1 of 6 · 22 guided hours

PostgreSQL Administration

Install and configure PostgreSQL, manage roles and privileges, back up and restore, and keep servers healthy with routine maintenance.

Learning objectives

  • Install and configure a PostgreSQL server
  • Manage roles and privileges with least privilege
  • Back up, restore and maintain databases

Topics

  • installation and configuration files
  • client authentication (pg_hba.conf)
  • roles, privileges and default privileges
  • pg_dump, pg_restore and base backups
  • VACUUM, ANALYZE and autovacuum
  • monitoring and upgrades

Lessons

  1. Lesson 1 Installing and Configuring PostgreSQL

    Install PostgreSQL, understand its configuration files and control who may connect from where.

    postgresql.confpg_hba.conflisten_addressespassword authentication (scram-sha-256)

  2. Lesson 2 Roles, Privileges and Least Privilege

    Create roles for people and applications and grant only the privileges each needs.

    roleGRANT and REVOKEschema privilegesdefault privileges

  3. Lesson 3 Backups and Restores in PostgreSQL

    Choose between logical and physical backups, automate them and practise restores.

    pg_dump / pg_restorephysical base backupWAL archivingpoint-in-time recovery

  4. Lesson 4 Maintenance and Monitoring

    Keep the server healthy with VACUUM and ANALYZE, monitor its activity and plan upgrades.

    MVCC and dead rowsVACUUM and ANALYZEpg_stat_activityminor and major upgrades

Practical activity

Set up a PostgreSQL server for the health programme with roles for clinicians, data officers and auditors, automated backups and a weekly maintenance checklist.

Expected outcomes

  • A securely configured database server
  • Reliable backup and maintenance routines

Assessment

Practical: configure a server to a written specification; checked by connecting as each role and by restoring a backup.

Taught live by an instructor — online or in person. Free course lessons are for self-study and do not lead to a certificate — see registration options.