PostgreSQL Administration - 5 days Online Classroom
3.450,00 EUR
- 5 days
Develop the practical skills needed to install, configure, secure, maintain, and optimize PostgreSQL databases in enterprise environments. This hands-on PostgreSQL Administration course guides you through the complete database lifecycle, from initial deployment and user management to performance tuning, backup and recovery, and ongoing maintenance.
Key Features
Language
Course material in English
Level
Beginner - Intermediate level
5 days (8 hours/day) online instructor-led
60+ recommended study hours
Real-world lab exercises
Course materials to keep for future reference
Covers PostgreSQL versions 13 to 18
Troubleshooting tips based on real scenarios
Ask for date confirmation!
Program completion certification included
Learning Outcomes
At the end of this program, you will be able to:
Advanced Transaction Management
Learn how PostgreSQL maintains data consistency through transactions, concurrency control, locking mechanisms, and deadlock prevention.
PostgreSQL Logging and Monitoring
Configure and analyze PostgreSQL logs to troubleshoot issues, monitor performance, and identify security or query-related problems.
Managing PostgreSQL Extensions
Install, configure, and manage PostgreSQL extensions to enhance monitoring, indexing, and database functionality.
High-Performance Data Loading
Optimize bulk data imports using COPY, indexing strategies, and performance tuning techniques for large-scale data migrations
Course timeline
Day 1
Session 1: Installation and Configuration of PostgreSQL
- PostgreSQL installation and deployment
- Database clusters and server configuration
- Account and environment management
Session 2: PostgreSQL Architecture
- PostgreSQL architecture and processes
- Memory, storage, and directory structure
- MVCC, WAL, and VACUUM operations
Session 3: PostgreSQL Client Applications
- Using psql and pgAdmin
- Access control and authentication
- Client connectivity and troubleshooting
Session 4: Creating and Configuring Databases
- Creating, copying, and deleting databases
- Database server administration
- Database monitoring and cluster initialization
Day 2
Session 5: PostgreSQL Schemas
- Schema concepts and management
- Search paths, privileges, and system schemas
- Information Schema overview
Session 6: Role Management
- Roles, users, and privileges
- Role membership and inheritance
- Access management and troubleshooting
Session 7: Fine-Grained Access Control
- Database, schema, table, and column permissions
- GRANT and REVOKE commands
- Privilege management
Session 8: Index Creation and Management
- PostgreSQL index types and strategies
- Creating, optimizing, and maintaining indexes
- Performance tuning with indexes
Session 9: Transactions and Concurrency
- Transaction management and control
- MVCC and isolation levels
- Locking, deadlocks, and concurrency management
Day 3
Session 10: PostgreSQL Database Structure
- Configuration files and storage architecture
- Tables, rows, indexes, and file layout
- Free Space Map and Visibility Map
Session 11: Managing Tablespaces
- Tablespace concepts and administration
- Creating, modifying, and removing tablespaces
- Tablespace management with SQL and pgAdmin
Session 12: PostgreSQL Logging
- Logging destinations and formats
- Configuring logging and event tracking
- Monitoring and troubleshooting with logs
Session 13: PostgreSQL Extensions
- Installing and managing extensions
- PostgreSQL Contrib modules
- Creating and removing database extensions
Day 4
Session 14: Backup and Recovery
- Backup strategies and database exports
- Using pg_dump, pg_dumpall, and pg_restore
- Database and file system recovery
Session 15: Point-in-Time Recovery (PITR)
- Write-Ahead Logging (WAL) and crash recovery
- Continuous archiving and base backups
- PITR setup, recovery, and timeline management
- Transaction log optimization and restore points
Day 5
Session 16: PostgreSQL Query Optimization
- Query optimization and execution planning
- Scan and join strategies
- EXPLAIN, ANALYZE, and statistics management
- Performance tuning and memory optimization
Session 17: Routine Database Maintenance
- VACUUM and Autovacuum management
- Storage optimization and cleanup
- Routine database maintenance tasks
Session 18: Server Performance Monitoring and Tuning
- Database and system performance monitoring
- Activity, lock, WAL, and archiving analysis
- Statistics collection and performance tuning
Session 19: Efficient Database Population
- High-performance data loading with COPY
- Bulk load optimization techniques
- Index, constraint, and WAL management
- Efficient backup and migration operations
Who Should Enroll in this Program?
Database Administrators (DBAs)
PostgreSQL Administrators
Linux System Administrators
Infrastructure Engineers
DevOps Engineers
Technical Support Engineers managing PostgreSQL environments
Prerequisites
- Basic SQL knowledge
- Familiarity with relational database concepts
- Basic Linux or UNIX administration experience
- Experience working with PostgreSQL databases is beneficial but not mandatory
- Understanding of database querying and data management concepts
Statements
Licensing and accreditation
The course is offered according to Partner Program Agreement and complies with the License Agreement requirements.
Equity Policy
Candidates are encouraged to reach out to AVC for guidance and support throughout the accommodation process.
Frequently Asked Question
Need corporate solutions or LMS integration?
Didn't find the course or program which would work for your business? Need LMS integration? Write us, we will solve everything!