(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 | 28 October 2024 | 6 Days | Istanbul | Registration Form Link |
| IT-D 263 | 02 December 2024 | 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
