PostgreSQL Fundamentals - Basics & Setup

A. What is PostgreSQL?

PostgreSQL (often called "Postgres") is an open-source, object-relational database management system (ORDBMS) emphasizing:

• ACID compliance - Atomicity, Consistency, Isolation, Durability

• SQL standard compliance - One of the most SQL-compliant databases

• Extensibility - Custom data types, functions, operators

• MVCC (Multi-Version Concurrency Control) - Non-blocking reads

Postgres vs MySQL

B. Installing PostgreSQL

1. Local Installation

• Linux (Ubuntu/Debian):

  bash

  sudo apt update
  sudo apt install postgresql postgresql-contrib
  sudo systemctl start postgresql
  sudo systemctl enable postgresql

• macOS (Homebrew):

  bash

  brew install postgresql@16
  brew services start postgresql@16

• Windows: Download installer from postgresql.org

2. Docker Installation

bash

# Pull and run PostgreSQL
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=customuser \
  -e POSTGRES_DB=mydatabase \
  -p 5432:5432 \
  -d postgres:16

# With persistent storage
docker run --name postgres-db \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -v ./pgdata:/var/lib/postgresql/data \
  -p 5432:5432 \
  -d postgres:16

C. psql CLI Basics

psql is PostgreSQL's interactive terminal

bash

# Basic connection
psql -h localhost -p 5432 -U username -d database_name

# Common psql commands (inside psql)
\?          # List all psql commands
\h          # SQL help
\q          # Quit psql
\l          # List databases
\c dbname   # Connect to database
\dt         # List tables
\du         # List users/roles
\d table    # Describe table structure
\x          # Toggle expanded display
\i file.sql # Execute SQL from file
\o file.txt # Send output to file

D. Connecting to a Database

Connection Strings

bash

# Format: postgresql://[user[:password]@][host][:port][/dbname][?params]
psql postgresql://alice:password@localhost:5432/mydb

# Environment variables
export PGHOST=localhost
export PGPORT=5432
export PGUSER=postgres
export PGDATABASE=mydb
export PGPASSWORD=secret
psql  # Uses env vars automatically

Basic Operations

sql

-- Create and connect to database
CREATE DATABASE mydb;
\c mydb

-- Create user
CREATE USER myuser WITH PASSWORD 'mypassword';
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

E. PostgreSQL Architecture

Client-Server Model

text

┌─────────┐    Network    ┌─────────────┐
│ Client  │───────────────► PostgreSQL  │
│ (psql,  │◄──────────────┤ Server      │
│ app)    │               └─────────────┘
└─────────┘

Process Architecture (Unix/Linux)

text

Postmaster (Main Process - PID: 1234)
├── Writer Process (background writer)
├── WAL Writer (Write-Ahead Log)
├── Checkpointer
├── Autovacuum Launcher
├── Autovacuum Worker(s)
├── WAL Sender (replication)
└── Client Backend Processes (one per connection)
    ├── Backend Process (PID: 1235) - User 1
    ├── Backend Process (PID: 1236) - User 2
    └── Backend Process (PID: 1237) - User 3

Key Components:

1. Postmaster Daemon - Main process, manages connections

2. Backend Processes - Handle individual client connections

3. Shared Memory - Shared buffers, WAL buffers, lock space

4. Background Processes:

   • BgWriter - Writes dirty buffers to disk

   • Checkpointer - Periodically writes all dirty pages

   • WAL Writer - Writes WAL buffers to disk

   • Autovacuum - Cleans up dead tuples

   • Logger - Writes error messages to log files

Data Directory Structure

text

/var/lib/postgresql/16/main/
├── base/          # Database files
│   └── 1/        # Default database (template1)
├── global/        # Cluster-wide tables
├── pg_wal/       # Write-Ahead Logs (WAL)
├── pg_log/       # Log files
├── postgresql.conf  # Configuration
├── pg_hba.conf   # Client authentication
└── pg_ident.conf # User name mapping

Configuration Files:

bash

# Main config file
/var/lib/pgsql/16/data/postgresql.conf

# Client authentication
/var/lib/pgsql/16/data/pg_hba.conf

# View settings in psql
SHOW config_file;
SHOW hba_file;

Checking Processes:

bash

# Linux
ps aux | grep postgres

# View connections
SELECT * FROM pg_stat_activity;

# Monitor in real-time
pg_ctl status
systemctl status postgresql

This foundation covers the essential starting points for working with PostgreSQL. The architecture ensures reliability through process isolation, while the client-server model supports concurrent connections efficiently.