PostgreSQL Infrastructure Seminar

Many companies and government organisations looking for open source RDBMS have either moved or are considering moving databases to PostgreSQL. Not only does Postgres have a wealth of database functionality, it's the nearest like-for-like featured alternative to Oracle.

Whilst Oracle, MySQL and Microsoft SQL Server have a big market lead, Postgres has outperformed them all in terms of rising popularity in the last few years. For those organisations who require a commercially supported platform, then this is offered by the well-established organisations such as 2nd Quadrant and EnterpriseDB.

Different cloud providers offer hosted systems. These include Amazon AWS and Aurora Cloud, Microsoft's Azure Database for PostgreSQL and Google's Cloud SQL for PostgreSQL. To further understand why PostgreSQL is the open source database of choice for many businesses attend StayAhead's PostgreSQL Infrastructure Seminar. The seminar is designed to give decision makers, professional database administrators and architects an insight of the wealth of features and functionality of PostgreSQL and how these could be leveraged within their organisation.

Slides and demonstrations will be used where appropriate throughout the seminar to give the delegates an insight of the topics discussed. Delegates are expected to take part in discussion and question and answer sessions at the end of each section.

The delegate will learn and acquire skills as follows:

• PostgreSQL multi server architecture
• PostgreSQL configuration recommendations
• PostgreSQL versions and recommended patching cycles
• PostgreSQL interfaces, client applications and client tools
• The available PostgreSQL extensions
• Configuration files and best practices
• Security features available in PostgreSQL
• Security best practises
• Server maintenance, monitoring and tuning tools
• Tools available to backup and recover a cluster
• High availability of PostgreSQL
• PostgreSQL replication capabilities
• How to leverage cloud-based PostgreSQL

This PostgreSQL Infrastructure Seminar is designed for professional database administrators, consultants, architects and decision makers who need to gain a broad-based understanding of the features and functionality that are available to manage PostgreSQL databases.

This seminar aims to provide the delegate with a basic knowledge of the capabilities of the PostgreSQL database.

This course assumes a good understanding of database management in general.

There is no practical content within this PostgreSQL Infrastructure Seminar.

• Course technical content is subject to change without notice.
• Course content is structured as sessions, this does not strictly map to course timings. Concepts, content and practicals often span sessions.

Course Contents - DAY 1

Course Introduction

• Seminar Structure and Agenda
• Delegate and Trainer Introductions

Session 1: POSTGRESQL ARCHITECTURE OVERVIEW

• PostgreSQL Architectural Overview
• Database Design Alternatives
• Postgres Multitenancy Options:
• Using multiple databases in a single Postgres cluster (i.e. instance)
• Using multiple schemas in a single Postgres database
• Using shared tables in a single schema in a single Postgres database
• File System Layouts
• PostgreSQL Directory Structure
• PostgreSQL Processes
• PostgreSQL Instance Management

Session 2: POSTGRESQL VERSIONS, INTERFACES AND TOOLS

• PostgreSQL Version Numbers
• PostgreSQL Recommended Patching Cycles
• PostgreSQL Upgrade Overview
• Overview of PostgreSQL Client Applications
• The psql and PL/pgSQL Interfaces
• Tools to Assist with Administration:
• pgAdmin
• OmniDB
• DBeaver
• Navicat
• Extensions available for PostgreSQL

Session 3: POSTGRESQL DATABASE STRUCTURE OVERVIEW

• PostgreSQL Configuration Recommendations
• The PostgreSQL Configuration Files
• Physical Storage and File Layout
• Overview of Tablespace Usage
• Table and Row Storage
• Temporary Undo
• Index Storage
• Overview of PostgreSQL Tablespaces

Session 4: POSTGRESQL LOGGING OVERVIEW

• The Principles and Usages of Logging
• PostgreSQL Log Destinations
• Logging Parameters
• Error Reporting and Logging
• What Should be Logged and When

Session 5: POSTGRESQL SECURITY FEATURES

• Security Best Practises
• Client Authentication Control
• Server Security Configuration
• Security Tools
• User and Role Management
• Secure TCP/IP Access and Connections
• Data Encryption with the pgcrypto Extension
• Audit with the pgAudit Extension
• Set Row Level Security Policies

Session 6: MONITORING SERVER PERFORMANCE & MAINTENANCE

• Multiversion Concurrency Control
• VACUUM, Freezing and Avoiding Wraparound
• Suitable Index Usage
• Join Planning
• EXPLAIN and SQL Execution
• Workload Analysis
• Scale PostgreSQL using Table Partitioning
• Server Monitoring and Tuning

Session 7: OVERVIEW OF PHYSICAL BACKUP AND RECOVERY OF DATABASES

• Overview of PostgreSQL Backup and Recovery Methods
• Export and Import Operations with COPY
• Backup a Database with Operating System Commands
• Backup a Database with pg_dump and pg_dumpall
• Use 3rd Party Backup Tools
• Barman
• pg_Back_Rest
• Restore using psql and pg_restore
• Overview of Point in Time Recovery
• Continuous Archiving
• Overview of PostgreSQL Write-ahead Logs (WAL)
• Create Base Backups with pg_basebackup
• Server Checkpoints
• Replay of Transaction Logs

Session 8: HIGH AVAILABILITY OVERVIEW

• PostgreSQL Replication Architecture
• Physical Streaming Replication and Hot Standby Databases
• Switchover, Failover and Failback
• Managing Replication and Failover with repmgr
• Logical Replication Overview
• Online Upgrade using Logical Replication
• Use Bi-Directional Replication with BDR

Session 9: CLOUD BASED POSTGRESQL

• Benefits of a Cloud Based PostgreSQL Database
• Amazon RDS for PostgreSQL
• Google Cloud SQL for PostgreSQL
• Microsoft Azure for PostgreSQL
• EnterpriseDB Cloud Database