PostgreSQL Stored Procedures and Functions
Class Duration
21 hours of live training delivered over 3-5 days to accommodate your scheduling needs
Student Prerequisites
- No prior experience with PostgreSQL or AWS is necessary
- Students should have a basic understanding of SQL, relational databases, and cloud computing
Target Audience
DBAs, backend engineers, and data engineers aiming to craft performant PL/pgSQL stored procedures/functions, optimize query execution, and enforce security on Amazon RDS—streamlining data workflows, automating maintenance, and achieving dependable backups, recovery, and monitoring.
Description
This comprehensive course is tailored for computer programming professionals aiming to deepen their expertise in PostgreSQL administration, with a particular focus on managing databases within an Amazon RDS environment. It provides thorough coverage of foundational topics such as PostgreSQL and Amazon RDS basics, creating and configuring database servers, and managing database parameters. The curriculum then advances into more complex areas including data migration, system configuration, ongoing maintenance, and ensuring database security. Students will also gain practical insights into performance tuning, backup and restore procedures, and troubleshooting common issues. The course emphasizes best practices for security, performance optimization, backup and recovery strategies, system monitoring, regular maintenance, and cost management. Ideal for those looking to strengthen their PostgreSQL administration capabilities, this course equips learners with both the theoretical knowledge and practical skills needed to manage production-grade PostgreSQL deployments effectively.
Learning Outcomes
- Explain PostgreSQL and Amazon RDS architecture and choose between standalone, clustered, and Multi-AZ topologies.
- Provision and configure RDS for PostgreSQL instances, including parameter groups, database options, and maintenance windows.
- Secure databases with SSL/TLS in transit, encryption at rest, and auditing via PgAudit; evaluate where TDE concepts apply.
- Plan migrations to RDS and execute reliable bulk data movement using S3 export/import.
- Implement monitoring with CloudWatch, Enhanced Monitoring, Performance Insights, and log exports for proactive operations.
- Optimize performance and cost through storage auto scaling and gp2 vs. gp3 selection, plus targeted tuning.
- Design and test backup strategies using snapshots, automated/continuous backups, and point-in-time recovery; perform restores from snapshots and S3.
- Troubleshoot connectivity, performance, storage, replication/read-replica, and authentication issues; apply HA/DR, maintenance, and schema/version-control best practices.
Training Materials
All students receive comprehensive courseware covering all topics in the course. Courseware is distributed via GitHub in the form of documentation and extensive code samples. Students practice the topics covered through challenging hands-on lab exercises. Students will need a free, personal GitHub account to access the courseware. Students are provided a cloud-based environment for all demonstrations and lab exercises.
Software Requirements
Students will need a free, personal GitHub account to access the courseware. Students are provided a cloud-based environment for all demonstrations and lab exercises.
Training Topics
PostgreSQL Administration
- Overview of PostgreSQL
- PostgreSQL Architecture
- Key Features of PostgreSQL
- Key Features of Amazon RDS for PostgreSQL
Create a Database Server
- What is an Amazon RDS Instance?
- Production vs. Development Instances
- Standalone Instance
- Clustered Instances
- Multi-AZ Deployments
Create a Database
- Creating a Database
- Database Parameters
- Database Options
Export/Import Data
- Migrate to Amazon RDS
- Export Data to S3
- Import Data from S3
Configuration and Maintenance
- Difference between self-managed and managed databases
- No usual configuration files such as postgresql.conf
- Managed Patches and Upgrades
- Parameter Groups
- Configure PgAudit
- Instance Maintenance
- Database Maintenance
Security
- Data Encryption in Transit
- Explore encryption at REST and in transit
- Basic SSL setup
- Transparent Data Encryption
Performance Tuning
- Storage Auto Scaling
- RDS storage types (Gp2 vs Gp3)
Backup and Restore
- Snapshots
- Automated Backups
- Manual Backups
- Continuous Backups
- Point-in-Time Recovery
- Restore a Database from a Snapshot
- Restore a Database from S3
- Restore a Database from Another Computer
Monitoring and Logging
- CloudWatch
- Enhanced Monitoring
- Performance Insights
- Log Exports
- PgAudit
Connecting to PostgreSQL Database
- psql Command Line
- pgAdmin
- DBeaver
- Enabling Remote Access
- Connecting to RDS from EC2
Troubleshooting
- Common Connectivity Issues
- Performance Issues
- Storage-Related Problems
- Backup and Restore Failures
- Replication and Read Replica Issues
- Authentication and Authorization Errors
- RDS Instance Availability and Failures
Best Practices
- Security
- Performance
- Backup and Recovery
- Monitoring
- Maintenance
- High Availability and Disaster Recovery
- Cost Management
- Schema Management and Version Control