PostgreSQL Infrastructure Seminar

Course Code: PSQLIS

Duration: 1 day

PostgreSQL Infrastructure Seminar Course Overview

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.

Seminar Objectives

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

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.

Course Notes

There is no practical content within this PostgreSQL Infrastructure Seminar.

Skills Gained

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

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

Notes:

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.

Requirements

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

Public Scheduled Events

Classroom & Live Virtual Instructor-Led Training

Duration: 1 day

Price: £495.00 exc. VAT

Please contact us to review your requirements and schedule a date.

Print Friendly

Why Choose StayAhead?

Leading indpendent UK training provider, established 1992
Formidable reputation for quality and expertise
Flexibility comes from developing our own independent courses
Course not meeting your needs? Private courses can be tailored specifically for you
Public Schedule & Closed Intructor-Led training - Classroom and Virtual

Live Virtual Classroom

Join live instructor-led classroom training from the comfort of your home or office.
All the convenience and benefits of the classroom experience without the hassle and costs of travel and accommodation.

Live Virtual Classroom

Our Customers Include