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

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 


Start Date Options Spaces  
01 Mar 2021
StayAhead Virtual Courses available 
Spaces Book Now 
07 May 2021
StayAhead Virtual Courses available 
Spaces Book Now 
05 Jul 2021
StayAhead Virtual Courses available 
Spaces Book Now 
31 Aug 2021
StayAhead Virtual Courses available 
Spaces Book Now 
01 Nov 2021
StayAhead Virtual Courses available 
Spaces Book Now 
 

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.
 
 



Our Customers Include

 
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
missing image
 
 



Our Course Curriculum

 
 
 
 
Facebook LinkedIn Twitter Instagram E-Mail
 
+44 (0)20 7600 6116
Enquiries@StayAhead.com
Copyright © 2021 StayAhead Training Ltd
Cookies   /   Privacy Policy