PostgreSQL Administration

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

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

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

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

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