Objectives
All students will learn how to:
Compile source code
Perform backup and disaster recovery
Work with inheritance, partitioning, and Tablespaces
Manage free space
Secure PostgreSQL
Create high availability and high performance solutions
Prerequisites
All PostgreSQL Administration training students should have familiarity with relational databases.
Duration
Five days
Outline for 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