PostgreSQL Database Administration on Windows/Linux- Part 1

Understand PostgreSQL architecture, from client requests and the postmaster to backend processes with local and shared memory, including shared buffers, wall buffers, checkpoints, wall writer, and background writers.

Explore the Postmaster, the master Postgres process that supervises and restarts background processes. It handles connections, authentication and authorization, recovery, and spawning back end processes across Windows and Linux.

Explore PostgreSQL background processes, including check pointer, auto vacuum launcher, archival, logger, writer, and wal writer, and learn how they manage dirty buffers, wal files, and recovery in production environments.

Explore the local memory component for each backend process, including work_mem for sorting and joins, temp buffers for temporary tables, and maintenance_work_mem, with default values and adjustment in postgresql.conf.

Discover the physical files of PostgreSQL, including data files for tables and indexes, and the write-ahead log for transaction recovery, with optional archive logs for backups.

Explore how PostgreSQL stores data with pages, segments, and toast, including 8 kb pages, automatic toasting of oversized rows into 2 kb chunks, and the page layout.

Install PostgreSQL 17.4 on Windows 64-bit using the installer, configure the data directory and port 5432, install pgAdmin 4, and set the Postgres superuser password.

Learn how to start and stop a PostgreSQL cluster on Windows and Linux, using PG_CTL stop with smart, fast, or immediate modes; defaults to smart if no mode is specified.

Explore the PostgreSQL installation directory layout, including bin, data, doc, include, lib, and pgadmin4, and examine the data directory contents like base, global, logs, replication slot, and configuration files.

The base directory contains all databases and their objects, each identified by an OID. Postgres is the default database used by utilities, while template0 remains pristine and template1 remains modifiable.

Demonstrates PostgreSQL database oids, listing databases with \l, connecting as postgres to templates, and creating and dropping a macro database to show folder per oid in the base directory.

Learn to manage the main postgresql.conf file in the data directory, including per-line parameters, restart requirements, backups, and sections such as connection and authentication and write ahead log.

Map operating system users to PostgreSQL users with the optional pg_identity.conf file, enabling external authentication via identity or API; define identity maps and reload on startup.

Explore how pg_identity.conf maps operating system usernames to database users for PostgreSQL connections, using peer authentication and pg_hba.conf to handle local versus external logins.

Explore the pg_hba.conf file, the host-based authentication gateway that controls who can connect to PostgreSQL, using local or host types, cidr notation, and scram-sha-256.

Demonstrates configuring pg_hba.conf and postgresql.conf to grant an application server access, create a read-only user, set a password or use trust authentication, and reload the server.

Create and manage PostgreSQL users using syntax and the create user utility, assign privileges such as superuser, create database, and roles, and demonstrate encrypted passwords and non-interactive options.

Implement PostgreSQL best practices for user management by enforcing least privilege, grouping with roles, strong passwords, row level security, auditing, and service accounts.

Explore how schemas organize database objects into logical groups, isolating HR, sales, and finance data, and how search_path determines which schema PostgreSQL searches for objects.

Master the psql interface, a terminal-based front end to PostgreSQL, and learn essential SQL commands, connection options including SSL mode, and an included command checklist with demo practice.

Learn to use the pgpass file to store PostgreSQL credentials and enable passwordless login for local and remote connections via psql on Linux and Windows.

Explore PostgreSQL system catalogs and monitoring views to observe schema metadata and real-time database activity, including pg_stat_activity, pg_logs, pg_stat_database, and pg_settings.