PostgreSQL for Oracle DBAs

Explore a high-level comparison of Oracle and Postgres, highlighting shared RDBMS features, ACID properties, concurrency, backup approaches, and differences in logging, replication, and extensions.

Explore database architecture concepts, including database, cluster, and instance, and compare Oracle and Postgres background processes, postmaster functions, listener process, and memory components.

Discover how Postgres defines a database cluster and an instance, including distinct PG data areas and ports, with the postmaster orchestrating processes for multiple clusters.

Compare Oracle and Postgres processes with a simplified diagram, correlating log writer and redo log, wall writer and wall files, and archiving with postmaster and back end processes.

Postmaster, the master process, spawns background and shared memory structures, listens for connections, reads configuration files, initializes memory, starts and monitors processes, and enforces crash recovery with wal records.

Postgres uses a single postmaster as the listener for the configured port, spawning backend sessions for each client connection; backends allocate memory, file descriptors, and manage transactions as clients communicate.

Compare memory components of Oracle and Postgres, mapping buffer cache to shared buffer, redo log buffer to wal buffer, and sort area to work_mem. Postgres allocates work_mem per client.

Install Postgres 15 on CentOS 7 using yum, initialize the cluster, and note a straightforward setup easier than Oracle.

Demo shows installing PostgreSQL 15 on linux (red hat based, centos 7) from postgresql.org, installing the contra package, initializing the cluster, and enabling automatic start.

Learn to use psql for PostgreSQL DBAs: connect to databases, use backslash commands like \l, \c, \d, and manage autocommit, alongside executing scripts with \i and monitoring with watch.

Store passwords securely for postgres connections using the .pgpass file to simplify authentication in scripts. Place it in the home directory with the format hostname:port:database:userid:password and set permissions to 0600.

Learn how the pkl rc file, placed in the home directory, customizes the pkl environment, persists prompts and autocommit settings, and enables aliases and startup SQL scripts for PostgreSQL workflows.

Explore instance management in PostgreSQL by comparing startup, shutdown, and reload operations with Oracle, detailing postmaster startup, configuration file rereads, and dynamic parameter changes via pg_ctl reload or pg_reload_conf.

This demo compares PostgreSQL shutdown modes, including fast shutdown and shutdown immediate, and shows startup crash recovery with redo of the wall log and a new checkpoint.

Explore the contents of Pgx data and its configuration files, including folder structure, control files, Dconf and Pgcon, PostgreSQL conf parameters, static or dynamic context, and Pkgbuild conf.

Explain how pgx data location defines the cluster with base, global, and log directories, postgres conf and autoconf, and how init db initializes these files.

The control file, usually named pkg underscore control, is an eight kilobyte binary in the cluster's global directory that stores server state and the latest checkpoint, unlike Oracle.

Explore the Pgx data by examining its configuration files and directories, including the PostgreSQL conf and the pgx conf, and learn how pgx based backup backs up this directory.

Explore key PostgreSQL conf parameters, including lesson address, max connections, shared buffers, workman, maintenance workman, wall level, log settings, and archiving options, with emphasis on default values and tuning guidelines.

Learn how pg_settings context controls when parameters change in postgres, from postmaster static requiring restart to dynamic contexts by reload, with alter system, alter database, alter role, or set local.

Learn how to manage and inspect PostgreSQL parameters across conf, autoconf, and various context levels, using alter system, reload, and pg_db_role_setting to apply per-database and per-user overrides.

Explore tools that generate recommended PostgreSQL conf parameters—memory, checkpoint, connections, and performance—based on workload and hardware, including PG tune and Cybertech PostgreSQL configurator.

Explore pg_hba.conf, PostgreSQL's host-based authentication firewall, showing how type, database, user, address, and method govern allow or reject connections from specific IPs, with trust, password, md5, sha256, and peer examples.

Learn to configure pg_hba.conf for host-based authentication, using IP CIDR ranges and MD5 passwords for apps and catalog users connecting to ERP and catalog databases, including localhost rules.

Learn how pg_hba.conf controls authentication, switching from OS user-based to MD5, reload configs, and place local and host entries to deny or allow ERP database access from specific clients.

Explore the logical and physical structures of PostgreSQL, including global objects, table spaces, temporary table spaces, data files, and toast tables.

Explore global objects in a PostgreSQL cluster, including cluster-wide users, roles, and table spaces, and learn how table spaces separate storage across disks to reduce io contention.

Define table spaces as logical storage containers for tables and indexes, including the default and pkg global spaces, to reduce I/O contention in Postgres.

Demonstrate a tablespace demo by creating u01, setting Postgres ownership, naming app_table_space, connecting via psql, and linking pg data to u01, then describe and insert into the table.

Learn how PostgreSQL uses temporary tablespaces to hold spillover files for sorting and hashing when work_mem fills, separating temp data from main files to reduce input/output contention and boost performance.

Demonstrate creating and using a temporary table space in PostgreSQL, including per-session temp files, OS-level usage, and memory versus disk sorting with work_mem tuning.

Each table uses a dedicated data file, stored in the directory or a tablespace directory; large tables split into 1 gb segments named by the file node and dot numbers.

Demonstrate how PostgreSQL maps tables to OS data files, including 1-to-1 links in the default table space and 1-to-many 1 GB chunks for large tables, with drops removing files.

Learn how toast, the oversized attribute storage technique in Postgres, handles large field values by inline compression or moving data to a toast table in chunks roughly 2000 bytes each.

Demonstrate how PostgreSQL uses compression and toast storage for extended text, comparing plain and extended types and showing toast table usage during inserts.

Explore the system catalog in pg_catalog, a Postgres-specific metadata store for tables and views, with information_schema offering a cross-platform perspective.

Demonstrates exploring the Postgres catalog with pg_stat_activity and pg_stat_statements, benchmarks workload with pgbench, and reviews table, database, and index statistics for Oracle migration.

PostgreSQL uses multiversion concurrency control to allow multiple users to read and write data concurrently, with old versions stored in the same table until commit and cleaned up by vacuum.

Explore PostgreSQL mvcc through a live demo showing transaction IDs, xmin and xmax, and ctid behavior across sessions, including updates, commits, and vacuum cleanup.