Duration
5 days
Course Outline
Introduction and Architectural Overview
- EDB Postgres Platform
- History of PostgreSQL
- Major Features
- EDB Postgres Advanced Server Features
- Architectural Overview
- General Database Limits
- Common Database Object Names
System Architecture
- Architectural Summary
- Process & Memory Architecture
- Utility Processes
- Connection Request-Response
- Disk Read Buffering
- Disk Write Buffering
- Background Writer Cleaning Scan
- Commit & Checkpoint
- Statement Processing
- Physical Database Architecture
- Data Directory Layout
- Installation Directory Layout
- Page Layout
PostgreSQL Installation
- OS User and Permissions
- Installation Options
- Installation of PostgreSQL
- Initializing Database Instance
- Database Cluster Defaults
- Starting Database Instance
- Connecting to a Database
- Setting Environmental Variables
Database Clusters
- Creating a Database Cluster
- Starting and Stopping the Server (pg_ctl)
- Connect to the Server Using psql
Configuration
- Setting PostgreSQL Parameters
- Access Control
- Connection Settings
- Security and Authentication Settings
- Memory Settings
- Query Planner Settings
- WAL Settings
- Log Management
- Background Writer Settings
- Statement Behavior
- Parallel Query Scan Settings
- Parallel Maintenance Settings
- Vacuum Cost Settings
- Autovacuum Settings
- Just-in-Time Compilation
- Configuration File Includes
Creating and Managing Databases
- Object Hierarchy
- Creating Databases
- Users and Roles
- Access Control
- Creating Schemas
- Schema Search Path
User Tools - Command Line Interfaces
- Introduction to psql
- Conventions
- Connecting to Database
- psql Command Line Parameters
- Entering psql Commands
- psql Meta-Commands
- psql SET Parameters
- Conditional Commands
- Information Commands
GUI Tools
- Introduction to pgAdmin 4
- Installing pgAdmin 4 on Linux
- Registering a server
- Viewing and Editing Data
- Query Tool
- Databases
- Languages
- Schemas
- Database Objects
- Maintenance
- Tablespaces
- Roles
- Introduction to PEM Web Client
Security
- Authentication and Authorization
- Levels of Security
- pg_hba.conf File
- Row Level Security
- Object Ownership
- Application Access Parameters
- Protecting Against Injection Attacks with SQL/Protect
- Source Code Protection for Functions
SQL Primer
- Data Types
- Structured Query Language (SQL)
- DDL, DML and DCL Statements
- Transaction Control Statements
- Tables and Constraints
- Views and Materialized Views
- Sequences
- Domains
- SQL Joins
- Using SQL Functions
- SQL Format Functions
- Quoting in PostgreSQL
- Indexes
Backup and Recovery & Point-in Time Recovery
- Backup Types
- Database SQL Dumps
- Restoring SQL Dumps
- Offline Physical Backups
- Continuous Archiving
- Online Physical Backups Using pg_basebackup
- Point-in-time Recovery
- Recovery Settings
Routine Maintenance
- Database Maintenance
- Maintenance Tools
- Optimizer Statistics
- Data Fragmentation
- Routine Vacuuming
- Vacuuming Commands
- Preventing Transaction ID Wraparound Failures
- Vacuum Freeze
- The Visibility Map
- Vacuumdb
- Autovacuuming
- Per Table Thresholds
- Routine Reindexing
- CLUSTER
Data Dictionary
- The System Catalog Schema
- System Information Tables
- System Information Functions
- System Administration Functions
- System Information Views
Moving Data
- Loading flat files
- Import and export data using COPY
- Examples of COPY Command
- Using COPY FREEZE for performance
- Introduction to EDB*Loader for EDB Postgres Advanced Server
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 behavior
- 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
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
Connection Pooling
- Connection Pooling Overview
- pgpool-II - Features
- pgpool II - Installation and Configuration
- Starting/Stopping pgpool-II
Table Partitioning
- Partitioning
- Partitioning Methods
- When to Partition
- Partitioning Methods
- Partitioning Setup
- Attach and Detach Partitions
- Partition Pruning
- Partitioned Table Limitations
Extension Modules
- What are Extension modules?
- Installing Extension Modules
- Add Extension to a Database
- Extensions List
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.