PostgreSQL Database Administrator
DBA Training mainly focus on Administration part of the databases like Installations, database creation, user creation, backup & recoveries, minor &
major upgrades, Performance tuning.
Course schedule (DBA)
| Week | Topic | Test |
|---|---|---|
| Week 1 | Linux / SQL Basics | Test-1 |
| Week 2 | PostgreSQL Architecture / Major features / Data Types | Test-2 |
| Week 3 | PostgreSQL Installation / Basic Activities | Test-3 |
| Week 4 | PostgreSQL Tools | Test-4 |
| Week 5 | Backup & Restore Strategies | Test-5 |
| Week 6 | PostgreSQL Maintenance Activities | Test-6 |
| Week 7 | PostgreSQL Upgrade / Replication Methods and Usage | Test-7 |
| Week 8 | Performance Tuning | Test-8 |
| Week 9 | Migration Strategies / Extensions | Test-9 |
Course Content
PostgreSQL Database Architecture:
- General Architecture
- Memory Based Architecture
- Process Based Architecture
PostgreSQL Major Features PostgreSQL Data Types
PostgreSQL Installation Types:
- Source Code Installation
- RPM Installation
- dnf/yum installation
PostgreSQL Basic Activities
- PostgreSQL Cluster initialization
- DATA directory Explanation
- Starting and Stopping PostgreSQL cluster Services
- Automatic Startup / Shutdown Common Issues &
Troubleshooting - Installation Lab setup for PostgreSQL configuration
- Host Based Authentication and Access
Management - Benefits of Vacuuming
- Recovering Disk Space
- Updating Planner statistics
- Transaction ID explanation
- Transactions & concurrency Control
Basic Activities
- MVCC architecture
- Routine DBA Tasks and
- Best Practices
- Audit Log Management
- Query Analysis
- Monitoring and Statistics
- Database Logs
- OS Process Monitoring
- PostgreSQL Statistics
- Collector Statistics
Views - Statistics Functions
- HW Configuration
- Transaction Logs
- Tablespaces
- Partitioning
- Data Import and Export
PostgreSQL Tools Overview
- PG Badger
- PG Pool
- PG Admin
- pgbench
Logical Backup & Restore:
- PG_DUMP
- PG_DUMPALL
- PG_RESTORE
- PSQL RESTORE
Physical Backup and Restore & Recovery:
- PG_BASEBACKUP
- DATABASE RESTORE AND RECOVERY
- POINT-IN-TIME
- RECOVERY
- PG_RECIEVEWAL
- PGBARMAN
- PGBACKREST
- INCREMENTAL BACKUP
PostgreSQL Maintenance Activities
- VACUUM
- ANALYZE
- VACUUM FULL
- VACUMM FREEZE
- REINDEX
Database Upgrade
- PostgreSQL contrib module
- installation
- PostgreSQL database
- upgrade Methods
- Major Upgrade
- Minor Upgrade
- Upgrade Roll Back
Replication Methods and usage
- PostgreSQL Streaming Replication
- Understanding recovery.conf
- Understanding Initializing Streaming Replication
- Recovery Options
- Standby Promotion
- Cascading Replication
- Replication Slots
- Synchronous Replication
- Replication Type Selection
- Cascading Replication
- Replication Slots
- Failover Slots
- Synchronous Replication
- Switchover Scenarios
- Failover Scenarios
- PostgreSQL High Availability
- Failing Back Scenarios
- Standby Conflicts
- Standby Promotion
- WAL Shipping process
- Replication Overview
- Replication Configuration & Setup
Replication Methods and usage
- Replication Monitoring
- pg_rewind usage
- Inheritance and Partitioning
- PostgreSQL self-Partitioning
- PostgreSQL Trigger Based Partitioning
Performance Tuning
- Operating System level Performance Tuning
- Query Level Performance Tuning
- Database Level Performance Tuning
- User Level Performance Tuning
- Object Level Performance Tuning
Oracle to PostgreSQL Migration
- Ways of Migration
- Types of Migration
- Tools used for migration
- Oracle to PostgreSQL Migration Demo
Postgres Extension
How to install Extensions
Important Extensions