Duration
4 days
Course Objective
All students will learn how to Install, Perform backup and disaster recovery, Work with inheritance, partitioning, and Tablespaces, Manage free space, Secure PostgreSQL and Create high availability and high performance solutions.
Course Outline
PostgreSQL Introduction & Architecture
Introduction and History
The rise of PostgreSQL Popularity
Getting support for PostgreSQL
The PostgreSQL community
Getting started with PostgreSQL
PostgreSQL Installation
Installation options: Binary vs. Source
Binary Installation
Source Installation
Discuss PostgreSQL distributions
Discuss cloud and hosted PostgreSQL
The PostgreSQL Cluster
High-level PostgreSQL architecture
Definition of a cluster
Common PostgreSQL executables
Initializing a PostgreSQL Cluster
Cluster configuration files
Starting and Stopping a PostgreSQL Cluster
Creating Databases and Users
Templates
Creating PostgreSQL Databases
Users, Groups, and Roles
PostgreSQL role inheritance
Security – Host-Based Authentication
The pg_hba.conf file
PostgreSQL Configuration
The postgresql.conf file
Making persistent configuration changes
Introduction to PSQL
Connecting the PSQL client
PSQL command line parameters
PSQL l meta Commands
PSQL input and output
PostgreSQL Database Objects
PostgreSQL data type overview
Discussion of PostgreSQL schemas
Object names and search path
Creating, altering, and dropping tables
Creating and dropping views
Creating, refreshing, dropping materialized views
Notes on COMMIT
Notes on case sensitivity
Copying and Importing Data with PostgreSQL
PostgreSQL data import and export methods
COPY
Copy PSQL meta command
pgAdmin
Overview of basic pgAdmin features and utilities
Demonstration of pgAdmin dashboards
PostgreSQL Encryption
Discussion of encryption at rest
Discussion of encryption in transit
The future of PostgreSQL encryption
Basic SSL server setup and configuration
PostgreSQL Backup and Recovery
PostgreSQL logical backup
PostgreSQL physical backup
Using pg_dump
Using pg_dumpall
Recovery from logical backups
Using pg_restore
Performing an offline physical backup
Using pg_basebackup
Recovery from a physical backup
PostgreSQL Streaming Replication
Overview
Discussion of PostgreSQL replication options
Setup a hot-standby configuration
Monitoring
Replication slots
PostgreSQL Internals – System Catalogs and Cluster Settings
Overview of system catalogs
Overview of system views
Modifying cluster settings
Logging and Monitoring Tools
Logging SQL queries
System log configuration and management
Installing and using pg_stat_statements
PostgreSQL stats views
Query analysis with EXPLAIN
Visualizing EXPLAIN output
Upgrading PostgreSQL
Minor version upgrades
Major version upgrades
Discussion of data conversion
pg_upgrade
Logical Replication
Features of PostgreSQL native logical replication
Setup a logical replication configuration
Continuous Archiving and Point In Time Recovery (PITR)
Configuring PostgreSQL Point In Time Recovery
Recovering from a PITR backup
Recovering to a specific point in time
Discussion of popular backup utilities
Routine DBA Tasks and Best Practices
Log Management
Query analysis
Routine Vacuuming
Recovering disk space
Managing Planner statistics
REINDEX
Performance Tuning
OS Tuning
Hardware configuration
Transaction logs
Tablespaces and Partitioning
Checkpoint tuning
Query tuning
Programme scope, delivery arrangements and suitability are confirmed during enquiry.