POSTGRES DATABASE ADMINISTRATION
S.N STEPHEN NJOROGE
Linkedln:[Link]/in/stephen-njoroge
|SENIOR DATABSE ADMINISTRATOR|ORACLE| POSTGRESQL | MSSQL|OCI|AWS|
CHAPTER 3: POSTGRESQL PHYSICAL STRUCTURES – DIRECTORY STRUCTURES.
About the Author:
I am Stephen Njoroge, a Senior Database Engineer with over 8+ years of expertise in designing, securing
and optimizing high-performance database systems across the banking and fintech sectors. My core
technical proficiency spans major databases: Oracle, PostgreSQL, MS SQL Server and MySQL-along with
advanced scripting in SQL and Python for automation and data pipeline development. I architect and
manage cloud and virtualization solutions on OCI, AWS, and Proxmox for onprem systems and implement
comprehensive DevOps practices using monitoring tools like Prometheus and Grafana and IT service
management via iTop. Further, I deliver advanced data analytics and visualization using Tableau, Power BI
and Oracle BI. As a Certified Data Protection Officer, I integrate robust data protection, compliance and
security measures including vulnerability assessment and role-based access control into every layer of the
data infrastructure.
POSTGRESQL DATABASE ADMINISTRATION SERIES |
Chapter 3: PostgreSQL Physical Structures - Directory Structures.
Understanding Postgresql service architecture:
PostgreSQL uses a process-based, client/server architecture designed for reliability, concurrency and
extensibility. Below is a clear, DBA-oriented explanation from the moment a client connects to how data is
stored and managed.
Overview
PostgreSQL consists of:
Client applications (psql, pgAdmin, applications)
PostgreSQL server (postmaster / postgres)
Backend server processes
Shared memory
Background processes
Physical storage (data files, WAL, configs)
Client–Server Model
PostgreSQL runs as a server process (postmaster or postgres)
Clients connect over:
TCP/IP
Unix domain sockets
Each client connection is handled by a dedicated backend process
Postmaster (Parent Process)
The postmaster process:
Starts when PostgreSQL is launched
Listens for incoming connections
Authenticates users
Forks a new backend process for each connection
Manages shared memory and background workers
Backend Server Processes
Each client gets its own backend process, which:
Parses SQL
Generates query plans
Executes queries
Reads/writes data via shared buffers
Communicates with background processes
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Shared Memory Architecture
PostgreSQL uses shared memory for coordination and performance.
Key Shared Memory Areas:
a) Shared Buffers
Cache of table and index pages
Reduces disk I/O
Controlled by shared_buffers
b) WAL Buffers
Temporarily store WAL records
Written to disk by WAL writer
c) Lock Manager
Manages table, row and transaction locks
d) Process Information
Active sessions
Transaction status
Statistics
Background Processes
PostgreSQL relies on several background processes to maintain performance and consistency.
Key Background Processes:
Process Role
Writes dirty buffers to disk at
Checkpointer
checkpoints
Background Writer Gradually writes dirty pages
WAL Writer Flushes WAL buffers to disk
Autovacuum Launcher Starts autovacuum workers
Autovacuum Worker Cleans dead tuples
Stats Collector Collects performance stats
Logical Replication Workers Handle logical replication
Query Processing Flow
When a client runs a query:
Chapter 3: PostgreSQL Physical Structures – Directory Structures
1. Parser – checks syntax
2. Analyzer – resolves tables, columns, types
3. Planner/Optimizer – chooses the best execution plan
4. Executor – runs the plan
5. Results returned to client
Transaction and MVCC Architecture
PostgreSQL uses MVCC (Multi-Version Concurrency Control):
Readers don’t block writers
Writers don’t block readers
Each row version has:
o xmin (creating transaction)
o xmax (deleting/updating transaction)
Write-Ahead Logging (WAL)
To guarantee durability:
Changes are first written to WAL
Data files are updated later
WAL files live in pg_wal/
Physical Storage Layout
Inside the data directory:
base/ → database data files
pg_wal/ → Write-Ahead Logs
pg_tblspc/ → Tablespaces
global/ → cluster-wide tables
pg_stat/ → statistics
Chapter 3: PostgreSQL Physical Structures – Directory Structures
PostgreSQL Server Architecture.
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Directory Layout
/ (Root)
├── /var/lib/postgresql/ # Main data directory (default)
│ ├── /18/ # Version-specific directory
│ │ ├── main/ # Default cluster "main"
│ │ │ ├── base/ # Database files (critical)
│ │ │ │ ├── 1/ # System database (template1)
│ │ │ │ ├── 13427/ # User database 1
│ │ │ │ └── 13428/ # User database 2
│ │ │ ├── global/ # Cluster-wide tables
│ │ │ ├── pg_wal/ # Write-Ahead Logs (WAL) [previously pg_xlog]
│ │ │ ├── pg_multixact/ # MultiXact data
│ │ │ ├── pg_commit_ts/ # Commit timestamps
│ │ │ ├── pg_dynshmem/ # Dynamic shared memory
│ │ │ ├── pg_logical/ # Logical decoding
│ │ │ ├── pg_notify/ # LISTEN/NOTIFY data
│ │ │ ├── pg_replslot/ # Replication slots
│ │ │ ├── pg_serial/ # Serializable transaction info
│ │ │ ├── pg_snapshots/ # Exported snapshots
│ │ │ ├── pg_stat/ # Permanent statistics
│ │ │ ├── pg_stat_tmp/ # Temporary statistics
│ │ │ ├── pg_subtrans/ # Subtransaction data
│ │ │ ├── pg_tblspc/ # Tablespace symlinks
│ │ │ ├── pg_twophase/ # Prepared transactions
│ │ │ └── PG_VERSION # Version file
│ │ └── othercluster/ # Additional cluster
│ ├── /13/ # Another PostgreSQL version
│ └── /15/ # Latest version
├── /etc/postgresql/ # Configuration files
│ ├── /18/
│ │ ├── main/
│ │ │ ├── [Link] # Main config
│ │ │ ├── pg_hba.conf # Client auth
│ │ │ └── pg_ident.conf # User mapping
│ │ └── othercluster/
│ └── /15/main/
├── /var/log/postgresql/ # Log files
│ ├── [Link]
│ └── [Link]
├── /tmp/ # Temporary files
├── /home/postgres/ # Postgres user home
└── /mnt/postgres_data/ # Alternative data mount (for large DBs)
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Enterprise-Grade Production Layout
/ (Root)
├── /postgres/ # Dedicated PostgreSQL mount point
│ ├── /data/ # Main data (fast SSD/NVMe)
│ │ ├── /18/main/ # Current production
│ │ ├── /18/replica/ # Replica data
│ │ └── /backups/ # Base backups
│ ├── /wal/ # WAL directory (separate fast disk)
│ ├── /tablespaces/ # Tablespaces (optional)
│ │ ├── /indexes/ # Index tablespace
│ │ ├── /hot_data/ # Frequently accessed data
│ │ └── /cold_data/ # Archived/rarely accessed
│ ├── /archive/ # Archived WALs (large HDD)
│ ├── /backups/ # Logical backups
│ └── /scripts/ # Maintenance scripts
├── /etc/postgresql/ # Configuration
└── /var/log/postgresql/ # Logs
Data Directory Structure:
Chapter 3: PostgreSQL Physical Structures – Directory Structures
How to Verify Directory Structure
Check Current Data Directory
-- From inside PostgreSQL
SHOW data_directory;
SHOW config_file;
SHOW hba_file;
-- Get all directories
SELECT name, setting FROM pg_settings
WHERE name LIKE '%dir%' OR name LIKE '%file%'
ORDER BY name;
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Verify File system Structure
# Check main data directory
ls -la /var/lib/postgresql/18/main
Chapter 3: PostgreSQL Physical Structures – Directory Structures
# Check permissions
ls -la /var/lib/postgresql/18/main/ | head -20
Verify whether the Key Directories Exists or not.
#!/bin/bash
# verify_structure.sh
DATA_DIR=$(psql -t -c "SHOW data_directory;" | xargs)
echo "Data Directory: $DATA_DIR"
echo "Checking essential directories..."
for dir in base global pg_wal pg_xact pg_multixact pg_commit_ts; do
if [ -d "$DATA_DIR/$dir" ]; then
echo "✓ $dir exists"
else
echo "✗ $dir MISSING!"
fi
done
echo -e "\nChecking permissions..."
ls -ld $DATA_DIR
ls -ld $DATA_DIR/base
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Check Directory Permissions
# All should be owned by postgres user
ls -la /var/lib/postgresql/18/main/
# Files: 600 or 640, Directories: 700 or 750
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Verify Disk Layout
# Check mount points
df -h /var/lib/postgresql
df -h /var/log/postgresql
# Check disk usage
du -sh /var/lib/postgresql/18/main/*
# Check inode usage
df -i /var/lib/postgresql
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Configuration Validation
# Check if config files exist
ls -la /etc/postgresql/18/main/
# Should have: [Link], pg_hba.conf, pg_ident.conf
# Verify config syntax
sudo -u postgres /usr/lib/postgresql/18/bin/postgres \
--config-file=/etc/postgresql/18/main/[Link] \
--check
Complete Health Check Script
#!/bin/bash
# postgres_health_check.sh
echo "=== PostgreSQL Health Check ==="
echo "Timestamp: $(date)"
echo ""
# 1. System Information
echo "1. System Info:"
echo "Hostname: $(hostname)"
echo "PostgreSQL Version: $(psql --version)"
echo ""
# 2. Running Clusters
echo "2. Running Clusters:"
Chapter 3: PostgreSQL Physical Structures – Directory Structures
if command -v pg_lsclusters &> /dev/null; then
sudo pg_lsclusters
else
ps aux | grep postgres | grep -v grep
fi
echo ""
# 3. Data Directory Structure
echo "3. Data Directory Structure:"
PGDATA=$(psql -t -c "SHOW data_directory;" 2>/dev/null | xargs)
if [ -z "$PGDATA" ]; then
PGDATA="/var/lib/postgresql"
fi
echo "PGDATA: $PGDATA"
if [ -d "$PGDATA" ]; then
tree -L 3 $PGDATA 2>/dev/null || find $PGDATA -maxdepth 3 -type d | sort
else
echo "ERROR: PGDATA directory not found!"
fi
echo ""
# 4. Permissions Check
echo "4. Permission Check:"
sudo find $PGDATA -type f ! -user postgres 2>/dev/null | head -5
if [ $? -eq 0 ]; then
echo "WARNING: Files not owned by postgres user!"
else
echo "✓ All files owned by postgres"
fi
echo ""
# 5. Disk Usage
echo "5. Disk Usage:"
df -h $PGDATA
echo ""
du -sh $PGDATA/* 2>/dev/null | sort -hr
echo ""
# 6. Configuration Files
echo "6. Configuration Files:"
CONF_FILE=$(psql -t -c "SHOW config_file;" 2>/dev/null | xargs)
HBA_FILE=$(psql -t -c "SHOW hba_file;" 2>/dev/null | xargs)
echo "Config: $CONF_FILE"
echo "HBA: $HBA_FILE"
if [ -f "$CONF_FILE" ]; then
echo "Config size: $(wc -l < "$CONF_FILE") lines"
fi
echo ""
# 7. Database Status
echo "7. Database Status:"
psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) as size,
datcollate, datctype FROM pg_database ORDER BY size DESC;"
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Chapter 3: PostgreSQL Physical Structures – Directory Structures
Check Tablespace Structure
-- View tablespaces and their locations
SELECT spcname, pg_tablespace_location(oid)
FROM pg_tablespace;
-- Check symlinks in pg_tblspc
ls -la /var/lib/postgresql/18/main/pg_tblspc/
WAL Directory Verification
# Check WAL directory
ls -la /var/lib/postgresql/18/main/pg_wal/
Chapter 3: PostgreSQL Physical Structures – Directory Structures
# Should contain .wal files
# Check WAL archive status
psql -c "SELECT * FROM pg_stat_archiver;"
PostgreSQL Configuration scripts for Ideal Structure
# [Link]
data_directory = '/postgres/data/18/main'
hba_file = '/etc/postgresql/18/main/pg_hba.conf'
ident_file = '/etc/postgresql/18/main/pg_ident.conf'
external_pid_file = '/var/run/postgresql/[Link]'
# Logging
log_directory = '/var/log/postgresql'
log_filename = '[Link]'
# WAL Settings
wal_level = replica
archive_mode = on
archive_command = 'cp %p /postgres/archive/%f'
wal_keep_size = 10GB
Chapter 3: PostgreSQL Physical Structures – Directory Structures