PostgreSQL Database Administration on Windows/Linux- Part 2

Unlock the power of PostgreSQL, a leading open-source Object-Relational Database Management System (ORDBMS), and master its administration! This comprehensive course provides in-depth knowledge and practical skills for managing this robust database.

What is PostgreSQL?

PostgreSQL is a powerful relational database renowned for its extensibility and adherence to SQL standards. It uniquely supports both traditional SQL (relational) and modern JSON (non-relational) data querying, making it ideal for a wide range of applications.

Key Features & Benefits:

• Robust Concurrency: Leverage Multi-Version Concurrency Control (MVCC) for enhanced data consistency and efficient handling of concurrent transactions.
• Data Recovery: Implement point-in-time recovery to restore your database to a specific point in time.
• Granular Security: Utilize granular access controls to precisely manage user permissions and data security.
• Scalability: Designed for high scalability, PostgreSQL can handle massive amounts of data and a large number of concurrent users.
• Reliable Backups: Employ online/hot backups and write ahead logging for minimal downtime during backup and recovery operations.
• Advanced Query Optimization: Benefit from a refined query planner/optimizer for faster and more efficient query execution.
• Internationalization Support: Fully supports international character sets, multi-byte character encodings, and Unicode, ensuring proper handling of diverse data.
• Locale Awareness: Provides locale-aware sorting, case-sensitivity, and formatting options.

Course Topics: Mastering PostgreSQL Database Administration

This course delves into essential PostgreSQL administration techniques with real-time examples. You'll gain expertise in:

• Replication:

  • Introduction to Replication: Understand the fundamentals and terminology.
  • Master & Slave Configuration: Set up and manage master-slave replication.
  • Warm & Hot Standby: Implement both warm and hot standby configurations for high availability.
  • Types of Replication: Explore different replication methods.
  • Modes of Replication: Understand various replication modes and their implications.
  • Log Based Shipping Standby Initial Setup: Learn the initial setup for log-based shipping standby.
  • Log Based Shipping Standby on Windows & Linux: Configure log-based shipping standby on both Windows and Linux platforms.
  • Log Based Shipping Standby Failover: Implement failover mechanisms for log-based shipping standby.
  • Streaming Replication Setup: Set up and configure streaming replication.
  • Monitoring Streaming Replication: Monitor the health and performance of streaming replication.
  • Synchronous Streaming Replication: Configure synchronous streaming replication for data consistency.
  • Configure Physical Replication Slots Failover Streaming Replication: Configure physical replication slots for failover.
  • Repmgr – Replication Manager: Utilize Repmgr for simplified replication management.
  • Automatic Failover using Repmgr: Automate failover processes with Repmgr.
  • Node Rejoin of Failed Primary: Recover a failed primary node.
  • Add New Node and Standby: Add new nodes and standby servers to the replication topology.
  • Switch Over From Primary to Standby: Perform a controlled switchover from primary to standby.
  • Cascading Streaming Replication: Implement cascading streaming replication for multi-master setups.
  • Witness Server: Utilize a witness server to prevent split-brain scenarios.
  • Event Notification System: Configure event notifications for replication events.
  • Repmgr Monitoring: Monitor the status of your replication setup using Repmgr.
  • Upgrade Replication Manager: Upgrade your Repmgr installation.
  • Uninstall Replication Manager: Remove Repmgr from your system.

• Logical Replication:

  • Logical Replication Setup: Set up logical replication for data transformation and filtering.
  • Publication and Subscription: Understand the concepts of publications and subscriptions.
  • Logical Replication Slots: Manage logical replication slots.
  • Add Table to Publication: Add tables to a publication.
  • Refresh Subscription: Refresh a subscription to reflect changes in the publication.
  • Cascading Logical Replication: Implement cascading logical replication.
  • Uninstall Publication and Subscription: Remove publications and subscriptions.

• Performance Tuning:

  • Tune PostgreSQL Server Parameters: Optimize PostgreSQL server parameters for performance.
  • Shared Buffers: Configure shared buffers for optimal memory utilization.
  • Work_Mem: