PostgreSQL for Oracle DBAs

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.

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.

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.

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.

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 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.