Master PostgreSQL: From Basics to Advanced Management

Understand that a database cluster is a collection of databases managed by a single server instance; use init db to create a new cluster with separate data directories and ports.

Learn to create and manage multiple PostgreSQL database clusters on a single server by initializing new data directories, changing ports, and connecting with psql to verify databases.

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.

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.

Learn how to create and drop PostgreSQL databases using the create database command or create db utility, manage permissions with superusers or create db privileges, and work with template databases.

Create a new PostgreSQL user using the create user utility or a shell script, with or without superuser privileges and a password.

Map the data directory folders to databases by querying the Pgx database and inspecting the base folders. Verify the mapping by creating a new database and checking its folder ID.

Learn how to work with timestamps in PostgreSQL by using select now to get the current time and add intervals like two hours or five days to compute future times.

Discover how PostgreSQL table spaces designate on-disk locations for tables and indexes, enabling selective storage across devices, and understand how default tablespaces guide database creation and data layout.

Identify the postmaster process by reading the postmaster pid file in the data directory. Confirm the pid in task manager before starting or stopping PostgreSQL.

Learn to clear the PostgreSQL psql shell by typing the command \! CLS. Use a slash and exclamation mark, then CLS, to clear your screen in the PostgreSQL session.

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.

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.

Explore essential psql shortcuts for PostgreSQL, including listing databases and users, viewing tables and schemas, connecting to databases, and features like timing and editing commands.

Learn how to check a PostgreSQL table size using pg_relation_size and pg_size_pretty, converting bytes to readable units.

Explore PostgreSQL backups and restores by comparing physical and logical backups, including file-system backups and PITR, and learn SQL dump and custom format dumps with pg_dump and pg_dumpall.

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.

Learn to back up a PostgreSQL database with pgdump, choosing text or sql formats. Use options like -C, -S, -s, and inserts to control creation and data.

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.

Restore a PostgreSQL backup with the psql utility by dropping and recreating testdb from the backup file, then verify tables, indexes, and sequences.

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.

Explore offline backups (file system backups) requiring stopping the server to back up data files and restore the full database, and online backups through continuous wal archiving for point-in-time recovery.

Enable continuous archiving in PostgreSQL by turning on archive mode and configuring the archive command to copy wal files to the archive destination; restart and validate point-in-time recovery and replication.

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.

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.

Learn how PostgreSQL point-in-time recovery uses base backups and archived wal logs to restore a database to a specific time, guided by recovery targets and parameters.

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.

Explore how PostgreSQL replication uses physical and logical modes to maintain master and standby servers, with streaming and file based replication, wall files, and load distribution for high availability.

Explore two PostgreSQL replication configuration types: warm standby, a non-connected slave that continuously copies data; and hot standby, a connected, read-only slave for load balancing and high availability.

Explore PostgreSQL replication modes, including asynchronous and synchronous configurations. Learn how master and standby servers handle wal archiving, acknowledgments, and the trade-offs between data protection and potential replication delay.

Explore replication models and topologies in PostgreSQL, including single master unidirectional replication from a designated master to replication servers and multi-master bidirectional replication with conflict resolution for duplicate primary keys.

Learn file-based replication in PostgreSQL, an asynchronous physical replication with wall files archived from master to standby. Archiving governs shipment, and a 16 MB threshold controls when files are applied.

Learn to configure file-based replication in PostgreSQL by archiving wal files, setting archive and restore commands, preparing master and standby servers, and validating read-only standby synchronization.

Learn how to promote a file-based standby in PostgreSQL to master by using pg_promote, handling archived WAL files, removing standby.signal, and updating pg conf file to route traffic.

Learn how the recovery.signal file starts point-in-time recovery on a standby PostgreSQL server by placing an empty recovery.signal in the data directory and configuring recovery parameters in postgres.conf.

The standby.signal file signals Postgres to enter standby mode, making the database read-only and accepting only changes from the master server to reduce master load.

PostgreSQL streaming replication transfers wal records from master to standby in real time, asynchronously by default, with wal sender and wal receiver and a read-only standby.

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.

Explore replication configuration parameters for PostgreSQL streaming, including wall level, wall log hints, and max wal sender. Understand wall keep segments and hot standby for failover and read-only operation.

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.

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.

Learn to monitor streaming replication between master and standby in PostgreSQL using commands such as pg_stat_replication and wal receiver for status and lag.

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.

Learn how vacuum and vacuum full manage dead tuples in PostgreSQL, reclaim space, and address table locking and bloated tables, with guidance on when to run full vacuum during maintenance.

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.

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.

Disable autovacuum for a specific table and tailor vacuum behavior by adjusting autovacuum threshold and scale factor with alter table; monitor autovacuum activity via pg_stat_activity.

Understand how vacuum and autovacuum prevent snapshot too old errors with transactions using old snapshot threshold, and how values (minutes) like -1, 0, and 86400 affect behavior and require a restart.

Explore how vacuum handles table bloat in PostgreSQL by using a vacuum test table, comparing manual vacuum, auto vacuum, and vacuum full, and observing space reuse and reclaiming space.

Pg_repack enables online table reorganization and space reclamation with minimal downtime, unlike full vacuum; use dry runs and note limits like triggers, foreign keys, disk space, and superuser privilege.