Master PostgreSQL: From Origins to Hands‑On Labs and HA Strategies

PostgreSQL Overview

PostgreSQL (PG) is a mature, community‑driven open‑source relational database released under the permissive BSD/MIT license. Its lineage traces back to the 1977 Ingres project by Michael Stonebraker, evolving through POSTGRES to PostgreSQL with funding from DARPA, ARO, NSF and ESL. Over 1,000 contributors maintain a single stable release line that emphasizes reliability, high‑transaction workloads, and extensive documentation.

Architecture

Like most RDBMS, PostgreSQL consists of a database instance (processes and shared memory) and a set of on‑disk files: control file, write‑ahead log (WAL), data files, and configuration files. The basic structure is illustrated below:

Hands‑On Labs

Lab 1 – Create a Database Prepare a filesystem path, create a tablespace, then create a database:

# OS: create directory
mkdir -p /pgdata/music
chmod 700 /pgdata/music
# PostgreSQL: create tablespace and database
psql -U postgres -c "CREATE TABLESPACE music_ts LOCATION '/pgdata/music';"
psql -U postgres -c "CREATE DATABASE music TABLESPACE music_ts;"

Lab 2 – CRUD Operations Create a table summary in the music database, insert rows, then make the primary key unique:

# Connect to music database
psql -U postgres -d music
# Create table
CREATE TABLE summary (id integer, info text);
# Insert four rows with duplicate id
INSERT INTO summary VALUES (1,'a'),(1,'b'),(1,'c'),(1,'d');
# Update ids to be unique
UPDATE summary SET id = row_number() OVER ();

Lab 3 – Configuration Changes View and modify runtime parameters. work_mem takes effect immediately, while shared_buffers requires a restart.

# Show current setting
SHOW work_mem;
SHOW shared_buffers;
# Change work_mem for current session
SET work_mem = '32MB';
# Persist change in postgresql.conf
# (edit the file or use ALTER SYSTEM)
ALTER SYSTEM SET shared_buffers = '256MB';
# Reload configuration (no restart needed for most settings)
SELECT pg_reload_conf();
# Restart required for shared_buffers
pg_ctl restart -D /var/lib/pgsql/data

Lab 4 – Performance Monitoring Use built‑in extensions and views to monitor activity:

# Install pg_stat_statements (if not present)
CREATE EXTENSION pg_stat_statements;
# Query lock information
SELECT * FROM pg_locks;
# View active sessions
SELECT * FROM pg_stat_activity;
# Generate a snapshot with pgstatspack
SELECT pgstatspack.snapshot();
# Review the generated report (HTML or text)

Lab 5 – Backup and Restore Perform a logical backup with pg_dump , including parallel mode on newer versions, then restore:

# Parallel dump (e.g., 4 workers)
pg_dump -U postgres -d music -F d -j 4 -f /tmp/music_dump
# Drop the table to simulate data loss
psql -U postgres -d music -c "DROP TABLE summary;"
# Restore the dump
pg_restore -U postgres -d music -j 4 /tmp/music_dump
# Verify data
psql -U postgres -d music -c "SELECT COUNT(*) FROM summary;"

Lab 6 – Hot Standby (Streaming Replication) Configure a primary‑standby pair using streaming replication for real‑time synchronization:

# On primary (dbserver1)
ALTER SYSTEM SET wal_level = replica;
ALTER SYSTEM SET max_wal_senders = 5;
ALTER SYSTEM SET hot_standby = on;
pg_ctl reload -D /var/lib/pgsql/data
# Create replication user
CREATE ROLE repl_user WITH REPLICATION LOGIN PASSWORD 'repl_pass';
# Allow replication in pg_hba.conf
# host replication repl_user 0.0.0.0/0 md5
# Take base backup from standby
pg_basebackup -h dbserver1 -D /var/lib/pgsql/standby -U repl_user -P -X stream
# Create standby.signal (PostgreSQL 12+)
# Or recovery.conf for older versions
# Restart standby
pg_ctl start -D /var/lib/pgsql/standby
# Test: create table on primary, verify on standby
psql -h dbserver1 -U postgres -c "CREATE TABLE test(id int);"
psql -h dbserver2 -U postgres -c "SELECT * FROM test;"

Lab 7 – PGPOOL Deploy PGPOOL as a middleware for connection pooling, load balancing and high availability. PGPOOL listens on port 9999 by default.

# Install pgpool (example on CentOS)
yum install -y pgpool-II
# Start pgpool in foreground for demo
pgpool -n
# Connect via pgpool
psql -h localhost -p 9999 -U postgres -d music -c "SELECT now();"
# Perform CRUD operations through pgpool and verify replication to standby

Technical Q&A Highlights

PostgreSQL vs. MySQL PostgreSQL uses a process‑based architecture, offers a powerful optimizer, richer statistics tables (e.g., pg_stat_statements ), and robust MVCC. MySQL relies on a thread‑based model and has different performance characteristics.

Migration from Oracle to PostgreSQL Adopt a phased approach: start with schema conversion (using tools like ora2pg ), then migrate data with pg_dump / pg_restore or logical replication. Note that Oracle’s block‑level backup (RMAN) has no direct equivalent; PostgreSQL relies on file‑level tools ( pg_basebackup , pg_rman ) and logical dumps.

Backup Strategy with Block Change Tracking (BCT) BCT stores change metadata inside the database. Switching to a different catalog directory does not lose previous backup records; the history remains accessible as long as the database containing the BCT tables is retained.

High‑Availability Failover Options Automatic failover can be achieved with a lightweight vote node, but it introduces a single point of failure. Alternatives include keepalive scripts combined with PostgreSQL’s native streaming replication, or full HA stacks such as Pacemaker + Corosync.