PostgreSQL Administration: Hands-On Training

Course Overview

Day 1: Foundations of PostgreSQL Administration

Chapter 1: Course Introduction

• Overview of course materials and agenda.
• Introductions and expectations.

Chapter 2: Installation and Configuration

• PostgreSQL versioning.
• Installation on Linux/UNIX platforms.
• Basic server configuration.
• Post-installation setup and account management.

Chapter 3: PostgreSQL Architecture

• PostgreSQL instance overview.
• Data processing, shared memory, and directory structures.
• Configuration files and WAL architecture.

Chapter 4: Client Applications and Database Setup

• Introduction to client tools (psql, pgAdmin).
• Database creation, configuration, and management.
• Troubleshooting common client connection issues.

Day 2: Advanced Administration and Security

Chapter 5: PostgreSQL Schemas

• Schema concepts and management.
• Information and system catalog schemas.

Chapter 6: Role Management

• Role creation, attributes, and membership.
• Troubleshooting role access issues.

Chapter 7: Fine-Grained Access Control

• Grant and revoke commands.
• Managing column-level permissions and schema privileges.

Chapter 8: Index Creation and Management

• Index types and best practices.
• Partial, unique, and multi-column indexes.
• Reindexing and maintaining indexes efficiently.

Chapter 9: Transactions and Concurrency

• Transaction properties and isolation levels.
• Multi-version concurrency control.
• Deadlock detection and advisory locks.

Day 3: Optimisation, Maintenance, and Recovery

Chapter 10: Database Structure and Tablespaces

• Physical storage layout.
• Creating and managing tablespaces.

Chapter 11: Logging and Extensions

• Configuring PostgreSQL logging.
• Installing and managing extensions.

Chapter 12: Backup and Recovery

• Methods for database backup and restore.
• pg_dump, pg_restore, and point-in-time recovery.

Chapter 13: Query Optimisation and Performance Tuning

• Using EXPLAIN and ANALYZE to optimise queries.
• Index and query planner statistics.
• Memory settings and performance tuning parameters.

Chapter 14: Routine Maintenance and Monitoring

• Configuring autovacuum and routine cleanup tasks.
• Monitoring activities and statistics.
• Efficiently populating databases.

Prerequisites

• Basic Knowledge of RDBMS Concepts: Familiarity with relational database principles, SQL queries, and common database objects like tables, indexes, and views.
• Experience with Command-Line Tools: Comfort with using command-line interfaces for database administration tasks.
• Understanding of Operating Systems: Basic experience with Linux/UNIX and Windows operating systems, including file systems and user account management.
• Technical Background: Prior experience as a database administrator or similar technical role is beneficial but not required.