PostgreSQL Administration Training Course

Course Contents - DAY 1

Course Introduction

• • Administration and Course Materials
• • Course Structure and Agenda
• • Delegate and Trainer Introductions

Session 1: INSTALLATION AND CONFIGURATION OF POSTGRESQL

• • PostgreSQL Version Numbers
• • PostgreSQL Configuration Recommendations
• • Single Cluster and Database per Server
• • File System Layouts
• • Install PostgreSQL
• • Install PostgreSQL on Linux/UNIX
• • Post-Installation Setup
• • Create a Database Cluster
• • Basic Server Configuration
• • Account Management

Session 2: POSTGRESQL ARCHITECTURE

• • Overview of PostgreSQL Architecture
• • The PostgreSQL Instance
• • How Data is Processed by PostgreSQL
• • PostgreSQL Processes
• • Shared Memory
• • PostgreSQL Directory Structure
• • Manage a PostgreSQL Instance
• • The PostgreSQL Configuration Files
• • Multiversion Concurrency Control
• • The Vacuum utility
• • PostgreSQL WAL

Session 3: POSTGRESQL CLIENT APPLICATIONS

• • Overview of PostgreSQL Client Applications
• • The PostgreSQL Interactive Client Terminal - psql
• • The pgAdmin Tool
• • Client and host based access control
• • Client Connection Problems
• • Authentication Failures
• • Server Startup Failures

Session 4: CREATE AND CONFIGURE A DATABASE

• • Create a New Database using the CREATE DATABASE Command
• • Create a New Database using pgAdmin
• • Start and Stop the Database Server
• • Drop a Database
• • Copy a Database
• • List Databases
• • Obtain Database Object Sizes
• • Initialise a Cluster

PostgreSQL Administration Training Course

Course Contents - DAY 2

Session 5: POSTGRESQL SCHEMAS

• • Overview of the Key Concepts of PostgreSQL Schemas
• • Create and Drop a Schema
• • The Public Schema
• • The Schema Search Path
• • Schemas and Privileges
• • The System Catalog Schema
• • Overview of the Information Schema

Session 6: ROLE MANAGEMENT

• • Overview of PostgreSQL Roles and Privileges
• • Create a User Defined Role
• • Role Attributes
• • Role Membership
• • Assigning Users to Roles
• • Group and User Role Inheritance
• • Removing Roles
• • Troubleshooting and Understanding Role Access

Session 7: FINE GRAINED ACCESS CONTROL USING GRANT

• • Control Database Level Permissions
• • Control Schema Level Permissions
• • Grant Table Level Permissions
• • Define Access Privileges with the GRANT Command
• • Remove Access Privileges with the REVOKE Command
• • Manage Column Rights

Session 8: INDEX CREATION AND MANAGEMENT

• • Index Overview
• • The CREATE INDEX Command
• • Index Types
• • Efficient Usage of PostgreSQL Indexes
• • Index creation
• • B-tree,Hash and BRIN Indexes
• • Single Column Indexes
• • Unique indexes
• • Multicolumn Indexes
• • The INCLUDE clause
• • Partial Indexes
• • Index Based Expressions
• • Creating an index concurrently
• • The REINDEX command
• • List Indexes
• • Manage and Maintain Indexes
• • When Indexes Should be Avoided

Session 9: TRANSACTIONS AND CONCURRENCY

• • Overview of Transaction Processing in PostgreSQL
• • Transaction Properties
• • Transaction Control
• • Multi-version Concurrency Control
• • Concurrency Problems
• • Isolation Levels
• • Implicit Locking
• • Explicit Locking
• • Possible Causes of Lock Contention
• • Deadlocks
• • Advisory Locks
• • Lock Management Parameters

PostgreSQL Administration Training Course

Course Contents - DAY 3

Session 10: POSTGRESQL DATABASES STRUCTURE

• • The PostgreSQL Configuration Files
• • Relocate the Configuration Files
• • Physical Storage and File Layout
• • Overview of Tablespace Usage
• • Table and Row Storage
• • Column Limitations
• • Free Space Map
• • The Visibility Map
• • Index Storage

Session 11: MANAGE TABLESPACES

• • Overview of PostgreSQL Tablespaces
• • Default PostgreSQL Tablespaces
• • Create a Tablespace using SQL Commands and pgAdmin
• • Create a Tablespace in the UNIX Operating System
• • Alter a Tablespace
• • Drop a Tablespace

Session 12: POSTGRESQL LOGGING

• • PostgreSQL Event Log Destinations
• • Configuring syslog,eventlog,stderr and csv format output
• • Configuring What Should be Logged and When

Session 13: POSTGRESQL EXTENSIONS

• • Install the PostgreSQL Contrib Module
• • List the Available Extensions
• • Add an Extension to the postgresql.conf File
• • Create an Extension in a Database
• • Drop an Extension in a Database

PostgreSQL Administration Training Course

Course Contents - DAY 4

Session 14: BACKUP AND RECOVERY OF DATABASES

• • Overview of Backup Methods
• • Export and Import Operations with COPY
• • Backup a Database with Operating System Commands
• • Backup a Database with pg_dump
• • Backup All Databases with pg_dumpall
• • Backup User Credentials
• • Backup Database Object Definitions
• • Overview of Database Restore
• • Restore using psql
• • Restore using pg_restore
• • File system Backup and Recovery

Session 15: POINT-IN-TIME RECOVERY (PITR)

• • Write-ahead Logging and Crash Recovery
• • Checkpoints
• • List the Transaction Logs
• • Transaction Log Optimisation
• • Overview of PITR
• • Setup PITR
• • Continuous Archiving
• • Test Transaction Log Archiving
• • Create a Base Backup using the Low Level API
• • Create a Base Backup using pgBaseBackup
• • The PITR Recovery Process
• • How To Perform a PITR Recovery
• • Recovery Configuration Parameters
• • Timelines
• • Locating the Correct Timestamp
• • Restore Points
• • Clean up the Archived Transaction Logs

PostgreSQL Administration Training Course

Course Contents - DAY 5

Session 16: THE POSTGRESQL QUERY OPTIMIZER

• • Query Optimization
• • Optimization Operations
• • Optimization Decisions
• • Scan Methods
• • Join Methods
• • Join Order
• • Statement Transformation
• • Prepared Statements
• • Query Performance Analysis
• • Detect Slow Queries
• • Use EXPLAIN to optimize Queries and Indexes
• • Execution Plans
• • Query Planner Statistics
• • The ANALYZE command
• • The CREATE STATISTICS command
• • Parameters Affecting Optimization
• • Memory Settings That Affect Performance

Session 17: ROUTINE DATABASE MAINTENANCE

• • Optimize Storage and Manage Clean up with VACUUM
• • Configure VACUUM
• • Configure Autovacuum
• • Cost Based Vacuum Delay
• • Track a VACUUM Process
• • Routine Maintenance Tasks

Session 18: SERVER PERFORMANCE MONITORING AND TUNING

• • Monitor Database Activities
• • System Monitoring & PostgreSQL Monitoring
• • Performance Statistics in the Server Log
• • Statistics Collection Configuration
• • Monitor Database Activity
• • Monitor Table,Index and SQL Statement Activity
• • Monitor Background Writer,WAL and Archiving Activity
• • Progress Reporting
• • Locks

Session 19: POPULATE A DATABASE EFFICIENTLY

• • DISABLE autocommit
• • Configure Variables for Increased Performance
• • Use the COPY Command to Bulk Load Data
• • Drop Indexes and Foreign Keys Temporarily
• • Use the COPY command to Bulk Load Data
• • Temporarily drop indexes and Foreign Key Constraints before a Bulk Load
• • Configure Variables for Increased Performance
• • Temporarily Disable WAL Archival and Streaming Replication
• • Use pg_dump Efficiently