PostgreSQL DBA

PostgreSQL is commonly known as Postgres and it is also open source database. PostgreSQL is a powerful, open source object-relational database system. A database administrator’s (DBA) primary job is to ensure that data is available, protected from loss and corruption, and easily accessible as needed.

Course Overview

What will you learn?

Who should go for this training?
The following professionals can go for this course:

What are the pre-requisites for this Course?

Course Content

  • History of PostgreSQL
  • Major Features
  • PPAS Features
  • Architectural Overview
  • General Database Limits
  • Common Database Object Name
  • OS User & Permissions
  • Installation Options
  • Installation of PostgreSQL
  • StackBuilder
  • Setting Environmental Variables
  • Introduction to PSQL and EDB-PSQL
  • Conventions • Connecting to Database
  • PSQL Command Line Parameters
  • Entering PSQL Commands
  • PSQL Meta-Commands
  • PSQL SET Parameters
  • Information Commands
  • Background Writer Settings
  • Statement Behavior
  • Configuration File Includes
  • Introduction to PEM Client
  • Registering a server
  • Viewing and Editing Data
  • Query Tool
  • Databases
  • Languages
  • Schemas
  • Database Objects
  • Maintenance
  • Tablespaces
  • Roles
  • Data Types
  • Structured Query Language (SQL)
  • Tables and Constraints
  • Manipulating Data using INSERT,UPDATE and DELETE
  • Creating Other Database Objects: Sequences, Views and Domains
  • Table Partitioning
  • Partitioning
  • Partitioning Methods
  • When to Partition
  • Partitioning Setup
  • Partitioning Example
  • Partitioning and Constraint Exclusion
  • Caveats
  • Backup Types
  • SQL Dump Backups
  • SQL Dump Options
  • Handling Large Databases
  • Restoring SQL Dumps
  • Cluster Dump
  • Offline Backups
  • Recovering a Database using Offline
  • Database Maintenance
  • Maintenance Tools
  • Optimizer Statistics
  • Data Fragmentation
  • Routine Vacuuming
  • Vacuuming Commands
  • Preventing Transaction ID Wraparound Failures
  • Vacuum Freeze
  • The Visibility Map
  • Loading flat files
  • Import and export data using COPY
  • Examples of COPY Command
  • Using COPY FREEZE for performance
  • Introduction to EDB*Loader for PPAS
  • Performance Tuning – Overview
  • Performance Monitoring using PEM
  • A Tuning Technique
  • Operating System Considerations
  • Server Parameter Tuning
  • Memory Parameters
  • Temporary File Parameters
  • WAL Parameters
  • Connection Pooling Overview
  • pgpool-II – Features
  • Connection pooling
  • Replication
  • Load balance
  • Limiting exceeding connections
  • Automatic failover
  • pgpool II – Installation and Configuration
  • Configuring pcp.conf, pgpool.conf
  • Setting up pool_hba.conf for Client Authentication (HBA)
  • Starting/Stopping pgpool-II
  • Version Change and Upgrade
  • Need to Upgrade
  • Upgrade Plan
  • Upgrade Using pg_upgrade
  • Upgrading Best Practices
  • Architectural Summary
  • Process & Memory Architecture
  • Utility Processes
  • Connection Request-Response
  • Disk Read Buffering Disk
  • Write Buffering
  • Background Writer Cleaning Scan
  • Commit & Checkpoint
  • Statement Processing
  • Physical Database Architecture
  • Data Directory Layout
  • Installation Directory Layout
  • Page Layout
  • Database Cluster
  • Creating a Database Cluster
  • Starting and Stopping the Server (pg_ctl)
  • Connect to the Server Using psql
  • Setting PostgreSQL Parameters
  • Access Control
  • Connection Settings
  • Security and Authentication Settings
  • Memory Settings
  • Query Planner Settings
  • WAL Settings
  • Object Hierarchy
  • Creating Databases
  • Users Access Control
  • Creating Schemas
  • Schema Search Path
  • Authentication and Authorization
  • Levels of Security
  • pg_hba.conf File
  • Object Ownership
  • Application Access Parameters
  • Protecting Against Injection Attacks with SQL/Protect
  • Source Code Protection for Functions
  • Quoting
  • SQL Functions
  • Concatenation
  • Nested Queries
  • Joins
  • Materialized Views
  • Updatable Views o Indexes
  • Online Directory Backups
  • Continuous Archiving
  • Backup with Low-level API
  • How to use pg_basebackup for Online Backups
  • Point-In-Time Recovery Concepts
  • Recovery Example
  • Backup and Recovery Tool (BART)
  • The System Catalog Schema
  • System Information Tables
  • System Information Functions
  • System Administration Functions
  • System Information Views
  • Oracle-like Dictionaries
  • Statement Processing
  • Common Query Performance Issues
  • SQL Tuning Goals
  • SQL Tuning Steps
  • Identify slow queries
  • Review the query execution plan
  • Optimizer statistics and behavior
  • Restructure SQL statements
  • Indexes
  • Review Final Execution Plan
  • Data Replication
  • Data Replication in PostgreSQL
  • Sync or Async • Log-Shipping Standby Servers
  • Log-Shipping Architecture • Streaming Replication
  • Hot Streaming Architecture • Cascading Replication
  • Setup Replication Using Archive
  • Setup Streaming Replication
  • Prepare the Primary Server
  • Synchronous Streaming Replication Setup
  • Configure Authentication
  • Take a Full Backup of Primary Server
  • Setting up the Standby Server
  • Adding Cascading Replicated Standby Server
  • Monitoring Hot Standby
  • Recovery Control Functions
  • What is Extension Modules
  • Installing Extension Modules
  • Module Index
  • Monitoring
  • Database Monitoring
  • Database Statistics
  • The Statistics Collector
  • Database Statistic Tables
  • Operating System Process Monitoring
  • Current Sessions and Locks
  • Log Slow Running Queries
  • Disk Usage
  • Postgres Enterprise Manager
  • PEM – Features
  • PEM – Architecture

Modes of Training

Classroom Training

Live interactive sessions delivered in our classroom by our expert trainers with real-time scenarios.

Online Training

Learn from anywhere over the internet, joining the live sessions delivered by our expert trainers.

Self-Pace Training

Learn through pre-recorded video sessions delivered by experts at your own pace and timing.

Frequently Asked Questions

Our trainer is an OCP & OCM certified consultant and has a significant amount of experience in working with the technology, having 18yrs of experience.

Once you get registered, our back-end team will share you the details to join the session live over an online portal which can be accessed through a browser.

Each of our live sessions is recorded. In case if you miss any, you can request us to share the link to that particular session.

For practical execution, our trainer/technical team will provide server access details to the student

Yes. We do provide the step-by-step document which you can follow and if required our technical team will assist you.

Live-Online training is where you can have a live session with the trainer and clarify queries parallelly.

Pre-recorded sessions are the recorded videos that will be provided to you that you can see, listen, and learn anytime at ur feasible place. For doubts in the videos, you can mail the trainer regarding the same.

You can contact our support team, or just drop an email to with your queries.

The course material and recorded videos which are provided during the course period. You can download it anytime.

Visit our website regularly to check discounts offers from time to time. However, we provide a discount for single participants & special discounts for 2 or more participants.

* If the request for cancelation is made within 2 days of enrolment for class, 100% refunded.

* If the request made after 2 days, then Refund is made after deduction of the administration fee.