Postgresql Administrator (PGSQL-220)

This comprehensive course targets IT professionals and database administrators seeking to deepen their knowledge in PostgreSQL administration. Covering a wide range of administrative tasks, the course provides insights into the best practices for database setup, maintenance, performance tuning, security, and troubleshooting. Through hands-on exercises and real-world case studies, participants will learn to ensure the reliability, security, and optimal performance of PostgreSQL databases.

Target Audience:

• Database administrators and IT professionals responsible for managing PostgreSQL databases.
• Systems administrators looking to expand their database management skills.
• IT professionals aiming to transition into database administration roles.

Prerequisites:

• Basic knowledge of SQL and relational databases.
• Familiarity with command-line operations and Linux environments.
• Prior experience with PostgreSQL or other relational databases is beneficial but not mandatory.

Module 1: PostgreSQL Fundamentals​​​​

• Introduction to PostgreSQL
• History and Architecture
• Key Features and Benefits
• Installation and Setup
• Installation on various platforms
• Initial server configuration
• Creating and Managing Databases
• Creating, dropping, and altering databases
• Managing users and roles
• The psql Command-Line Interface
• Basic commands and navigation
• Common administrative tasks with psql

Module 2: PostgreSQL Configuration

• Server Configuration Parameters
• Understanding key configuration parameters
• Modifying configuration files
• Restarting the server with changes
• Client Connection Configuration
• Configuring client connections
• Connection pooling
• Resource Management
• Memory management
• Disk space management

Module 3: PostgreSQL Security

• Security Best Practices
• Implementing strong passwords
• Securing network connections
• Authentication Methods
• Password authentication
• Peer authentication
• LDAP authentication
• Role-Based Access Control (RBAC)
• Creating and managing roles
• Granting and revoking privileges
• Encryption and Data Masking
• Encrypting data at rest and in transit
• Data masking techniques

Module 4: Backup and Recovery

• Backup Strategies
• File system backups
• Logical backups
• Streaming backups
• Recovery Scenarios
• Single-user mode recovery
• Point-in-Time Recovery (PITR)
• Backup and Recovery Tools
• pg_dump, pg_restore
• WAL archiving

Module 6: Performance Monitoring and Tuning

• Monitoring PostgreSQL Performance
• Server statistics
• Query monitoring
• Log analysis
• Query Tuning
• Analyzing query plans with EXPLAIN
• Creating and using indexes effectively
• Resource Limits
• Setting resource limits for users and databases
• Vacuuming and Analyzing
• Understanding vacuuming and analyzing
• Optimizing vacuuming parameters

Module 7: Advanced Administration

• Advanced Indexing Techniques
• Multicolumn indexes
• Partial indexes
• Expression indexes
• Concurrency Control
• Transaction isolation levels
• Locking mechanisms
• PostgreSQL Extensions
• Installing and using PostgreSQL extensions

Module 8: Troubleshooting and Maintenance

• Common Troubleshooting Issues
• Connection problems
• Performance bottlenecks
• Deadlocks
• Log Analysis
• Interpreting server logs
• Maintenance Tasks
• Regular maintenance procedures