Real-Time Training on PostgreSQL DBA Training

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.... This practical course on PostgreSQL DBA also includes Real-time Project with Solution, One to One Mock Interview and Resume Guidance.

PostgreSQL DBA Training Plans

  PLAN A PLAN B
  PostgreSQL
DBA
PostgreSQL
with Azure
Course Curriculum Chapters 1 to 20 Chapters 1 to 29
Course Duration 5 Weeks 7 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 * INR 18000/-
USD 244
INR 24000/-
USD 325

Trainer: Mr. Sai Phanindra

Schedules for PostgreSQL DBA [Chapters 1 to 20]
S No Timings (IST) Start Date  
  Regular Schedules [Mon - Fri]
1 10 AM - 11 AM Apr 26th Register
2 9 PM - 10 PM Apr 19th Register
Schedules for Azure PostgreSQL DBA [Chapters 21 to 30]
S No Timings (IST) Start Date  
  Weekend Schedules [Sat & Sun]
1 9 PM - 10 PM May 22nd Register

If above schedules does not work, opt for PostgreSQL Video Training

All Trainings are completely Practical, Real-Time. Register Today

PostgreSQL DBA - TRAINING HIGHLIGHTS :

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

PostgreSQL DBA Training Course Contents:

Module I

Module II

Module III

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 Variants
  • Common UseCases of Postgres
  • Language Support, Features
  • PostgreSQL Database Installation
  • EDB Postgres Package Setup
  • PpgAdmin4, GUI Management Tool
  • Command Line Tools, Stack Builder

Ch 8: Installation from Source Code

  • Source Code Installation @ Windows
  • Short Version and Long Version
  • GNU Requirements, Getting the Source
  • Installation Procedure: Configurations
  • Environment Variables and Usage
  • Support Platforms with PostgreSQL
  • Native Windows Operating System
  • PREFIX, EXEC-PREFIX, DIRECTORY
  • html, doc, mandir, locale, datadir
  • bindir, sysconfigdir, libdir, includedir
  • Visual C++ Components for Windows
  • Installation, Cleaning, Regressions

Ch 15: Tuning - 2: Full Text Search

  • Document and Text Matching
  • Table and Indexes Configurations
  • Controlling Text Search: Parsing
  • Documents, Queries, Search Results
  • Ranking and Highlighting Results
  • Data Dictionaries: Stop Words
  • Simple Dictionary , Synonym Dictionary
  • Thesaurus Dictionary, Ispell Dictionary
  • Snowball Dictionary & Configurations
  • Testing and Debugging Configurations
  • Parser Testing and Dictionary Testing
  • GIN, GIST Index Types in PostgreSQL

Chapter 2: PostgreSQL Archtiecture, SQL

  • PostgreSQL Installation Checks
  • psql (Shell) & pgAdmin for Connection
  • Database Creation, System DB Clone
  • Database Architecture and Access
  • Client Server Model of PostgreSQL
  • Server Process and PostgreSQL Instances
  • Postmaster, WAL, Background Writer
  • Checkpointer, Logger and Archiver
  • Stats Collector, WAL Sender/Receiver
  • Data Storage and Need for SQL
  • DDL, DML and SELECT Statements
  • Databases and Tables in PostgreSQL
  • Table Creations and Data Insertions

Ch 9: Managing Databases

  • Creating and Managing Databases
  • Database Clusters in Real-time
  • Template Databases and Usage
  • GEQO Optimizer with PostgreSQL
  • Destroying Database with DROP
  • Table Spaces and Usage
  • Table Spaces with PostgreSQL
  • Changing Tablespaces, Options
  • Rename and Alter Options
  • Deleting Table Spaces
  • Show Tables, Show Databaseds
  • Describe Tables, psql Commands
  • COPY Table Options and Advantages

Ch 16: High Availability & Load Balancing - 1

  • HA DR Concepts in PostgreSQL
  • Shared Disk Failover Options
  • File System (Block Device) Replication
  • Write Ahead Log Log Shipping
  • Logical Replication with Tables
  • Trigger Based Master - Standby
  • Replication Topology in PostgreSQL
  • Architecture & Snapshots For HADR
  • Publisher, Publication and Articles
  • Subscriber, Subscription and Slots
  • Configuration Settings, Security
  • Conflicts, Replication Restrictions
  • Replication Monitoring Options

Chapter 3: SQL Concepts, Data Types

  • SELECT Statement & Conditions
  • IN, NOT IN, AND, BETWEEN Operators
  • DISTINCT, ORDER BY, NOT Operators
  • UPDATE, DELETE & SELECT INTO
  • ALTER Table & TRUNCATE Options
  • Add / Remove Columns, Renames
  • Numeric and Monetary Data Types
  • Character and Binary Data Types
  • Date / Time & Boolean Data Types
  • Enumerated and Geometric Types
  • Bit String, Text Search Data Types
  • UUID, XML, JSON, Arrays & Ranges
  • CTAS : Create Table As Select

Ch 10: Client Authentication

  • pg.hba config file, User Name Maps
  • Record Formats and Match Values
  • Authentication Methods, GSSAPI
  • SSPI Authentication and Realm
  • Peer Authentication Method
  • Ident Authentication Options
  • User Name Mapping Options
  • Trust Authentication in Real-time
  • RADIUS and PAM Authentication
  • Certification Authentication
  • Database Access Privileges
  • BSD Authentication & Issues
  • Client Authentication Connections

Ch 17: High Availability & Load Balancing - 2

  • Log Shipping Standby Servers
  • Standby Server Operation
  • Preparing Master for Standby
  • Streaming Replication Options
  • Replication Slots For HA DR
  • Cascading Replication for HA DR
  • Load Balancing Options
  • Synchronous Replication
  • Multi Synchronous Standby
  • Host Based Authentication
  • Hot Standby and Failover
  • Planning HA DR for Performance
  • Planning HA DR for Archivals

Chapter 4: Constraints and Keys

  • Constraints and Keys in PostgreSQL
  • Null and Not Null Constraints
  • Primary Key and Unique Constraint
  • Foreign Key Constraint, Relations
  • Entity Relationship (ER) Models
  • Check Constraint and Conditions
  • SERIAL and Sequence Statements
  • Identity Column and Seed, Increment
  • Temporary Tables - Creation, Use
  • Composite Keys and Self References
  • Import CSV Files into PostgreSQL DB
  • Export PostgreSQL Data to CSV Files

Ch 11: Security Management

  • Security Management Options
  • Roles : Creation and Usage
  • Role Attributes & Memberships
  • Login Roles, Passwords & Resets
  • Creating SuperUser Roles, Options
  • Grant and Revoke Permissions
  • Schema Concept and Schema Search
  • Creating and Altering Schemas
  • Using Schemas to Create Tables
  • Using Schemas to Create Views
  • Schema Level Privileges, Options
  • CURRENT USER & SESION USER

Ch 18: Lock Management

  • Lock Management Concepts
  • Lock Types and Concurrency
  • Shared, Exclusive Locks
  • Schema and Metadata Locks
  • Concurrency Levels @ psql
  • Advisory Locks in PostgreSQL
  • Table Level Locks and Use
  • Row and Page Level Locks
  • Deadlock: Preventions
  • Deadlocks: Avoidance
  • Isolation Levels : Read Committed
  • Repeatable Read, Serializable

Chapter 5: Joins, Group By Queries

  • Joins : Purpose and Types of Joins
  • Inner Join and Matching Data
  • LEFT / RIGHT and OUTER Joins
  • CROSS, NATURAL & Self Join
  • LIMIT and OFFSET with Joins
  • Update Join Statement and EXCEPT
  • CASE Statement in PostgreSQL
  • COALESCE and NULLIF Functions
  • CAST Function, Data Type Conversions
  • Group By Concept and Grouping
  • Cube and Rollup Functions
  • UNION, CASE and Related Constructs

Ch 12: Backup

  • Backups Concepts in PostgreSQL
  • Database Backup Types, Options
  • Full Database Backups & Use
  • Partial Database Backups
  • Structure and Data Backups
  • Structural Backup Options
  • Point-In-Time Recovery
  • Table Backups and Options
  • pgdump & pgdumpall Tools
  • Single Database Backups
  • Multi Database Backups
  • Backups Performance Options

Ch 19: Database Monitoring

  • Standard Tools for Monitoring
  • Statistics Collector Operations
  • Viewing and Updating Stats
  • contrib extensions, pgstat statements
  • pg_stat_plans, pgstattuple
  • pg_buffercache, check_postgres
  • check_pgactivity, pgsnmpd
  • pganalyze / collector Interface
  • Dynamic Tracing and Lock Views
  • Compiling for Dynamic Trace
  • Built-In Probes in Real-time
  • Defining New Probes and Usage

Chapter 6: CTE, Views, Transactions

  • CTE: Common Table Expression
  • Temporary Result Set and References
  • SELECT, INSERT, UPDATE & DELETE
  • Joining and CTE with a Table
  • Recursive CTEs and Window Functions
  • Performance Advantages of CTEs
  • Transactions : ACID Properties
  • Transaction Types and Options
  • COMMIT and ROLLBACK Concepts
  • Views : Creation and Real-time Use
  • DML, DDL on Views. Recursive Views
  • Updatable Views, Materialized Views

Ch 13: Restores

  • Restore Operation and Phases
  • Restore Tools in PostgreSQL
  • Using sql to Restore Database
  • Using pg_restore & tar files
  • Full Database Restores
  • Partial Database Restores
  • Point-In-Time Recovery
  • Restoring Tables and Data
  • Restoring Table Structure Only
  • create and verbose options
  • PostgreSQL restore Limitations
  • Need for 3rd Party Tools

Ch 20: Common Errors and Solutions

  • Server Startup Failures & Solutions
  • Client Connection Issues, Solutions
  • Managing Kernel Resources
  • Shared Memory and Semaphores
  • Systemd & RemovelPC Resources
  • Resource Limits in PostgreSQL
  • PostgreSQL Sever Shutdown
  • PostgreSQL Clusters, Upgrades
  • Upgrading Data via pg_dumpall
  • Upgrading Data via pg_upgrade
  • Upgrading Data via Replication
  • PostgreSQL Spoofing Prevention

Chapter 7: Stored Procedures & Triggers

  • PostgreSQL PL/pgSQL Programming
  • Stored Procedures Creation, Use
  • Variables and Constants with Functions
  • For Loop, While Loop, IF and CASE
  • Exit and Continue in Stored Procedures
  • Stored Procedures with Dynamic SQL
  • UDF : Real-time Usage in Programming
  • UDFs with Variables, Parameters
  • Functions with Stored Procedures
  • Triggers: Creation and Types of Use
  • Create, Alter, Drop, Disable Triggers
  • Updatable Views with Triggers

Ch 14: Tuning - 1: Indexes

  • Indexes : Real-time Usage, Creation
  • Index Types and Unique Index. Lists
  • Index On Expression, Partial Index
  • Reindex and Multicolumn Indexes
  • Database Tuning Options in pgsql
  • Table and Query Tuning Options
  • Index Only Scans in PostgreSQL
  • Covering Indexes and Advantages
  • Indexes and Collations in pgsql
  • Examining Index Usage in Realtime
  • EXPLAIN Command in PostgreSQL
  • Index Recommendations & Tuning

Real-time Project

  • Routine DBA Activities
  • Emergency DBA Activities
  • Downtime Options & Maintenance
  • Update Process in PostgreSQL
  • Upgrade Process in PostgreSQL
  • Just In Time Compilation
  • Using EXPLAIN and Planner
  • Planner Control with JOIN Clause
  • Database Population Options
  • Parallel Query Issues & Solutions
  • Need for Azure Cloud for PostgreSQL
  • Azure Databases for PostgreSQL

Ch 21: 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

Ch 24: 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

Ch 27: 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

Ch 22: 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

Ch 25: 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

Ch 28: 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

Ch 23: 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

Ch 26: 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

Ch 29: 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
Register Today  Other Popular Courses: Azure Synapse Training, Azure Cosmos DB Training, Azure Data Factory Training, Azure Data Engineer Training, Azure Admin Training, AZ-104 Training [+] More Courses

SQL Server T-SQL, Azure SQL, Azure DBA, Azure BI, Azure Data Engineer, Power BI Training