PostgreSQL Database Administration On AlmaLinux

Configure a virtual machine for AlmaLinux by selecting the ISO and enabling bridged networking. Complete post-install steps, set the root password, and set the hostname to Demo Box.

Connect to an AlmaLinux 8.8 VM via PuTTY as root, verify the host and available Postgres rpm packages not installed, and preview upcoming Postgres installation, overview, and architecture topics.

Discover how Postgres, an open-source object-relational database system, supports sql standards, handles complex queries, and offers features like foreign keys, triggers, and updatable views. Explore its naming conventions, data types, mvcc, default port 5432, and scalable architecture, including table sizes up to 32 TB and unlimited indexes, backed by a global community.

Explore the page layout as a data block of eight kilobytes, with a page header, item ID, item special space, and free space, illustrating data residing on disk in pages.

Explain that Postgres runs on operating systems, including Linux, Windows, and macOS, using a server process to manage database files and support client applications, while noting the recommended Postgres user.

Discover the PostgreSQL architecture, including the postmaster, shared memory, and background processes. See how data, log, and archive files flow through shared buffers and the write ahead log.

Learn to install PostgreSQL 13 on AlmaLinux: create a separate data directory, add the repository, install server and contrib RPMs, and set ownership to postgres before cluster initiation.

Initiate the Postgres cluster with initdb using the data directory and -E UTF eight, then start the server as the Postgres user to run version 13.3 on the default port.

Observe how Postgres creates subdirectories during cluster initialization, assigns memory, sets max connections to 100, notes utf eight encoding, and sets asia/kolkata time zone, with a trust authentication warning.

Explore how a Postgres db cluster works, differentiate reload and restart, and learn about shutdown modes smart, fast, and immediate using pg_ctl and systemctl.

Identify the four data directory configuration files: postgresql.conf, postgresql.auto.conf, pg_ident.conf, and pg_hba.conf, and learn how they govern connections, authentication, logging, and memory, noting restart versus reload requirements.

Explore the pg_hba.conf file and its authentication methods, including trust, reject, md5, scram-256, gssapi, and ldap, for secure database connections.

Explore the psql command line utility by listing databases, tables, indexes, schemas, views, and functions, and inspect privileges, timing, and auto-commit settings in a PostgreSQL database.

Explore default databases in PostgreSQL on AlmaLinux, including template0 and template1, and show creating users, schemas, privileges, and tables in the default Postgres database used by utilities.

Execute deployment scripts against a PostgreSQL database on AlmaLinux by validating permissions, then run demo.sql with psql as buyer against the ABC store database to create schema buyer and tables.

Explore how the schema search_path directs Postgres to locate objects, moving from the public schema to a named schema like buyer, and how setting the search path avoids schema qualifiers.

Manage PostgreSQL access by creating a read-only role and a user, granting connect on the ABC store database and enabling select queries in the buyer schema.

Verify that the read-only user cannot perform inserts in PostgreSQL on AlmaLinux by attempting an insert as user Steve on database ABC store and confirming permission denied while accessing tables.

Create a read/write role and grant connect, usage, and DML privileges (select, insert, update, delete) to a user; demonstrate by connecting as John and performing reads and writes.

Explore backup and restore in PostgreSQL on AlmaLinux, including physical and logical backups, online and offline strategies, and point-in-time recovery with archive log mode, using pg_dump.

Use pg_dump to create a full backup of the ABC store database on almaLinux and a schema-only dump, saved in the backups directory as ABC_store.sql and ABC_schema.sql.

Demonstrates compressed backups and a full database cluster backup using pg_dumpall, including schema backups for the ABC store. Shows backup sizes and restoration basics.

Restore the ABC store from backups using the binary dump format, drop and recreate the test database, then verify the restored data.

Drop and recreate the ABC store database, then restore its binary compressed dump with pgrestore from the backup directory, running in the background to generate a log.

Set PostgreSQL to archive log mode by editing postgresql.conf to enable archive_mode and archive_command, restart the database, force a log switch with pg_switchback, and verify archive mode is on.

Perform a pgbasebackup to create an online base backup from the data directory in archive log mode, using options for compression and verbose output, and verify completion after the checkpoint.

Restore the database from the base backup by stopping the database, copying the backup data directory to the data directory, and starting it again.

Explore compressed backups and split backups for PostgreSQL on AlmaLinux. See a 23 MB zip backup, gunzip extraction revealing schemas and grants, and a 10 MB split into three files.

Explore PostgreSQL tablespaces as logical objects that improve performance by distributing user data across devices, and learn about the default pg_default and pg_global spaces and their use by databases.

Create and manage a user managed tablespace in PostgreSQL on AlmaLinux, using a dedicated directory, with a user, schema, and tables, and apply the proper drop order.

Explore how temporary tablespaces support sorting and join operations in PostgreSQL, with automatic creation and cleanup. Learn they are optional, may have a path, and are dropped on shutdown.