Name: Master PostgreSQL: From Basics to Advanced Management
Rating: 3.9 (3 reviews)

Udemy Business

Teach on Udemy

Turn what you know into an opportunity and reach millions around the world.

Learn More

Your cart is empty.

Keep shopping

Created byGautam Saini

Last updated 2/2025

English

What you'll learn

Gain hands-on experience with PostgreSQL setup and management.
Master Database Operations: Gain hands-on experience with installing and configuring PostgreSQL, performing backups and restores,and efficiently working with it
Learn practical troubleshooting and performance optimization techniques.
Build a strong foundation to tackle real-world PostgreSQL challenges.

Course content

10 sections • 54 lectures • 8h 20m total length

PostgreSQL introduction10:28
Discover PostgreSQL, an advanced open source RDBMS known for reliability, scalability, and a rich SQL feature set including MVCC, replication, security, and user defined functions.
Postmaster process6:37
Types of Shutdown3:17
Quiz 1
How to get most out of the course0:33

Part 1 - Download and Install PostgreSQL6:30
Install PostgreSQL by downloading from the official site for Windows, macOS, or Linux, run the installer, set the Postgres superuser password, and note the default port 5432.
Part 2 - Setup Environment variables2:56
Set PostgreSQL environment variables by adding the bin directory to the system PATH and creating Pgx data for the data directory, enabling access to utilities and cluster control.

Check Postmaster Process id6:03
Clear screen psql shell0:52
Execute script from a file8:12
Learn to execute an sql script from a file using psql, and redirect output to a log or csv file, demonstrated with a vehicle table and seven inserts.
Error role postgres does not exist12:11
Identify why the fatal role postgres does not exist error occurs when initializing a PostgreSQL cluster, and resolve it by supplying a username with initdb and using a separate port.
psql shortcuts10:32
Check Size of a table4:29
Learn how to check a PostgreSQL table size using pg_relation_size and pg_size_pretty, converting bytes to readable units.

Part 1- Introduction11:08
Part 2- What is pg_dump utility6:27
Learn pg_dump, PostgreSQL's logical backup tool, to back up a single database by default and customize dumps with -f, -A, -C, -s, -t, and --inserts.
Part 3- How to take the dump10:40
Part 4- What is pg_dumpall5:32
Learn to back up all databases in PostgreSQL with pg_dumpall, including schema-only or data-only dumps, using clean drop and create database commands.
Part 5- restore using psql utility4:37
Part 6- restore using pg_restore utility7:37
Learn how pg_restore restores dot dump files produced by pg_dump, including handling custom formats and selective table restoration via -t, with a lab walkthrough.
Part 7- Offline backups9:41
Part 8- How to set continuous archiving - LAB13:20
Part 9-File System backup or Physical backup4:42
Master file system (physical) backups of PostgreSQL by stopping the cluster and using tar to compress the data directory on Windows. Learn tar cvzf usage, backup location, and space savings.
Part 10- Backup using pg_basebackup utilty5:55
Use the pg_basebackup utility to perform a base backup of a running Postgres cluster, copying the data directory, wal files, and configuration in tar format for point-in-time recovery and replication.
Part 11- Point in time recovery and Recovery parameters15:46
Part 12- Point in time recovery - LAB21:38
Master PostgreSQL point-in-time recovery by enabling archive mode, taking a base backup with pg_basebackup, and applying wal files to recover to a specified time.
Backup & Restore

Streaming Replication - Introduction7:12
WAL Sender- WAL receiver8:08
Describe how the wal sender on the primary streams wal files to the wal receiver on the standby in streaming replication. Note wal level, max wal sender, replication slots.
Replication Configuration Parameters8:39
Parameters required on Master server9:33
Configure the master for streaming replication by setting listen addresses and hot standby, and select a wal level (minimal, replica, logical). Restart is required and monitoring replication status follows.
Setting up Streaming replication - LAB23:46
Demonstrates a lab setup of streaming replication between a master and standby in PostgreSQL, configuring wal_level replica, listen_addresses, and hot_standby, and using pg_basebackup to clone the standby.
Monitor Streaming replication - LAB24:43
Streaming Replication

Vacuum - Part 1 - Introduction11:37
Discover how vacuum in Postgres reclaims space after updates and deletes by marking row versions as dead and reusing space via the free-space map without shrinking table size.
Vacuum - Part 2 - Vacuum & Full Vacuum9:26
Vacuum - Part 3 - AutoVacuum6:55
Auto vacuum runs vacuum and analyze in the background to reclaim space and update statistics, keeping tables and indexes optimized. It prevents transaction ID wraparound and boosts query planning reliability.
Vacuum - Part 4 - AutoVacuum Parameters4:40
Learn how autovacuum runs in PostgreSQL, tune its global settings via postgresql.conf, and adjust parameters such as nap time, analyze thresholds, cost limit, and freeze max age to optimize maintenance.
Vacuum - Part 5- Disable AutoVacuum for a table7:45
Vacuum - Part 6 - Snapshot TOO OLD ERROR6:18
Vacuum - Part 7 - LAB VacuumTest10:57
Vacuum - Part 8- pg_repack utility11:48
Vacuum

Requirements

A system with good configurations - No previous knowledge to Postgres will be required..

Description

In this series we will be talking about PostgreSQL fundamentals, basics and all topics which are required for you to learn the technology and understand the concepts of PostgreSQL.

What You'll Learn:

1. PostgreSQL Fundamentals

Introduction to PostgreSQL: Understand the foundational concepts and architecture of PostgreSQL.
Postmaster Process: Learn about the crucial postmaster process and its role in database management.
Types of Shutdown: Explore different PostgreSQL shutdown modes and their appropriate use cases.
Database Cluster: Gain insights into database cluster configuration and management.

2. Installing and Setting Up PostgreSQL

Download and Installation: A step-by-step guide to downloading and installing PostgreSQL.
Environment Variables: Configure environment variables for seamless PostgreSQL operations.

3. Database and User Management

Create/Drop Database: Learn how to efficiently create and drop databases.
Create/Drop User: Master user management, including creating and removing users.
Error Handling: Resolve the common "Role 'postgres' does not exist" error.

4. PostgreSQL Tools and Commands

psql Shortcuts: Increase your productivity with essential psql command-line shortcuts.
Postmaster Process ID Check: Verify and troubleshoot the postmaster process with confidence.
Tablespaces: Manage and utilize tablespaces for optimal storage organization.

5. Backup and Restore Essentials

Introduction to Backup and Restore: Understand the importance of PostgreSQL backups.
pg_dump Utility: Learn to use pg_dump for taking backups of individual databases.
Taking Dumps: A hands-on approach to performing reliable backups.
pg_dumpall: Backup all databases in your PostgreSQL instance.
Restoration Techniques: Use psql and pg_restore utilities to recover databases.
Offline Backups: Learn how to perform offline backups for critical scenarios.
Continuous Archiving: Configure continuous archiving for real-time recovery.
File System Backup: Implement physical backups for your PostgreSQL database.
pg_basebackup Utility: Use this utility for efficient physical backups.
Point-in-Time Recovery (PITR): Recover databases to a specific point using recovery parameters.

6. Replication and High Availability

Replication Overview: Learn the significance and types of replications in PostgreSQL.
Replication Modes: Explore synchronous and asynchronous replication modes.
File-Based Replication: Understand the basics of file-based replication.
Promoting Standby: Promote a standby server to master in disaster recovery scenarios.
Streaming Replication: Set up streaming replication for real-time data consistency.
WAL Sender and Receiver Processes: Understand these critical components of streaming replication.
Replication Configuration Parameters: Configure both primary and standby servers for replication.
Replication Setup (Lab): A hands-on lab to set up streaming replication.
Monitoring Replication (Lab): Learn techniques to monitor and troubleshoot replication.

7. Database Optimization

Vacuum and AutoVacuum: Understand the importance of VACUUM operations for performance.
Full Vacuum: Learn when and how to use FULL VACUUM to reclaim space.
pg_repack Utility: Optimize tables and reclaim storage without downtime using pg_repack

Who this course is for:

Database Beginners: Individuals new to databases who want to start their journey with PostgreSQL, one of the most powerful and widely-used relational database systems.
Developers and Programmers: Software professionals looking to integrate PostgreSQL into their applications and learn effective database management techniques.
Database Administrators (DBAs): Current or aspiring DBAs seeking practical knowledge in PostgreSQL setup, replication, backup, and performance optimization.
IT Professionals and System Administrators: Tech enthusiasts responsible for managing database clusters and ensuring high availability and performance in production environments.
Students and Data Enthusiasts: Learners pursuing a career in data science, analytics, or engineering who want to build a strong foundation in relational databases.

What you'll learn

Explore related topics

Course content

Introduction4 lectures • 21min

Database Cluster2 lectures • 18min

Getting Started: Installing PostgreSQL2 lectures • 9min

Database fundamentals5 lectures • 1hr 4min

Working with PostgreSQL6 lectures • 42min

Backup & Restore12 lectures • 1hr 57min

Replication7 lectures • 1hr 9min

Recovery and Standby signal files2 lectures • 9min

Streaming Replication6 lectures • 1hr 22min

Vacuum8 lectures • 1hr 9min

Requirements

Description

Who this course is for: