Chennai :

9710033388 / 9884062253

Tanjore :

+919655141145

PostgreSQL Course Syllabus

Duration : 3 Months
Category :POSTGRESQL, SQL, DATABASE

The objective of the PostgreSQL course is to provide learners with comprehensive, hands-on expertise in installing, configuring, and managing PostgreSQL in real-world production environments. This course is designed to build strong foundational and administrative skills, covering database architecture, schema design and maintenance, performance tuning, backup & recovery, scheduling, upgrades, migrations, and connection pooling mechanisms.

Learners will gain practical experience working with PostgreSQL tools and utilities, enabling them to optimize database performance, automate administrative tasks, plan and execute version upgrades, and ensure smooth data migrations across environments. The training also emphasizes connection pooling strategies to support scalable application workloads and improve overall system efficiency.

By the end of the course, learners will be able to deploy and maintain robust PostgreSQL instances, implement industry best practices, troubleshoot performance issues, and confidently support enterprise-grade database operations in roles such as PostgreSQL DBA, Database Engineer, and Backend Database Support Specialist.

  • Database Basics and Database Types
  • PostgreSQL: Advantages, Usage
  • PostgreSQL as an Open Source Database
  • PostgreSQL: History and Versions
  • PostgreSQL: Real-time Usage
  • PostgreSQL Installation on LINUX OS
  • bin, data, and PATH Concepts
  • Command Line Tools
  • Post Installation: Environment Variables, Path, bin, and PGDATA Variables
  • PostgreSQL Server Architecture
  • Server Process and Client Process
  • Postgres Program: Internal Process
  • Postmaster Process and Forks
  • Background Process, Connections
  • Utility Processes: Bgwriter, WAL
  • Checkpointer and StatsCollector
  • Log Writer, Autovacuum Utilities
  • Memory Segments and Usage
  • Shared Buffer and WAL Buffer
  • Dirty Data and Background Writer
  • Background Process, Backend Process
  • initdb Utility Introduction
  • Creating Cluster using initdb
  • Changing Port Number of a New Cluster
  • Default Databases in Postgres: postgres, template0, template1
  • Database Creation from Bash
  • Database Creation from DB Level
  • Default Schema: public
  • Creating Schema
  • Creating Table Inside Schema
  • Setting Default Schema
  • Moving Tables Across Schemas
  • Tablespaces and Real-time Advantages
  • pg_default, pg_global Tablespaces
  • Creating and Modifying Tablespaces
  • Creating Tables Inside Tablespace
  • User Creation
  • Assigning Privileges to User
  • Dropping User
  • Assigning Privileges to Role
  • Assigning Role to User
  • Privileges Management
  • Transactions and Lock Management
  • Table Level Locks, Row Level Locks
  • Query Blocking and Deadlocks
  • Lock Queue, Lock Management Options
  • Page Locks, Shared and Exclusive Locks
  • pg_stat_activity
  • pg_cancel_backend,pg_terminate_backend
  • Vacuum, Vacuum Full, Freeze, Analyze
  • Partitioning
  • pg_badger – Log Analysis Tool
  • pg_cron Introduction
  • Installing pg_cron
  • Configuring pg_cron Parameters
  • Scheduling Jobs
  • DBlink
  • Foreign Data Wrapper (FDW)
  • Backups: Types, Levels in Postgres
  • Logical and Physical Backups
  • Backup Tools: pg_dump, pg_dumpall
  • Online and Offline Backups
  • Logical Backups: Single Table, Multiple Tables, Single Database
  • Logical Backup Options and Usage
  • pg_backrest – Third-Party Backup Tool
  • wal_level and archive_mode Options
  • archive_command with File Formats
  • WAL Archival Process
  • Point-In-Time Recovery (PITR)
  • pg_switch_wal, pg_basebackup
  • Physical Backups and Continuous Archival
  • pg_upgrade Utility
  • Checking Consistency
  • Deleting Old Cluster
  • ora2pg Introduction
  • Downloading Required Files
  • Setting Migration Parameters
  • Migrating Data
  • PostgreSQL Replication and Advantages
  • WAL Sender, WAL Receiver, Startup
  • pg_hba.conf Entries for Replication Host
  • pg_basebackup Utility
  • Single Master Replication Setup
  • recovery.conf File and Configurations
  • primary_conninfo and standby_mode
  • Replication Setup Validations
  • pg_stat_replication Monitoring
  • PgBouncer Introduction
  • Installing PgBouncer
  • Configuring PgBouncer
  • pg_hint_plan
  • pg_audit
  • pg_stat_statements
  • barman

Alexzender Alex

CSE Teacher

Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusa dolore mque laudantium totam rem aperiam eaqipsa quae ab illo inventore veritatvolup tatem quia voluptas sit aspernatur aut odit aut fugit sed quia conseque.

Nathaniel Bustos

Manager

Latanya Kinard

Web Designer

Where Our Alumini Works

CallUs
WhatsApp
Instagram
Facebook
YouTube