logo

PostgreSQL Course

course overview

Click to View dates & book now

Audience

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.

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

Seminar Objectives

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

Outline

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

Talk to an expert

Thinking about Onsite?

If you need training for 3 or more people, you should ask us about onsite training. Putting aside the obvious location benefit, content can be customised to better meet your business objectives and more can be covered than in a public classroom. Its a cost effective option. One on one training can be delivered too, at reasonable rates.

Submit an enquiry from any page on this site, and let us know you are interested in the requirements box, or simply mention it when we contact you.

All $ prices are in USD unless it’s a NZ or AU date

SPVC = Self Paced Virtual Class

LVC = Live Virtual Class

Please Note: All courses are availaible as Live Virtual Classes

Trusted by over 1/2 million students in 15 countries

Our clients have included prestigious national organisations such as Oxford University Press, multi-national private corporations such as JP Morgan and HSBC, as well as public sector institutions such as the Department of Defence and the Department of Health.