Installation_setup

Chapter 2: Installation & Configuration

Setting Up PostgreSQL for Development and Production

---

2.1 Installation Methods Overview

                    PostgreSQL Installation Methods
    ========================================================================

                              Start: Install PostgreSQL
                                        |
                                        v
                            +-----------------------+
                            | What's your platform?|
                            +-----------+-----------+
                                        |
            +-------------+--------------+-------------+-------------+
            |             |              |             |             |
            v             v              v             v             v
        Linux         macOS          Windows       Docker       Cloud
        (YUM/APT)     (Brew)         (Installer)   (Container)  (RDS/Azul)
            |             |              |             |             |
            v             v              v             v             v
    ┌───────────┐  ┌───────────┐  ┌───────────┐  ┌───────────┐ ┌─────────┐
    │Binary     │  │Binary     │  │Installer   │  │Official   │ │Managed  │
    │Packages   │  │Packages   │  │or ZIP      │  │Images     │ │Service  │
    │(apt/yum)  │  │(brew)     │  │           │  │           │ │         │
    └───────────┘  └───────────┘  └───────────┘  └───────────┘ └─────────┘
    ========================================================================

---

2.2 Linux Installation (Ubuntu/Debian)

Installing PostgreSQL from APT Repository

# Step 1: Create the file repository configuration
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Step 2: Import the repository signing key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Step 3: Update the package lists
sudo apt-get update

# Step 4: Install PostgreSQL
sudo apt-get install postgresql-17 postgresql-client-17

# Step 5: Verify installation
psql --version
# Output: psql (PostgreSQL) 17.x.x

Alternative: Install from Default Repository

# Ubuntu 22.04+ and Debian 12+ have PostgreSQL 14+ in default repos
sudo apt update
sudo apt install postgresql postgresql-contrib

# Check version
psql --version

---

2.3 Linux Installation (RHEL/CentOS/Fedora)

Using YUM/DNF Repository

# For RHEL/CentOS 8+ or Fedora
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL server and client
sudo dnf install postgresql17-server postgresql17

# Initialize the database (first time only)
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

# Start and enable PostgreSQL
sudo systemctl start postgresql-17
sudo systemctl enable postgresql-17

# Check status
sudo systemctl status postgresql-17

# Open firewall (if enabled)
sudo firewall-cmd --permanent --add-service=postgresql
sudo firewall-cmd --reload

---

2.4 macOS Installation

Using Homebrew

# Install PostgreSQL
brew install postgresql@17

# Start PostgreSQL as a service
brew services start postgresql@17

# Or run it manually (not as a service)
pg_ctl -D /opt/homebrew/var/postgresql@17 start

# Verify installation
psql --version

# Stop PostgreSQL (if needed)
brew services stop postgresql@17

Using PostgreSQL.app

# Download from https://postgresapp.com/
# Or install via Homebrew (older method)
brew install --cask postgres

# After installation, add to PATH
# Add to ~/.zshrc or ~/.bash_profile:
export PATH="/Applications/Postgres.app/Contents/Versions/latest/bin:$PATH"

---

2.5 Windows Installation

Using Interactive Installer

Windows Installation Steps:
===========================

1. Download installer from:
   https://www.postgresql.org/download/windows/

2. Run the installer (postgresql-17.x-windows-x64.exe)

3. Installation Wizard:
   ┌────────────────────────────────────────────┐
   │  Welcome to PostgreSQL Setup               │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Installation Directory:                   │
   │  C:\Program Files\PostgreSQL\17            │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Select Components:                        │
   │  ☑ PostgreSQL Server                       │
   │  ☑ pgAdmin 4                              │
   │  ☑ Command Line Tools                     │
   │  ☑ Stack Builder                          │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Data Directory:                          │
   │  C:\Program Files\PostgreSQL\17\data       │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Set Password for 'postgres' superuser:    │
   │  Password: ************                    │
   │  Confirm: ************                    │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Port: 5432 (default)                      │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Advanced Options:                         │
   │  Locale: [Default locale]                  │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Pre-Installation Summary                 │
   │                                            │
   │  [Next]                                    │
   └────────────────────────────────────────────┘

   ┌────────────────────────────────────────────┐
   │  Installing...                             │
   │  ████████████████░░░░░░░░░░░░░░░░░░░░░░░  │
   │                                            │
   │  Complete!                                 │
   └────────────────────────────────────────────┘

---

2.6 Docker Installation

Running PostgreSQL in Docker

# Pull the official PostgreSQL image
docker pull postgres:17

# Run PostgreSQL container
docker run \
    --name my-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e POSTGRES_DB=mydb \
    -p 5432:5432 \
    -v postgres-data:/var/lib/postgresql/data \
    postgres:17

# Run with custom configuration
docker run \
    --name my-postgres \
    -e POSTGRES_PASSWORD=mysecretpassword \
    -e POSTGRES_DB=mydb \
    -p 5432:5432 \
    -v /path/to/postgresql.conf:/etc/postgresql/postgresql.conf \
    -v postgres-data:/var/lib/postgresql/data \
    postgres:17 \
    -c config_file=/etc/postgresql/postgresql.conf

# Connect to the container
docker exec -it my-postgres psql -U postgres

# Docker Compose example

Docker Compose Configuration

version: '3.8'

services:
  postgres:
    image: postgres:17
    container_name: my-postgres
    environment:
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: mysecretpassword
      POSTGRES_DB: mydb
    ports:
      - "5432:5432"
    volumes:
      - postgres-data:/var/lib/postgresql/data
      - ./backup:/backup
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U postgres"]
      interval: 10s
      timeout: 5s
      retries: 5

volumes:
  postgres-data:

# Start the containers
docker-compose up -d

# Stop containers
docker-compose down

# View logs
docker-compose logs -f postgres

---

2.7 Cloud PostgreSQL Services

Amazon RDS for PostgreSQL

# Create RDS PostgreSQL instance via AWS CLI
aws rds create-db-instance \
    --db-instance-identifier my-postgres-instance \
    --db-instance-class db.t3.micro \
    --engine postgres \
    --engine-version 17.4 \
    --allocated-storage 20 \
    --master-username postgres \
    --master-user-password mypassword123 \
    --vpc-security-group-ids sg-0123456789abcdef0 \
    --db-subnet-group-name my-subnet-group \
    --backup-retention-period 7 \
    --multi-az

# Connect to RDS PostgreSQL
psql -h my-postgres-instance.xxxx.us-east-1.rds.amazonaws.com -U postgres -p 5432

Azure Database for PostgreSQL

# Create Azure PostgreSQL server
az postgres server create \
    --resource-group myresourcegroup \
    --name mypostgreserver \
    --sku-name B_Gen5_1 \
    --location eastus \
    --admin-user postgresadmin \
    --admin-password mypassword123 \
    --ssl-enforcement Enabled

# Configure firewall
az postgres server firewall-rule create \
    --resource-group myresourcegroup \
    --server mypostgreserver \
    --name AllowMyIP \
    --start-ip-address 0.0.0.0 \
    --end-ip-address 255.255.255.255

# Connect
psql -h mypostgreserver.postgres.database.azure.com -U postgresadmin -p 5432

Google Cloud SQL

# Create Cloud SQL PostgreSQL instance
gcloud sql instances create my-postgres-instance \
    --database-version=POSTGRES_17 \
    --tier=db-f1-micro \
    --region=us-central1

# Set password
gcloud sql users set-password postgres \
    --instance=my-postgres-instance \
    --password=mypassword123

# Connect
gcloud sql connect my-postgres-instance --user=postgres

---

2.8 PostgreSQL Configuration

Understanding Configuration Files

                    PostgreSQL Configuration Files
    ========================================================================

    Configuration File Location:
    ============================

    Linux:     /etc/postgresql/17/main/postgresql.conf
    macOS:     /opt/homebrew/var/postgresql@17/postgresql.conf
    Windows:   C:\Program Files\PostgreSQL\17\data\postgresql.conf
    Docker:    /etc/postgresql/postgresql.conf

    ┌─────────────────────────────────────────────────────────────────────┐
    │                     Configuration Hierarchy                          │
    │                                                                      │
    │  1. postgresql.conf       - Main configuration                    │
    │  2. postgresql.auto.conf  - ALTER SYSTEM settings (overrides)     │
    │  3. pg_hba.conf          - Client authentication                   │
    │  4. pg_ident.conf        - User name mapping                       │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

    ========================================================================

Essential postgresql.conf Settings

# Memory settings
shared_buffers = 256MB              # 25% of RAM, max 8GB
effective_cache_size = 768MB        # 75% of RAM
work_mem = 64MB                     # Per sort/hash operation
maintenance_work_mem = 256MB        # For VACUUM, CREATE INDEX
wal_buffers = 16MB                 # WAL buffer size

# Query planning
random_page_cost = 1.1             # For SSD storage
effective_io_concurrency = 200      # Parallel I/O operations
default_statistics_target = 100     # Statistics collection

# Write-Ahead Log (WAL)
wal_level = replica                # minimal, replica, or logical
max_wal_senders = 10               # Replication senders
max_replication_slots = 10         # Replication slots
wal_keep_size = 1GB                # WAL files to keep

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000  # Log queries > 1 second

# Connection settings
listen_addresses = '*'             # Listen on all interfaces
port = 5432
max_connections = 100

pg_hba.conf - Authentication Configuration

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             all                                     peer

# IPv4 local connections
host    all             all             127.0.0.1/32            scram-sha-256

# IPv6 local connections
host    all             all             ::1/128                 scram-sha-256

# Allow replication connections
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

# Production: Allow specific application servers
host    myapp           myapp           10.0.0.0/8              scram-sha-256

# Development: Allow all
host    all             all             0.0.0.0/0               scram-sha-256
host    all             all             ::/0                    scram-sha-256

---

2.9 Managing PostgreSQL Services

Starting, Stopping, and Restarting

# Systemd (Linux)
# Start PostgreSQL
sudo systemctl start postgresql

# Stop PostgreSQL
sudo systemctl stop postgresql

# Restart PostgreSQL
sudo systemctl restart postgresql

# Reload configuration (without restart)
sudo systemctl reload postgresql

# Check status
sudo systemctl status postgresql

# Enable on boot
sudo systemctl enable postgresql

# Disable on boot
sudo systemctl disable postgresql

# Using pg_ctl (all platforms)
# Start
pg_ctl -D /var/lib/postgresql/data start

# Stop
pg_ctl -D /var/lib/postgresql/data stop

# Restart
pg_ctl -D /var/lib/postgresql/data restart

# Check status
pg_ctl -D /var/lib/postgresql/data status

---

2.10 Initial PostgreSQL Setup

Creating Users and Databases

-- Connect to PostgreSQL
sudo -u postgres psql

-- Create a new database
CREATE DATABASE myapp;

-- Create a new user with password
CREATE USER myuser WITH PASSWORD 'secure_password';

-- Grant privileges on database
GRANT ALL PRIVILEGES ON DATABASE myapp TO myuser;

-- Make user superuser (optional, not recommended for production)
ALTER USER myuser WITH SUPERUSER;

-- Connect to database
\c myapp

-- Grant schema privileges
GRANT ALL ON SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO myuser;

-- Create role with login capability
CREATE ROLE app_user WITH LOGIN PASSWORD 'password';
CREATE DATABASE app_db;
GRANT app_user TO app_db;

Setting Up Multiple Environments

-- Create development database
CREATE DATABASE myapp_dev;
GRANT ALL PRIVILEGES ON DATABASE myapp_dev TO developer;

-- Create staging database
CREATE DATABASE myapp_staging;
GRANT ALL PRIVILEGES ON DATABASE myapp_staging TO deploy;

-- Create production database
CREATE DATABASE myapp_prod;
GRANT ALL PRIVILEGES ON DATABASE myapp_prod TO admin;

-- Create read-only role for reporting
CREATE ROLE readonly WITH LOGIN PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE myapp_prod TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

---

2.11 Connecting to PostgreSQL

Using psql

# Connect to local database
psql -U postgres

# Connect to specific database
psql -U postgres -d mydb

# Connect to remote database
psql -h hostname.example.com -U postgres -d mydb

# Connect with port
psql -h hostname.example.com -p 5433 -U postgres -d mydb

# Using connection string
psql postgresql://user:password@hostname:5432/mydb

# Execute SQL from command line
psql -U postgres -d mydb -c "SELECT * FROM users LIMIT 5;"

# Execute SQL from file
psql -U postgres -d mydb -f query.sql

# Export query results to CSV
psql -U postgres -d mydb -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV" > output.csv

# Import CSV
psql -U postgres -d mydb -c "COPY users FROM STDIN WITH CSV" < input.csv

# Common psql commands
\list or \l        -- List databases
\dt                -- List tables
\du                -- List users
\di                -- List indexes
\dv                -- List views
\ds                -- List sequences
\dn                -- List schemas
\c dbname          -- Connect to database
\i file.sql        -- Execute SQL file
\h                 -- Help on SQL commands
\x                 -- Expanded display
\timing            -- Show query timing

Using Connection String (URI Format)

# Standard connection string format
postgresql://[user[:password]@][host[:port]][/database][?param1=value1&...]

# Examples
psql postgresql://postgres:password@localhost:5432/mydb
psql postgresql://user@localhost/mydb
psql postgresql://user@localhost:5433/mydb
psql postgresql://user@/mydb              # Unix socket
psql postgresql://user:@localhost/mydb   # No password (prompt)

# With SSL
psql "postgresql://user:password@hostname:5432/mydb?sslmode=require"

---

2.12 Verifying Installation

Health Check Commands

-- Check PostgreSQL version
SELECT version();

-- Check current database size
SELECT pg_database_size(current_database());

-- List all databases with sizes
SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

-- Check active connections
SELECT count(*) FROM pg_stat_activity;

-- Check database uptime
SELECT now() - pg_postmaster_start_time() AS uptime;

-- Check server settings
SHOW all;
SHOW max_connections;
SHOW shared_buffers;

-- List all tables in current schema
SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_type = 'BASE TABLE';

-- Check table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

---

2.13 Best Practices Summary

                    PostgreSQL Installation Best Practices
    ========================================================================

    1. Choose the Right Version
       ┌──────────────────────────────────────────────────────────────┐
       │  • Use the latest stable version (17.x)                     │
       │  • LTS versions for production stability                     │
       │  • Test thoroughly before upgrading                         │
       └──────────────────────────────────────────────────────────────┘

    2. Security Configuration
       ┌──────────────────────────────────────────────────────────────┐
       │  • Change default postgres user password                    │
       │  • Use SCRAM-SHA-256 authentication                          │
       │  • Enable SSL/TLS connections                               │
       │  • Restrict pg_hba.conf access                             │
       │  • Use firewall rules                                      │
       └──────────────────────────────────────────────────────────────┘

    3. Performance Tuning
       ┌──────────────────────────────────────────────────────────────┐
       │  • Configure shared_buffers appropriately                   │
       │  • Set effective_cache_size                                │
       │  • Tune work_mem for complex queries                       │
       │  • Use SSD storage                                         │
       └──────────────────────────────────────────────────────────────┘

    4. Backup Configuration
       ┌──────────────────────────────────────────────────────────────┐
       │  • Set up automatic backups                                 │
       │  • Configure WAL archiving                                  │
       │  • Test restore procedures                                  │
       └──────────────────────────────────────────────────────────────┘

    5. Monitoring Setup
       ┌──────────────────────────────────────────────────────────────┐
       │  • Enable pg_stat_statements                                 │
       │  • Configure logging                                        │
       │  • Set up slow query monitoring                             │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 3: PostgreSQL Architecture

---

Last Updated: February 2026