PostgreSQL Infrastructure Seminar

Who will the Seminar Benefit?

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.

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

Seminar Objectives

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

View Course Contents - DAY 1 PostgreSQL Infrastructure Seminar Training Course

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