Course Catalog  > Data & Databases
TP3025

PostgreSQL Administration Training

PostgreSQL Administration training class teaches students how to build, query, administer, backup, and secure PostgreSQL.
Request Information
Request On-Site or Customized Course Info
Course Details

Duration

5 days

Prerequisites

Familiarity with relational databases

Skills Gained

  • 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
Course Outline
  • PostgreSQL Introduction & Architecture 
    • Introduction & History
    • PostgreSQL Major Features
    • PostgreSQL Architecture Overview
  • PostgreSQL Data Types
  • 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
  • PostgreSQL Configuration 
    • Access Control
    • The postgresql.conf file
    • Common Issues & Troubleshooting
  • Introduction to psql 
    • Command line parameters
    • Meta Commands
    • SET Commands
    • psql Security
  • Functions & Operators
  • 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
  • Moving Data with PostgreSQL 
    • Basic DML
    • COPY
    • Other Tools
  • Tablespaces, Inheritance and Data Partitioning 
    • Tablespaces
    • Inheritance
    • PostgreSQL Data Partitioning
  • VACUUM 
    • Routine Vacuuming
    • Benefits of Vacuuming
    • Recovering Disk Space
    • Updating Planner Statistics
  • Transaction ID Wraparound Failure
    • Vacuum Lab?
    • Transactions & Concurrency Control 
    • Transactions
    • Concurrency
  • Routine DBA Tasks and Best Practices 
    • Log Management
    • Query Analysis
    • Routine Vacuuming
    • Recovering Disk Space
    • Managing Planner Statistics
    • REINDEX
  • Monitoring and Statistics 
    • Database Logs
    • OS Process Monitoring
    • The PostgreSQL Statistics Collector
    • Statistics Views
    • Statistics Functions
  • PostgreSQL Tools Overview 
    • PG Badger
    • PG Bouncer
    • PG Pool
    • PGCLUU
    • PG Admin
    • PG Modeler
    • MySQL Workbench
    • pgbench
    • Consistent State PTS
  • PostgreSQL Performance Tuning 
    • OS Tuning
    • HW Configuration
    • Transaction Logs
    • Tablespaces & Partitioning
    • Checkpoint Tuning
    • Query Tuning
  • 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
  • PostgreSQL Upgrade Methods 
    • Minor Version Upgrades
    • pg_upgrade
    • RPM Based Upgrade
    • Source Based Upgrade
    • SLONY Based Upgrade
  • PostgreSQL Streaming Replication 
    • Configuration
    • Base Backup
    • Recovery.conf
    • Initializing Streaming Replication
    • Standby Conflicts
    • Monitoring
    • Standby Promotion
    • Cascading Replication
    • WAL Shipping
    • Replication Slots
    • Synchronous Replication
  • SLONY 
    • Configuration & Setup
    • Monitoring
    • Executing DDL
    • Adding Tables to Replication
    • Switchover
    • Failover
  • 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