Amazon RDS for PostgreSQL
Duration
3 days
Description
This comprehensive course is designed for computer programming professionals seeking to deepen their understanding and skills in PostgreSQL administration, particularly in an Amazon RDS environment. The course covers a wide range of topics, from the basics of PostgreSQL and Amazon RDS, creating a database server, managing database parameters, to advanced topics like data migration, configuration, maintenance, security, performance tuning, and backup and restore procedures. The course also includes a section on troubleshooting common issues and best practices for security, performance, backup and recovery, monitoring, maintenance, and cost management. This course is a must for anyone looking to enhance their PostgreSQL administration skills and knowledge.
Objectives
- Understand the key features and architecture of PostgreSQL and Amazon RDS for PostgreSQL.
- Learn how to create a database server and manage different types of instances.
- Master the process of creating a database, setting parameters, and choosing options.
- Gain proficiency in exporting and importing data to and from Amazon RDS and S3.
- Learn how to configure and maintain PostgreSQL databases, including managed patches, upgrades, and instance maintenance.
- Understand and implement security measures, including data encryption in transit and at rest.
- Learn how to tune performance and manage storage types and auto-scaling.
- Master backup and restore procedures, including snapshots, automated and manual backups, and point-in-time recovery.
Prerequisites
No prior experience with PostgreSQL is necessary. Students should have a basic understanding of SQL and relational databases.
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.
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.
Outline
- Introduction to 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
- Conclusion
- Summary of Key Concepts
- Q&A
- Further Resources and Next Steps