PostgreSQL DBA Masterclass with Real-Time Projects

Learn PostgreSQL installation methods on Windows and Linux, including Windows GUI installer, install Linux in VirtualBox, and connect with PuTTY and WinSCP.

Install PostgreSQL 18 on a Linux server using the yum method, enterprise repository. Initialize the database, start and enable the service with systemctl, and verify the version and data directory.

Perform a controlled PostgreSQL uninstall as part of a database lifecycle, including pre-uninstallation verification, backups with pg_dump all, and careful removal of the installation method (m, rpm, source) or Windows.

Learn to connect a remote Linux PostgreSQL server to PZAdmin on Windows by validating the network, configuring pg_hba.conf and listen_addresses, adjusting firewall, and registering the server for production monitoring.

Master PostgreSQL shutdown modes with pg_ctl: smart (default), fast, and immediate, and learn their behavior. Apply each mode in production and understand safety, recovery implications, and restart or reload commands.

Explore how the postmaster starts PostgreSQL, validates config, spawns a backend per client, and coordinates background processes to enable crash recovery and secure connections.

Explore how PostgreSQL background processes—check pointer, background writer, wall writer, archiver, stats collector, and more—work under the postmaster to ensure durability, performance, and availability.

Explore how PostgreSQL memory divides into shared memory and local memory per backend, and how the buffer manager uses shared buffers to cache 8 KB data pages for performance.

Explore write-ahead logging (wall) in postgresql, including how LSN, wall buffers, and wall segment files support crash recovery through redo and checkpoints with fsync.

Explore PostgreSQL catalog views and the data dictionary to understand metadata about databases, objects, and privileges. Learn practical queries against pg_catalog and information_schema for monitoring, troubleshooting, and auditing.

Learn how to create and manage PostgreSQL databases from a production perspective, covering default databases, template databases, creation syntax, safe dropping, and template hygiene.

Learn to manage PostgreSQL sessions by canceling or terminating long-running or blocked queries, using pgCancelBackend and pgTerminateBackend, while checking blocking with pgStatActivity.

Explore PostgreSQL schema management to organize databases, isolate security, and support multi-tenant architectures. Learn schemas, system catalogs, search path, and best practices for production governance.

Learn to find database, schema, and table sizes in PostgreSQL using metacommands like \l and \dn and the pg_size_pretty function to inspect sizes.

Learn to use the contrib module to add extensions on a postgres server, such as pgstat statements, for query statistics and execution time by enabling shared_preload_libraries and restarting the cluster.

Explore PostgreSQL configuration basics, including data directory, key config files, memory, logging, replication, and performance parameters. Learn host-based authentication and OS-to-database user mappings, and follow backup safeguards for production.

Explore post-classical GUC parameters in PostgreSQL, how name, setting, unit, and context reveal defaults or modifications, and use pg_settings queries for debugging and tuning.

Use alter system to manage post-crystal configuration and change parameters, such as lock connection off, with the changes written to post-crystal.autoconfig and overwriting post-crystal.config upon reload.

Tune memory parameters to boost PostgreSQL performance by adjusting shared buffers, work memory, and effective cache size, with a 32 GB server allocating 8 GB to shared buffers.

Enable all logging parameters, including log collector, log destination, and log directory, then save and restart the server. Monitor with a query to view execution time, errors, and disconnections.

Discover how auto vacuum parameters in PostgreSQL prevent table bloat and transaction wraparound by cleaning dead tuples and reclaiming space, demonstrated with a test table.

Explore PostgreSQL user management from role level architecture to least privilege design, including users vs roles, public role, grant and revoke strategies, and role inheritance.

Explore PostgreSQL security architecture by distinguishing authentication from authorization and how pg_hba.conf controls connections, including trust, md5, scram, and peer. Learn layered, role-based permissions across database, schema, and objects.

Explore the public role in PostgreSQL, its risk of privilege escalation via search path hijacking, and protective measures like revoking create on public and per-role schema qualified calls.

Choose secure PostgreSQL user creation by understanding how attributes grant privileges or vulnerabilities, including row level security. Avoid super users, enable role-based access, and enforce connection limits to protect tenants.

Learn how grant and revoke commands implement role-based access control in PostgreSQL, creating read-only and write roles, assigning permissions to roles, and safely managing role memberships.

Design a secure enterprise privilege model in PostgreSQL by separating duties into a developer role and a no inherit migration role, enabling explicit activation and least privilege access.

Row level security in PostgreSQL enforces per-row access via policies, enabling multi-tenant data isolation within a single table using session variables like app.currentCompany.

Learn how write-ahead log (wal) files ensure data durability, crash recovery, and replication by logging changes before updating data in the pgdata/pgwall directory, with 16 MB segments and LSN-based naming.

Learn what a PostgreSQL tablespace is, how it maps logical names to physical paths, and how to create, assign, and drop tablespaces across databases, with default pgDefault and pgGlobal.

Compare and choose backup formats for PostgreSQL, explaining plain, custom, tar, and directory formats, and contrast pg_dump versus pg_dumpall with emphasis on restore performance, parallelism, and production considerations.

Describe connection options for pg_dump, including -d, -h, -p, -u, and password handling with -w, then show -f, -F, -j, -a/-s, -c, -Z, and -v for backup control.

Learn to restore PostgreSQL backups under pressure by selecting the right restore method for each format, using psql for plain backups and pg_restore for custom formats.

Master pg_dump backups in plain, tar, custom, and directory formats for single tables, multiple tables, schemas, and full databases, with real-world file naming and location practices.

Learn practical restore techniques in the PostgreSQL DBA masterclass, using psql for plain backups and pg_restore for custom and directory formats.

Perform a cluster-level backup with pg_dumpall to capture all databases, schemas, data, and global objects such as users and table spaces for migration and disaster recovery.

Learn physical backups with pg_base_backup to copy data files, wall files, and configs, enabling online or offline backups and point-in-time recovery; use a backup strategy combining logical and physical backups.

Perform a major upgrade from PostgreSQL 17 to 18 using pg_upgrade, including dual binaries, port change 5432 to 5433, pre-upgrade validation, data migration, and post-upgrade verification of object counts.

Explore PostgreSQL replication theory, including write ahead logging and wall-based and streaming replication between primary and standby. Learn hot standby and failover to ensure high availability and recovery.

Configure streaming replication in PostgreSQL 18 using a base backup from the primary to a standby, with a dedicated replication user, archiving, and wal_level replica for real-time data.