(Click Category to List Courses)

38 - IT-D Information Technology - Databases and Related Technologies

IT-D 263 - PostgreSQL Advance Administration (6 Days)

Code Start Date Duration Venue
IT-D 263 09 October 2023 6 Days Istanbul Registration Form Link
IT-D 263 04 December 2023 6 Days Istanbul Registration Form Link
Please contact us for fees


Course Description

PostgreSQL Advance Administration Training Course PostgreSQL is one of the most advanced open-source platforms available today. Multiple large- development. It is managed by an open source community that is dedicated to improve the scale organizations use this RDBMS for their web based and mobile based application platform to keep it relevant to today's time. This course will go through the basics, advance installation/configurations to performance monitoring/ tuning, upgrade, backup/recovery and topics and migration from Oracle to PostgreSQL as well. It will cover topics ranging from security features.

Course Objectives

  • Understand the architecture of PostgreSQL and its advanced features
  • Configure and optimize PostgreSQL for high performance 
  • Implement advanced security measures to protect sensitive data
  • Perform advanced database administration tasks such as backup and recovery, replication, and monitoring
  • Utilize advanced PostgreSQL features 

Who Should Attend?

  • IT Professionals
  • Database administrators (DBA)
  • System Administrators 
  • Developers 
  • Anyone who is responsible for managing large-scale PostgreSQL deployments and want to optimize their database performance 

Course Details/Schedule

Day 1

  • Server Configurations and Operations
  • Architectural Fundamentals
  • Setting Parameters
  • Server Start-up Failures resolutions
  • Client Connection Problems
  • Tablespace Management
  • Data Files Advantages
  • Tablespace concepts
  • Changing Default Tablespace
  • Adding, Altering & Dropping Tablespaces
  • Indexes
  • Index Types
  • Multicolumn Indexes
  • Combining Multiple Indexes
  • Indexes on Expressions
  • Setting up a Partial Index to Exclude Common Values
  • Setting up a Partial Index to Exclude Uninteresting Values
  • Operator Classes and Operator Families
  • Full Text Search

Day 2

  • Transaction Isolation and Concurrency
  • SQL Transaction Isolation Levels Details
  • Explicit Locking
  • Viewing Locks
  • Table-Level Locks & lock modes
  • Conflicting lock modes
  • Row-Level Locks, Deadlocks, Advisory Locks
  • Advanced SQL
  • Transactionsl9
  • Window Functions
  • Inheritance
  • Aggregate/Built-in Functions
  • Supported Schema Objects (OLTP, OLAP)
  • Security
  • Preventing Server Spoofing
  • Encryption Options
  • Secure TCP/IP Connections with SSL
  • Using client certificates
  • SSL Server File Usage
  • Connections and Authentication
  • Connection Settings
  • Security and Authentication
  • Auditing

Day 3

  • Resource Consumption & Performance Tuning
  • Memory and Kernel Resource Usage
  • Inspecting the Workload
  • Cost-Based Vacuum Delay
  • Archiving/ Log shipping
  • Query Planning
  • Planner Method Configuration
  • Planner Cost Constants
  • Genetic Query Optimizer
  • Optimizing SQL Queries with EXPLAIN Benchmarking and Profiling
  • Instance Tuning
  • Monitoring Database Activity
  • Standard Unix Tools
  • The Statistics Collector & Configurations, Usage Dynamic Tracing, Compiling for Dynamic Tracing Using Probes, Built-in Probes, Defining New Probes Monitoring Disk and CPU Usage
  • Error Reporting and Logging
  • Where To Log, When To Log, What To Log
  • Message severity levels
  • Statistics Monitoring
  • Automatic Vacuuming
  • Tools for log file analysis

Day 4

  • Backup and Restore/ Recovery
  • SQL Dump & restoring the dump & Compression
  • Using pg_dumpall, split
  • Use pg_dump custom dump format.
  • Continuous Archiving and Point-In-Time Recovery (PITR)
  • Setting up WAL archiving
  • Making a Base Backup
  • Incrementally Updated Backups
  • Recovering using a Continuous Archive Backup
  • Standalone hot backups, archive_command scripts, Caveats
  • Warm Standby Servers for High Availability, their Planning, Implementation & Failover
  • Migration Between Releases
  • Record-based Log Shipping
  • PostgreSQL Replication
  • Overview of PostgreSQL Replication
  • High Availability Options in PostgreSQL
  • Asynchronous Behavior
  • Streaming Replication
  • Managing Clusters Using repmgr
  • File based Log Shipping
  • Trigger-based Logical Replication with Slony & Londiste
  • Connection Pooling
  • Sharding and Scaling PostgreSQL
  • Multi Data Center Clustering

Day 5

  • Oracle to PostgreSQL Migration
  • Analysis & Planning the migration
  • Migration process Overview
  • Migration Tools
  • Estimating Migration Timeline and Costs
  • Pre-Requisites of Migration Migration Phase
  • Post Migration Steps
  • Structure and Data Migration challenges
  • Migration Issues Resolution

Day 6

  • Comparison of MySQL and MariaDB With PostgreSQL w.r.t
  • Server Architecture
  • High Availability
  • Galera Cluster
  • Security Features
  • Performance Tuning
  • Migration Tools
  • Migration challenges from Oracle
  • Backup and Recovery
  • MaxScale
  • FlexView
  • Storage Engines
  • Administration Tools