PostgreSQL Database Administration on Windows/Linux- Part 1

Explore the power of PostgreSQL, a leading open-source Object-Relational Database Management System (ORDBMS), ideal for demanding applications. This course provides comprehensive knowledge of PostgreSQL database administration through practical, real-time examples. Learn how PostgreSQL seamlessly handles both SQL (relational) and JSON (non-relational) data, making it a versatile choice for modern data management.

Key Features & Capabilities:

• Robust Architecture: Understand PostgreSQL's advanced features, including Multi-Version Concurrency Control (MVCC) for enhanced concurrency, point-in-time recovery for data integrity, and granular access controls for security.
• Scalability: Discover how PostgreSQL scales effectively to manage vast amounts of data and support a large number of concurrent users.
• Reliability: Learn about asynchronous replication, nested transactions, and online/hot backups to ensure data availability and minimize downtime.
• Performance: Master the refined query planner/optimizer and write-ahead logging for optimal database performance.
• Internationalization: Work with international character sets, multi-byte character encodings, and Unicode with full locale awareness for accurate sorting, case-sensitivity, and formatting.

Course Topics:

• PostgreSQL Process and Memory Architecture: Gain a deep understanding of how PostgreSQL operates.
• Installation & Environment Setup: Learn to install PostgreSQL on both Windows and Linux, and configure essential environment variables.
• Storage Internals: Explore PostgreSQL storage internals, including database directory layout and configuration files.
• Cluster Management: Master cluster management using tools like initdb, start/stop, reload/restart, createdb/dropdb, and createuser/dropuser.
• User Management: Implement best practices for user management and secure access.
• Schema Design: Learn to create schemas and manage the schema search path. Understand the purpose of the pgpass file and psqlrc.conf.
• psql Interface: Become proficient with the psql interface commands for interacting with your database.
• System Catalogs: Explore the pg system catalogs to gain insights into database structure.
• Data Manipulation: Master CRUD operations (Create, Read, Update, Delete) for efficient data management.
• Advanced Data Management: Learn about MERGE and MERGE RETURNING statements, table inheritance, and table partitioning.
• Tablespaces: Utilize tablespaces in PostgreSQL for optimized storage.
• Backup & Recovery: Implement robust backup strategies, including continuous archiving and point-in-time recovery (PITR). Learn about incremental backups using pg_basebackup and the wal summarizer process.
• Performance Tuning: Understand and utilize the EXPLAIN plan and query execution cost for performance optimization.
• Maintenance: Learn about essential maintenance tasks such as updating planner statistics, vacuuming, routine reindexing, and log file maintenance. Understand best practices for routine reindexing.
• Data Fragmentation: Address data fragmentation using vacuum, vacuum full, and the auto vacuum daemon.
• Automation: Automate backups and maintenance tasks for efficient database management.
• Pgadmin 4 Upgrade: Learn to upgrade PostgreSQL using pg_dumpall and pg_upgrade.
• Uninstall: Uninstall PostgreSQL on both Windows and Linux.
• PostgreSQL Versions: A journey into PostgreSQL v13, PostgreSQL v15 (including new features), and PostgreSQL v16 (including new features).

This course is designed for database administrators, developers, and anyone seeking to master PostgreSQL for building scalable, reliable, and high-performance applications.