Real-Time Training on PostgreSQL Training

Complete Practical and Real-time Training on PostgreSQL. PostgreSQL Training will enable you to gain in-depth knowledge on all the core fundamentals of PostgreSQL. You will gain hands-on skills and expertise to administer and performance tune PostgreSQL databases through hands-on projects, and this will enable you to become a certified PostgreSQL developer.

PostgreSQL Training Plans

  PLAN A PLAN B
  PostgreSQL
Dev, DBA
PostgreSQL
with Azure
Course Curriculum Chapters 1 to 21 Chapters 1 to 30
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

PostgreSQL Training Schedules
S No Timings (IST) Start Date  
  Regular Schedules [Mon - Fri]
1 10 AM - 11 AM Jan 19th Register
2 10 PM - 11 PM Jan 4th Register
  Weekend Schedules [Sat & Sun]
2 6 PM - 8 PM Jan 30th Register

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

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

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

Ch 21: 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 22: 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 25: 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 28: 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 23: 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 26: 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 29: 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 24: 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 27: 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 30: 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