PostgreSQL Administration

Course Overview

I. Introduction

• A. History and Overview
• B. PostgreSQL Major Features
• C. PostgreSQL Limits
• D. PostgreSQL Architecture
• E. Write Ahead Logs
• F. The Background Writer
• G. PostgreSQL Data Types

II. Installation

• A. Overview
• B. Source Installation
• C. Initializing a cluster
• D. Starting and Stopping PostgeSQL
• E. Automatic startup

III.  Configuration

• A. Access Control
• B. Connection parameters
• C. Memory parameters
• D. Log Management
• E. Autovacuum settings
• F. Background process settings
• G. Access control settings

IV. PSQL

• A. Intro to PSQL
• B. Meta Commands
• C. Security 

V. Roles and Security

• A. Schemas
• B. Tables and Rules
• C. Users, Groups, and Roles
• D. Tables and Sequences
• E. Object Security 

VI. Managing PostgreSQL Databases

• A. Clusters
• B. Databases
• C. Tables
• D. Indexes 

VII. Postgres Tablespaces

• A. Postgres Tablespace definition and configuration
• B. Partitioning
• C. Transaction and concurrency control 
• D. Autovacuum function
• E. Functions and Operators 

VIII. Moving Data with PostgreSQL

• A. DML
• B. Copy command
• C. pg_dump 

IX. Routine Tasks

• A. Log Management
• B. Query Analysis
• C. Routine Vacuuming
• D. Recovering Disk Space 

X. Monitoring Statistics

• A. Database Logs
• B. OS Process Monitor
• C. Statistics Views 

XI. Performance Tuning

• A. OS Tuning
• B. Database Tuning
• C. Transaction Logs
• D. Query Tuning
• E. Checkpoints 

XII. Backup and Recovery

• A. pg_dump
• B. pg_dumpall
• C. pg_restore
• D. Point in Time recovery 

XIII. PostgreSQL Upgrade

• A. pg_upgrade
• B. Source upgrades
• C. Replication upgrades 

XIV. Replication

• A. Streaming replication
• B. Asynchronous Replication
• C. Failover
• D. Connection Pools
• E. HA Configuration 

XV. Triggers

• A. Trigger types
• B. Event Triggers 

XVI. Rules

• A. Rules and privileges
• B. Materialized Views

XVII. PL/pgSQL

• A. Structures
• B. Declarations
• C. Expressions
• D. Statements
• E. Cursors
• F. Error and Messages

XVIII. Other Procedural Languages

• A. PL/Perl
• B. PL/Python XIX.

Conclusion

A. Course Overview