PostgreSQL DBA Video 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
No of Videos 18 Videos 27 Videos
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 12000/-
USD 163
INR 18000/-
USD 244

Trainer: Mr. Sai Phanindra

Register Now @ 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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

Video 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
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