top of page

PostgreSQL DB Admin Online Training Course

Course Content

Module 1: Understanding PostgreSQL Database Architecture

  • Exploring the client process, server process, and key components like Postmaster, Background Writer, Checkpoint, Auto Vacuum Launcher, etc.

  • Understanding memory architecture and shared memory concepts

  • Overview of database structures including tables, indexes, views, functions, sequences, tablespaces, and partitioning

 

Module 2: PostgreSQL Installation Methods

  • Understanding PostgreSQL version types

  • Installation on Windows and Linux platforms

 

Module 3: Configuration Overview

  • Understanding PostgreSQL directory layout and configuration files

  • Setting up Linux environment variables for PostgreSQL

 

Module 4: Database Connection & Administration

  • Security and Authentication

  • Configuring Linux firewall, ports, and authentication methods like LDAP and OS Authentication

  • Introduction to PostgreSQL Admin Tools: pgAdmin, DBeaver, and OmniDB

 

Module 5: Managing Databases & Objects

  • Creation of databases, schemas, users, and groups

 

Module 6: Database Start / Stop & Catalog Tables

  • Utilizing pg_ctl and service commands for database management

  • Introduction to PostgreSQL catalog tables and system views

 

Module 7: Managing Storage

  • Overview of tablespaces and storage volumes

 

Module 8: Backup & Recovery

  • Implementation of logical and physical backups

  • Using tools like pg_dump, pg_basebackup, and point-in-time recovery

  • Exploring various backup methods including OS level, tar, and storage level backups

 

Module 9: Database Maintenance & Monitoring

  • Autovacuum, page corruption, and other maintenance tasks

  • Monitoring CPU, memory, disk space, and network usage

 

Module 10: Monitoring Tools Installation & Configuration

  • Installation and configuration of monitoring tools like Prometheus, Grafana, PEM, Pem Agent, Nagios, and AWS CloudWatch

 

Module 11: Data Load & Replication

  • Loading data from .csv files and using PostgreSQL_fdw for data copy

  • Understanding different replication types and configurations

 

Module 12: Database Upgrade

  • Planning and executing database version upgrades

 

Module 13: Database Connection Pooling

  • Implementing connection pooling with PgPool and PgBouncer

 

Module 14: Migration

  • Database and server migration best practices using tools like ora2pg

 

Module 15: Performance Tuning & Optimization

  • Server parameter tuning and query optimization techniques

 

Module 16: DBA Daily Tasks & Automation

  • Automating backup tasks with shell scripts

  • Monitoring database logs, killing sessions, and optimizing performance

bottom of page