ToC
Program Name: PostgreSQL Administration (DBA)
Objective:
At the end of this training, Participant will be able to:
1. Proper understanding of PostgreSQL DBA
2. They will able to do performance Tuning
3. They will be able to do HA environment
Pre-Requisite:
• Basic experience in database administration
• Intermediate knowledge of RBDMS concepts
• Basic understanding of SQL
• Basic experience with the use of the Linux operating system is helpful
Duration in
Sl. No Day # Module
Hours
1 Introduction & Architecture
PostgreSQL System
2
Architecture
3 Installation
4 Configuration
1 8
Creating and Managing
5
Databases
6 Postgres Data Dictionary
7 Introduction to PSQL
8 Using the GUI : pgAdmin
Routine Database
9
Maintenance Tasks
10 Backup & Recovery
2 8
11 Security
PostgreSQL Partitioning and
12
Tablespaces
13 Monitoring & Statistics
3 8
14 Performance Tuning
Introduction to PostgreSQL
15 High Availability and Basic
Configuration
Advanced Configuration and
16 4 8
Failover Handling
Monitoring, Maintenance,
17 5 8
and Best Practices
ToC
rating system is helpful
Concepts/ Hands-
Sub-Topic
on
• History of PostgreSQL
• Major Features
• Limitations Yes
• Architectural Overview
• PostgreSQL Terminology
• Architectural Summary
• Database Clusters
• Physical Database Architecture
• Data File Architecture
• Database File Layout
• Database Page Layout Yes
• Process Architecture
• Connect Request
• Backend Spawning
• Multi-Version Concurrency Control (MVCC)
• Write-Ahead Logging
• Installation
• Creating a database cluster
• Starting and Stopping the Server
• Lab Exercise - Installation
• Install PostgreSQL from source
Yes
• Create a database cluster
• Start the database server
• Connect to the server using psql
• Stop the database server
• Using PSQL Tool
• Setting PostgreSQL Parameters
• Access Control
• Connection Settings
• Security and Authentication Settings
• Memory Settings
• Query Planner Settings
Yes
• WAL Settings
• Log Management
• Background Writer Settings
• Statement Behavior
• Vacuum Cost Settings
• Autovacuum Settings
• Object Hierarchy
• Creating Databases
• Creating Schemas
• Schema Search Path
Yes
• Roles
• Users
• Groups
• Access Control
• The System Catalog Schema
• System Information views/tables Yes
• System Information Functions
• Introduction
• Conventions
• Connecting to PostgreSQL
• PSQL Command Line Parameters
Yes
• Entering PSQL Commands
• PSQL Meta-Commands
• PSQL SET Parameters
• Information Commands
Using the GUI : pgAdmin
• Explain and Explain Analyze
• Table Statistics
• Log Management
• Vacuuming
• Scheduling
• Auto Vacuum Yes
• Recovering Disk Space
• Updating Planner Statistics
• Preventing Transaction ID Wraparound
• Failures
• Routine Reindexing
• Dump & Restore
• File System Backup - copying DATA folder
• Continuous Archiving
Yes
• pg_basebackup, pg_upgrade
• PITR - Point in Time Recovery
• Recovery file parameters
• Levels of security: pg_conf, schemas and
• users and table level.
• USERS; GROUPS; ROLES
• Object Ownership
• Access control
• Application Access Yes
• pg_hba.conf
• Schemas and Search Paths
• Lab to create USERS, SCHEMAS, ROLES,
• Alter SEARCH_PATH, GRANT and REVOKE
• Privileges
• Partition Methods
• Partition Setup Yes
• Table space Management
• Database Logs
• OS Process Monitoring
• Statistics Collector
• Statistics Parameters
Yes
• Statistics Views and Functions
• Timing
• Explain and Explain Analyze
• Forcing Query Plan Selection
• Helping the Optimizer
• Using Views
• Creating Rules with Views
Yes
• Indexing
• Constraints
• Clustering Rows
• Overview of PostgreSQL High Availability
• Introduction to pgpool
• Understanding PostgreSQL Replication Methods
• Setting up a PostgreSQL Cluster with Streaming
Replication
Yes
• Introduction to pgpool Installation and Configuration
• Configuring pgpool for Load Balancing and Connection
Pooling
• Understanding Failover and Automated Switchover in
PostgreSQL
• Implementing Automated Failover with pgpool
• Configuring Watchdog for Automatic Failover
• Handling Failover Scenarios: Testing and
Troubleshooting Yes
• Scaling PostgreSQL with pgpool: Read/Write Splitting
• Performance Tuning and Optimization in pgpool
Database Migration Concepts
Migration activity from Oracle to PostgreSQL using
Ora2pg tool
• Monitoring PostgreSQL and pgpool Using Built-in Tools
• Setting up External Monitoring Tools for PostgreSQL and
pgpool
• Performing Routine Maintenance Tasks in pgpool
• Backup and Restore Strategies in a High Availability
Setup
• Disaster Recovery Planning and Procedures
• Best Practices for PostgreSQL High Availability with Yes
pgpool
Overview on Multi-Tenancy Architecture in PostgreSQL"
Ex: How to design a multi tenancy database for
micro services applications
how to containerization of postgressql
how to deploy the containzeration environment and how
to leverage in micro services architecture.
Expected Learning Outcome
Good Understanding of
PostgreSQL Database
Good Understanding of Storage
Management
Good Understanding of Security
and Backup & Restore Concepts
Good Understanding of
Performance Tuning
Good Understanding of HA & DR
concepts
Database Monitoring Tools and
others
