Description

Premise Course during October -2025
Venue: London/Birmingham/Manchester
Note: Actual venue and schedule will be shared after registeration
Prerequisite: Anyone willing to learn and Enterprise DB Postgres -Advance Server 14

First Day
1. Introduction
a. Postgre & New Features
b. Concurrency Control
c. Write-Ahead Logging
d. Architectural Overview
e. Limits

2. PostgreSQL System Architecture
a. Architectural Summary
b. Shared Memory
c. Statement Processing
d. Utility Processes
e. Disk Read & Write Buffering
f. Background Writer Cleaning Scan
g. Commit & Checkpoint
h. Physical Database Architecture
i. Data Directory Layout & Installation
j. Page Layout

3. Creating and Managing Databases
a. Object Hierarchy
b. Creating Databases and Schemas
c. Schema Search Path
d. Roles, Users & Groups
e. Security

4. Installation
a. Operating Sysytem User & Permissions
b. Installation & Environment variables
c. Clusters
d. Creating a database cluster
e. Starting & Stopping
f. (pg_ctl) Connect to the server using psql

Second Day
5. Configuration
a. Setting PostgreSQL Parameters
b. Access Control & Connection Settings
c. Security and Authentication
d. Settings Memory
e. Query Planner
f. WAL Settings
g. Background Writer Settings

6. Log Management
a. Where & when to Log

7. PSQL Commands
a. Introduction
b. Conventions
c. Connecting to PostgreSQL
d. PSQL Command Line Parameters
e. PSQL Meta-Commands
f. PSQL SET Parameters
g. Information Commands

8. pgAdmin III
a. Registering a server
b. Viewing and Editing Data
c. Query Tool
d. Databases & Schemas
e. Languages
f. Domains
g. Functions & Sequences
h. Tables
i. Columns
ii. Constraints
iii. Triggers
iv. Indexes
v. Maintenance
vi. Rules
vii. Types
viii. Views
ix. Table spaces
x. Roles

9. Tablespaces
a. Tablespaces and Datafiles
b. Global and Default
c. Advantages of Tablespaces
d. Creating & changing default Tablespaces
e. Usage Example
f. Altering & Dropping Tablespaces

Third Day
10. Security
a. Authentication & Authorization
b. Levels of security
c. config file
d. Users & Object ownership
e. Access control & Parameters

11. Backup and Recovery & Recovery
a. Backup Types
b. SQL & Cluster Dump
c. Offline Backup
d. Continuous Archiving
e. pg_basebackup
f. Point-In Time Recovery
g. pg_upgrade

12. Postgres Data Dictionary
a. The System Catalog Schema
b. System Information views/tables
c. System Information Functions

13. PostgreSQL API Connectivity
a. Third-Party Drivers
b. Configuration of JDBC & ODBC Drivers

Fourth Day
14. Performance Tuning
a. Hardware & Operating System Configuration
b. Server Parameter Tuning
c. Connection Settings
d. Memory Parameters
e. Memory settings for Planner
f. WAL Parameters
g. Explain Plan
h. Explain Example
i. Statistics Collection
j. Indexes
k. Examining Index Usage
l. Tips for Inserting Data
m. Labs

15. Routine Maintenance
a. Table Statistics
b. Updating Planner Statistics
c. Auto Vacuum
d. Preventing Transaction ID Wraparound Failures
e. The Visibility Map
f. Reindexing

16. Replication & Failover
a. Database High Availability
b. Causes of Data Loss & Common Errors
c. High Availability Options
d. Replication
i. Architecture and Setup
ii. Streaming Replication Example

Fifth Day
17. Table Partitioning
a. Partitioning
i. Methods
ii. How and when Partition
iii. Setup
iv. Example
v. Constraint

b. Lab

18. Connection Pooling
a. Install and configure pgpool-II
b. Pgpool II Modes
c. Start & Stop pgpool-II
d. Example

19. Database Monitoring
a. Database Statistics & Collector
b. Database Statistic Tables
c. Operating System Process Monitoring
d. Current Sessions and Locks
e. Log Slow Running Queries
f. Disk Usage
20. Migration
a. Introduction to Migration Toolkit (Oracle/ MSSQL to PostgreSQL)
b. Oracle Compatibility
c. Demo Oracle to PostgreSQL