PostgreSQL Administration Training

Chapter 1. PostgreSQL Introduction & Architecture 

Introduction & History

PostgreSQL Major Features

PostgreSQL Architecture Overview

Chapter 2. PostgreSQL Data Types

Chapter 3. PostgreSQL Installation 

Platforms

Binary Installation

Source Installation

Binary vs. Source – Pros & cons

Initializing a PostgreSQL Cluster

Starting & Stopping a PostgreSQL Cluster

Automatic Startup / Shutdown

Common Issues & Troubleshooting

Chapter 4. PostgreSQL Configuration 

Access Control

The postgresql.conf file

Common Issues & Troubleshooting

Chapter 5. Introduction to psql 

Command line parameters

Meta Commands

SET Commands

psql Security

Chapter 6. Functions & Operators

Chapter 7. Managing PostgreSQL Databases 

Creating PostgreSQL Databases

Creating Schemas

Creating Tables

Altering Tables

SELECT & Joins

Indexes & Foreign Keys

Chapter 8. PostgreSQL Roles and Security 

Views

Rules

Users, Groups & Roles

Sequences

Object Security

Chapter 9. Moving Data with PostgreSQL 

Basic DML

COPY

Other Tools

Chapter 10. Tablespaces, Inheritance and Data Partitioning 

Tablespaces

Inheritance

PostgreSQL Data Partitioning

Chapter 11. VACUUM 

Routine Vacuuming

Benefits of Vacuuming

Recovering Disk Space

Updating Planner Statistics

Chapter 12. Transaction ID Wraparound Failure

Vacuum Lab?

Transactions & Concurrency Control 

Transactions

Concurrency

Chapter 13. Routine DBA Tasks and Best Practices 

Log Management

Query Analysis

Routine Vacuuming

Recovering Disk Space

Managing Planner Statistics

REINDEX

LAB

Chapter 14.  Monitoring and Statistics 

Database Logs

OS Process Monitoring

The PostgreSQL Statistics Collector

Statistics Views

Statistics Functions

LAB

Chapter 15. PostgreSQL Tools Overview 

PG Badger

PG Bouncer

PG Pool

PGCLUU

PG Admin

PG Modeler

MySQL Workbench

pgbench

Consistent State PTS

Chapter 16. PostgreSQL Performance Tuning 

OS Tuning

HW Configuration

Transaction Logs

Tablespaces & Partitioning

Checkpoint Tuning

Query Tuning

Chapter 17. PostgreSQL Backup and Recovery 

pg_dump

pg_dumpall

Recovery Options

Restore via a List File

Point In Time Recovery (PITR) Based Backup

PITR Based Recovery

Chapter 18. PostgreSQL Upgrade Methods 

Minor Version Upgrades

pg_upgrade

RPM Based Upgrade

Source Based Upgrade

SLONY Based Upgrade

Chapter 19. PostgreSQL Streaming Replication 

Overview

Configuration

Base Backup

Recovery.conf

Initializing Streaming Replication

Standby Conflicts

Monitoring

Standby Promotion

Cascading Replication

WAL Shipping

Replication Slots

Synchronous Replication

Chapter 20. SLONY 

Overview

Configuration & Setup

Monitoring

Executing DDL

Adding Tables to Replication

Switchover

Failover

Chapter 21. PostgreSQL High Availability 

Overview

Replication Type Selection

Connection Poolers

Heartbeat Monitoring

Failing Over

Failing Back

PostgreSQL and AWS

PostgreSQL RDS Overview

PostgreSQL Redshift Overview

The PostgreSQL Contribs

Conclusion