Skip to main content
  • 4.7
  • 5.0

Official Learning Partner

Course Highlights

PostgreSQL DBA Training includes basic to advanced concepts on Real-time Administration Activities including Query Optimizations, Routine Maintenance, Management Operations including Backups, Restores, Security, Encryptions, High Availability, Disaster Recovery, Troubleshooting, Migrations, Upgrades and more….

Training Highlights:

  • In-depth Tuning
  • Migration Issues
  • In-Memory Data
  • DB Documenation
  • Schema Migrations
  • Stretch Database
  • Memory Tables
  • Temporal Tables
  • App Connections
  • Excel Pivots
  • CTEs and TVPs
  • Excel Imports
  • Power Shell
  • Azure CLI
Play Video

Course Content

PostgreSQL DBA Training
Course Contents:

Brochure DownloadRegister Now

Part 1: PostgreSQL DBA Training

Module 1

Chapter 1: Introduction & Installation

  • Database Basics and Database Types
  • OLTP, DWH, OLAP and HTAP
  • RDBMS : Need and Importance
  • PostgreSQL : Advantages, Usage
  • PostgreSQL as a Open Source
  • PostgreSQL : History and Versions
  • PostgreSQL : Real-time Usage
  • PostgreSQL : Windows, LINUX OS
  • PostgreSQL Installation in Windows
  • bin, data and PATH Concepts
  • PpgAdmin4, Command Line Tools
  • Post Installation, Environment Variables
  • Path, bin and PGDATA Variables
  • Installation Varification Options

Chapter 2: SQL Basics, Linux Installation

  • Using PSShell and pgAdmin Tools
  • Creating and Connecting Databases
  • DDL, DML and SELECT Statements
  • Creating Tables and Data Inserts
  • IN, NOT IN, AND, BETWEEN Operators
  • DISTINCT, ORDER BY, LIKE, NOT LIKE
  • Order By, Basic Level Sub Queries
  • LIMIT, OFFSET and Column Aliases
  • UPDATE, DELETE & SELECT INTO
  • TRUNCATE, ALTER and DROP
  • PostgreSQL Installation in LINUX
  • PostgreSQL in RHEL & Ubuntu
  • sudo & yum For RPM Packages
  • apt install, apt update, systemctl

Chapter 3: PSQL Tool & Transactions

  • Using PSQL Shell Command Line
  • Creating Databases, Tables in PSQL
  • Connecting to Databases, Table List
  • Creating Tables with Schemas
  • Working with Default “Public” Schema
  • Aliasing : Table Level, Column Level
  • Table Imports and COPY Statement
  • Transactions : ACID Properties
  • Transaction Types and Options
  • BEGIN TRANSACTION and BEGIN
  • COMMIT and ROLLBACK Concepts
  • COMMIT WORK Statement
  • Verifying Transactions in Database
  • Advantages of Commit & Rollback

Chapter 4: Constraints, Keys

  • Constraints and Keys in PostGreSQL
  • Null and Not Null Constraints
  • Primary Key and Real-time Usage
  • Unique Key and Real-time Usage
  • Foreign Key Constraint, Relations
  • Entity Relationship (ER) Models
  • Check Constraint and Conditions
  • Identity Column and Seed, Increment
  • GENERATED ALWAYS AS IDENTITY
  • GENERATED BY DEFAULT AS IDENTITY
  • Custom Start and Increment Values
  • Composite Keys and Self References
  • Adding Keys to Existing Tables
  • SERIAL Data Type and Sequence

Chapter 5: Join Queries in PostgreSQL

  • Joins : Purpose and Types of Joins
  • INNER Join and Matching Data
  • OUTER Join Types in PostgreSQL
  • Left Outer Join and Real-time Usage
  • Right Outer Join and Real-time Usage
  • Full Outer Join and Real-time Usage
  • Cross Join and Comma (,) in Tables
  • Natural Joins with Common Columns
  • Natural Inner / Left / Right Outer Joins
  • USING Keyword for Table Joins
  • Self Joins and Table Aliasing in Joins
  • Hierarchial Data Reporting in Joins

Chapter 6: Functions and Triggers

  • PostgreSQL PL/pgSQL Programming
  • Stored Procedures Creation, Use
  • Functions and Types in PostgreSQL
  • Overloading Concepts in Functions
  • Dynamic Data Retreival in PSQL
  • Internal Functions in PostgreSQL
  • Triggers and Callback Functions
  • Row Level, Statement Level Triggers
  • BEFORE and AFTER Triggers in PSQL
  • NEW and OLD System Tables
  • PLPGSQL and Trigger Functions
  • Triggers Rename and Retreival

Module 2

Chapter 7: Server Architecture

  • 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

Chapter 8: Database Architecture

  • Installation Directory Layout
  • bin, data, doc, include, installer
  • scripts, share, pgadmin directories
  • Database Directory Layout
  • Base, Log, commit_ts, mem
  • notify, pg_ident.conf, pg_xact
  • Base Directory Layout, OID
  • Default Databases in Postgres
  • postgres, template0, template1
  • Tablespaces, Real-time Advantages
  • pg_default, pg_global tablespaces
  • Create, Modify, Audit Tablespaces

Chapter 9: Clusters, Inheritance, COPY

  • PostgreSQL Clusters and Real-time Use
  • Data Directory and Data Area
  • initdb command Usage, Executions
  • Default Databases and their Usage
  • Start \ Stop Cluster and Operations
  • postgresql.conf , pg_ctl status
  • Shutdown: Fast, Smart, Linux
  • Reload and Restart Clusters
  • pg_controldata, Logical Structure
  • Table Inheritance : Auto DML
  • Creating Master, Child Tables
  • Create Table as Table Option
  • Create Table as Table with Nodata

Chapter 10: Security Management – 1

  • PostgreSQL Security Management
  • Users; Roles and Logins For Security
  • Server Level, Database Level Security
  • Schema Level, Table, Object Security
  • Column Level, Row Level Security
  • PSQL Client Tool and its Usage
  • Postgres Super User, Connection Test
  • Public Schema and REVOKE Options
  • Revoke and Revoke All from Public
  • Role Management: Users, Logins
  • search_path, sequence, defaults
  • Creating Logins without Passwords
  • pgAdmin Tool, Alter Role; ReadOnly

Chapter 11: Security Management – 2

  • Column Level Security Implementation
  • Column Security with User Views
  • Column Security with Permissions
  • Column Encryptions, pgp_sym Functions
  • pgp_sym_encrypt, pgp_sym_decrypt
  • Row Level Security (RLS) & Policies
  • ENABLE and DISABLE RLS Options
  • Alter User, bypassrls & All PUBLIC
  • pg_hba.conf: Usage, Record Format
  • trust, password, md5, ident, peer
  • SHOW Command, Service Restarts
  • LINUX Users & Management Options
  • User DBs, Linux User Connections

Chapter 12: Backup & Restore – 1

  • Backups: Types, Levels in Postgres
  • Logical and Physical Backups
  • Backup Tools: pg_dump, pg_dumpall
  • Online Backups, Offline Backups
  • Logical Backups : Single Table
  • Multiple Tables, Single Database
  • Logical Backup Options, Usage
  • Backups using pgAdmin Tool
  • TAR, TXT, SQL Formats, Verification
  • pg_restore and psql app Usage
  • Big Database Backups and Restores
  • Partial Backups and Restores
  • Restore DB with Auto Create

Module 3

Chapter 13: Backup & Restore – 2

  • Physical Backups in Real-time, Usage
  • Offline Backups with Database Clusters
  • tar files: Windows & Linux Environments
  • Online Backups and Continous Archival
  • Auto Archival Process in LINUX OS
  • wal_level and archive_mode Options
  • archive_command with File Formats
  • WAL Archival Process with LINUX OS
  • Point In Time Recovery (PITR) in Ubuntu
  • pg_switch_all, pg_basebackup Utilities
  • Physical Backups, Continous Archival
  • sudo Users for WAL Archival, Permissions
  • nano and vi for Config Edits, Precautions

Chapter 14: Performance Tuning – 1

  • Indexes : Purpose and Query Executions
  • Single Column Index and Real-time Use
  • Multi Column Index and Real-time Use
  • Unique Indexes and Data Retreival
  • Implicit Indexes and Index Expressions
  • Index-Only Scans and Covering Indexes
  • Index Types: btree, Hash, GiST, SP-Gist
  • GIN and BRIN: Real-time Usage
  • Index Recommendations in Real-time
  • Views : Creation and Usage in PSQL
  • DML Operation on Tables with Views
  • Temporary View and Temporary Tables
  • Materliazed Views and Real-time Use

Chapter 15: Performance Tuning – 2

  • Table Partitions & Performance Benefits
  • Table Inheritance For Table Partitioning
  • Data Routing Functions and Triggers
  • Full Text Search (FTS), Keyword Indexes
  • Document: TSVector, TSQuery Usage
  • to_tsvector and to_tsquery Options
  • Single Term Search, Multi Term Search
  • Word Statistics, pg_available_extensions
  • CTE: Common Table Expressions, Usage
  • Real-time Scenarios for CTE Usage
  • Recursive CTEs, Recursive Views
  • ReIndexing Strategies: Table Level
  • Schema Level, DB Level ReIndexing

Chapter 16: Performance Tuning – 3

  • Table Clusters in Postgres, Usage
  • Index, Table & Database Level Clusters
  • Performance Advantages with Clusters
  • Maintenance Tasks : Planner Statistics
  • Updating Statistics, Analyse Query Plans
  • Vacuum Options, Analyse Command
  • AutoVacuum Program, Query Costs
  • Page Count, Row Count and Seq Cost
  • Vacuum and Vacuum Full Programs
  • Visibility Map File (VSM) and its Use
  • Free Space Map File (FSM) and it Use
  • TOAST and AccessExclusiveLock
  • VacuumDB Command : Clean, Analyze

Chapter 17: Lock, Database Migrations

  • Transactions and Lock Management
  • Table Level Locks, Row Level Locks
  • Query Blocking and Deadlocks
  • Advisory Locks and Metadata Locks
  • Lock Queue, Lock Management Options
  • Page Locks, Shared and Exclusive Locks
  • TXD_CURRENT and pg_stat_activity
  • pg_cancel_backend,pg_terminate_backend
  • PG_LOCKS and TIMEOUT Options
  • Database Migrations with PostgreSQL
  • SQL Server Migrations to PostgreSQL
  • Database Migration Tools, Schema Check
  • Data Type Mapping, Pipe/TCP Connections

Chapter 18: HA-DR with Postgres

  • High Availability and Disaster Recovery
  • PostgreSQL Replication, Advantages
  • wal sender, wal receiver, startup
  • pg_hba.conf Entires for repl host
  • pg_basebackup & Fault Tolerance
  • Single and Multi Master Replication
  • recovery.conf file & configurations
  • primary_conninfo & standby_mode
  • Log Shipping Options with Replication
  • Replication Setup Validations
  • grep sender, grep receiver, grep startup
  • pg_stat_replication, PID Audits
  • Real-time Considerations with HADR

Part 2: Azure Database For PostgreSQL

Chapter 19: Azure Database For PostgreSQL

  • Azure Databases for PostgreSQL
  • Relational Database as a Service
  • PostgreSQL Community Edition
  • Advantages, Implementation Options
  • Deployment Models in Azure
  • Single Server, Flexible Server
  • HyperScale (Citus) Options
  • Azure (Cloud) Operations
  • PaaS, SaaS and IaaS Options
  • Azure Cloud Configurations
  • Azure Resources and Groups
  • Need for DBaaS in Azure
  • Database As a Service Advantages

Chapter 20: Azure Fundamentals

  • Azure Resources and Services
  • Azure Resources and Services
  • Azure Storage Account & Use
  • Azure Blob Storage Containers
  • LRS, GRS and RA-GRS Options
  • Hot and Cold Data Storage
  • Creating Azure File Shares
  • Azure Active Directory (AAD)
  • AAD Services and Options
  • Creating Users and Groups
  • Ownerships and Group Permissions
  • App Registrations – Service Principals
  • Service Principal Tokens and IAM

Chapter 21: PostgreSQL Server, DB in Azure

  • Relational Database as a Service
  • Creating Resource Groups in Azure
  • Creating PostgreSQL Server in Azure
  • Flexible Server Deployment Options
  • Server Name, Location, Versions
  • Compute and Storage Options
  • Administrator Account & Privileges
  • Connection Information in Azure
  • Firewall Configuration Options
  • psql Connections to Azure PostgreSQL
  • Database and Table Creations
  • Test Data Insertions and Queries
  • OnPremise Versus Azure Differences

Chapter 22: PostgreSQL DB Migrations

  • Database Assessment Options
  • Migrating OnPremise DB to Azure
  • Migrating PostgreSQL to Azure SQL
  • Using DMS [Data Movement Service]
  • Working with Hyperscale Server
  • Microsoft Virtual Networks & VPN
  • Network Security Group (NSG) for DMS
  • Vcore Purchasing with PostgreSQL
  • Server Level and DB Level Firewalls
  • Migrating Schema using Bash Commands
  • Dropping the Foreign Keys for Migration
  • Trigger Management Options for DMA
  • DMS Instance Provision with CLI

Chapter 23: DB Backups and Restores

  • Automated Backups in Azure
  • Manual Backup Configurations
  • Backup History and Maintenance
  • Backup Types and Retention
  • Backup History Purge in Azure
  • Backup Audits and Monitoring
  • Single / Multiple DB Backups
  • Restore Database (PITR) Options
  • Manual Restores with PostgreSQL
  • Long Term Retention Options
  • Recovery of Lost Databases
  • Re-Restoring DBs in Azure

Chapter 24: Monitoring and Tuning

  • Enabling Data Collection in Azure
  • PostgreSQL Server Parameters
  • pg_qs.query_capture_mode
  • pgms_wait_sampling.query_capture_mode
  • Query Performance Insights
  • Query Metrics and Visualizations
  • Long Running Queries, Troubleshooting
  • Wait Statistics and Server Dashboards
  • Performance Recommendations
  • Query Execution Plans & EXPLAIN
  • Index Recommendations and Stats
  • Differences with On-Premise

Chapter 25: Security Management

  • Using Identity & Access Management
  • PostgreSQL Server Level Security
  • PostgreSQL Database Level Security
  • PostgreSQL Object Level Security
  • PostgreSQL Data Level Security
  • Vulnerability Assessment (VA)
  • Transparent Data Encryption (TDE)
  • SSL Configuration and Protection
  • Firewall Security and Network
  • Security Baselines and Controls
  • Security Strategy in PostgreSQL
  • Differences with On-Premise

Chapter 26: HA & DR Concepts

  • High Availability Concepts (HA)
  • Disaster Recovery Concepts (DR)
  • HA DR during High Usage Periods
  • HA DR during Planned Maintenance
  • Automated and Manual Failover
  • Force Failover & Connection Switch
  • Configure Hyperscale Citus For HA DR
  • Enabling High Availability with Worker
  • Coordinator Status and Availability
  • Data Distribution Options
  • HA DR Strategy in PostgreSQL
  • Differences with On-Premises

Chapter 27: Planned Maintenance

  • Automated Patching in Azure
  • Service Features, S/W Updates
  • Planned Maintenance: Duration
  • Planned Maintenance: Impact
  • Alerts and Notifications in Azure
  • Event Types and Action Groups
  • Alert Rules and Pricing Options
  • Cancel / Postpone Maintenance
  • Retry Logic with Planned Maintenance
  • Troubleshoot Connection Issues
  • Transient Errors and Solutions
  • Persistent Errors and Solutions
  • Real-time Recommendations

PostgreSQL DBA Training Plans

Plan A

PostgreSQL
DBA

Plan B

PostgreSQL
with Azure
Course CurriculumChapters 1 to 18Chapters 1 to 27
Duration5 Weeks7 Weeks
PostgreSQL: Archtiecture, Configurations✔✔
PostgreSQL: SQL Queries and Sub Queries✔✔
PostgreSQL: Constraints, Keys, Joins & Views✔✔
PostgreSQL: Stored Procedures and Functions✔✔
PostgreSQL DBA: Lock Management, Tuning✔✔
PostgreSQL DBA: Backups and Restores✔✔
PostgreSQL DBA: HA DR with Replication✔✔
PostgreSQL DBA: HA DR with Log Shipping✔✔
PostgreSQL DBA: Security Management✔✔
PostgreSQL DBA: Service Packs, Maintenance✔✔
PostgreSQL DBA: Common Issues, Solutions✔✔
PostgreSQL in Azure: Configurations, Migrations✖✔
PostgreSQL in Azure: Server Modes✖✔
PostgreSQL in Azure:Hybrid Cloud✖✔
PostgreSQL in Azure: Maintenance and Tuning✖✔
PostgreSQL in Azure: Server Failover✖✔
PostgreSQL in Azure: Server Maintenance✖✔
PostgreSQL in Azure: Security Management✖✔
Total Course Fee ( Payable in Installments)*INR 18000USD 220*INR 22000USD 270*

PostgreSQL DBA Training

S NoTime (IST, Mon - Fri)Start DateWRegister
18:15 AM - 9:15 AMNov 27thRegister
27 PM - 8 PMNov 20thRegister

Can’t find a batch you
were looking for?

CONTACT US

If you need self
paced videos

CONTACT US
Play Video

CERTIFICATE OF COMPLETION

SQL SCHOOL

24x7 LIVE Online Server (Lab) with Real-time Databases.
Course includes ONE Real-time Project.

Technical FAQs

Who is SQL School? How far you have been in the training services ?

SQL School is a registered training institute, established in February 2008 at Hyderabad, India. We offer Real-time trainings and projects including Job Support exclusively on Microsoft SQL Server, T-SQL, SQL Server DBA and MSBI (SSIS, SSAS, SSRS) Courses. All our training services are completely practical and real-time.CREDITS of SQL School Training Center

  • We are Microsoft Partner. ID# 4338151
  • ISO Certified Training Center
  • Completely dedicated to Microsoft SQL Server
  • All trainings delivered by our Certified Trainers only
  • One of the few institutes consistently delivering the trainings for more than 8+ Years online as inhouse
  • Real-time projects in
    • Healthcare
    • Banking
    • Insurance
    • Retail Sales
    • Telecom
    • ECommerce

I registered for the Demo but did not get any response?

Make sure you provide all the required information. Upon Approval, you should be receiving an email containing the information on how to join for the demo session. Approval process usually takes minutes to few hours. Please do monitor your spam emails also.

Why you need our Contact Number and Full Name for Demo/Training Registration?

This is to make sure we are connected to the authenticated / trusted attendees as we need to share our Bank Details / Other Payment Information once you are happy with our Training Procedure and demo session. Your contact information is maintained completely confidential as per our Privacy Policy. Payment Receipt(s) and Course Completion Certificate(s) would be furnished with the same details.

What is the Training Registration & Confirmation Process?

Upon submitting demo registration form and attending LIVE demo session, we need to receive your email confirmation on joining for the training. Only then, payment details would be sent and slot would be allocated subject to availability of seats. We have the required tools for ensuring interactivity and quality of our services.

Please Note: Slot Confirmation Subject to Availability Of Seats.

Will you provide the Software required for the Training and Practice?

Yes, during the free demo session itself.

How am I assured quality of the services?

We have been providing the Trainings – Online, Video and Classroom for the last EIGHT years – effectively and efficiently for more than 100000 (1 lakh) students and professionals across USA, India, UK, Australia and other countries. We are dedicated to offer realtime and practical project oriented trainings exclusively on SQL Server and related technologies. We do provide 24×7 Lab and Assistance with Job Support – even aftrer the course! To make sure you are gaining confidence on our trainings, participans are requested to attend for a free LIVE demo based on the schedules posted @ Register. Alternatively, participants may request for video demo by mailing us to contact@sqlschool.com Registration process to take place once you are happy with the demo session. Further, payments accepted in installments (via Paypal / Online Banking) to ensure trusted services from SQL School™

YES, We use Enterprise Edition Evaluation Editions (Full Version with complete feature support valid for SIX months) for our trainings. Software and Installation Guidance would be provided for T-SQL, SQL DBA and MSBI / DW courses.

Why Choose SQL School

Azure DevOps and azure fundamentals video bannerPlay Video
  • 100% Real-Time and Practical
  • ISO 9001:2008 Certified
  • Concept wise FAQs
  • TWO Real-time Case Studies, One Project
  • Weekly Mock Interviews
  • 24/7 LIVE Server Access
  • Realtime Project FAQs
  • Course Completion Certificate
  • Placement Assistance
  • Job Support
  • Realtime Project Solution
  • MS Certification Guidance
×