Duration
2 days
Introduction
- EDB Postgres Platform
- PostgreSQL Facts
- Facts about EDB Postgres Advanced Server
- PostgreSQL Installation
- Prepare a Sample Database
Database Monitoring
- Database Statistics
- The Statistics Collector
- Database Statistic Tables
- Postgres Enterprise Manager (PEM)
- PEM – Features
- PEM – Architecture
- Monitoring Utilities
SQL Tuning
- Statement Processing
- Common Query Performance Issues
- SQL Tuning Goals
- SQL Tuning Steps
- Identify slow queries
- Review the query execution plan
- Optimizer statistics and behaviour
- Restructure SQL statements
- Indexes
Performance Tuning
- Performance Tuning - Overview
- Performance Monitoring using PEM
- A Tuning Technique
- Operating System Considerations
- Server Parameter Tuning
- Loading a Table into Memory
- Best Practices for Inserting Large Amount of Data
- Non-Durable Settings
- Tuning PostgreSQL Using PEM
Performance Benchmarking
- Database Benchmarking
- Benchmarking Tools
- pgbench
- Preparing a database for pgbench
- Running a pgbench test
- Default Script
Extensions
- What are Extension modules?
- Installing Extension Modules
- Add Extension to a Database
- Extensions List
Table Partitioning
- Partitioning
- Partitioning Methods
- When to Partition
- Paritioning Methods
- Partitioning Setup
- Attach and Detach Partitions
- Partition Pruning
- Partitioned Table Limitations
Connection Pooling
- Connection Pooling Overview
- pgpool-II - Features
- pgpool II - Installation and Configuration
- Starting/Stopping pgpool-II
- pgbouncer – Features
- pgbouncer – Installation and Configuration
- Starting/Stopping pgbouncer
High Availability
- Data Replication
- Data Replication in PostgreSQL
- Streaming Replication and Architecture
- Synchronous, Asynchronous and Cascading Replication Options
- Logical Replication
- Setup Streaming Replication
- Setup Cascaded Replication
- Monitoring and Recovery Control
- High Availability Setup Using EDB Failover Manager
Foreign Data Wrappers
- Components
- Types of FDWs
- Setup FDWs
- Example - postgres_fdw
- Example - file_fdw
- Access Control
Database Security
- Data Security Requirements
- Protection Plan
- PostgreSQL Security Options
- Added Security in EDB Postgres Advanced Server
- Host Based Access Control
- SSL, Passwords, LDAP
- Authorized Access
- Row Level Security
- Data Encryption
- General Recommendations
Upgrading Best Practices
- Version Change and Upgrade
- Need to Upgrade
- Upgrade Plan
- Upgrade Using pg_upgrade
- Upgrading Best Practices
Programme scope, delivery arrangements and suitability are confirmed during enquiry.