PostgreSQL Installation and Setup: A Complete Beginner's Guide

# After installation, run this command in your terminal to confirm PostgreSQL installed correctly.
# The '--version' flag asks PostgreSQL to report which version is running.
# If you see a version number, the installation worked. If you see 'command not found', it didn't.

psql --version

# You should also check that the PostgreSQL server process is running.
# On Linux and macOS this uses 'pg_isready', a built-in diagnostic tool.
# It pings the server and tells you whether it's accepting connections.

pg_isready

# On Windows, you can check via Services (services.msc) or run:
sc query postgresql-x64-16

# ─────────────────────────────────────────────
# WINDOWS (run in PowerShell as Administrator)
# ─────────────────────────────────────────────
# Download the installer from https://www.postgresql.org/download/windows/
# Then run it and follow the GUI steps. After installation, verify with:
psql --version


# ─────────────────────────────────────────────
# macOS (using Homebrew)
# ─────────────────────────────────────────────

# Step 1: Install Homebrew if you don't have it yet
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"

# Step 2: Install PostgreSQL (version 16 is current stable)
# 'brew install' downloads and installs the package automatically
brew install postgresql@16

# Step 3: Add PostgreSQL to your PATH so terminal can find the psql command
# Replace '~/.zshrc' with '~/.bash_profile' if you use bash instead of zsh
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

# Step 4: Start the PostgreSQL server.
# 'brew services start' registers it so it also auto-starts on every reboot
brew services start postgresql@16


# ─────────────────────────────────────────────
# Linux — Ubuntu / Debian
# ─────────────────────────────────────────────

# Step 1: Add the official PostgreSQL apt repository
# This ensures we get v16, not an outdated Ubuntu-packaged version
sudo sh -c 'echo "deb https://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 so apt trusts it
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Step 3: Refresh the package list and install PostgreSQL
sudo apt-get update
sudo apt-get install -y postgresql-16

# Step 4: Start the service and enable it to start on system boot
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Step 5: Confirm the service is running
sudo systemctl status postgresql

-- ─────────────────────────────────────────────
-- STEP 1: Connect to PostgreSQL
-- ─────────────────────────────────────────────

-- On Linux, first switch to the postgres system user in your terminal:
-- $ sudo -i -u postgres
-- $ psql

-- On macOS (Homebrew) or Windows, just open a terminal and run:
-- $ psql -U postgres

-- You should now see the psql prompt: postgres=#


-- ─────────────────────────────────────────────
-- STEP 2: Create a new database for a bookshop app
-- ─────────────────────────────────────────────

-- CREATE DATABASE makes a fresh, empty database.
-- Think of this as buying a brand-new filing cabinet.
CREATE DATABASE bookshop_db;

-- Verify it was created. '\l' is a psql shortcut that lists all databases.
\l


-- ─────────────────────────────────────────────
-- STEP 3: Connect to the new database
-- ─────────────────────────────────────────────

-- '\c' stands for 'connect'. Without this, your commands run against
-- the default 'postgres' database, not your new bookshop_db.
\c bookshop_db

-- The prompt changes to: bookshop_db=#
-- That '#' confirms you're now inside bookshop_db.


-- ─────────────────────────────────────────────
-- STEP 4: Create a table to store book records
-- ─────────────────────────────────────────────

-- A table is like a spreadsheet with defined column types.
-- SERIAL = auto-incrementing integer (PostgreSQL assigns the ID automatically)
-- VARCHAR(255) = text up to 255 characters
-- NUMERIC(10,2) = a number with up to 2 decimal places (perfect for prices)
-- NOT NULL = this column can never be left empty

CREATE TABLE books (
    book_id     SERIAL PRIMARY KEY,        -- Unique ID auto-assigned to every book
    title       VARCHAR(255) NOT NULL,     -- The book's title, required
    author_name VARCHAR(150) NOT NULL,     -- Author's full name, required
    price       NUMERIC(10, 2) NOT NULL,   -- Price in dollars, e.g. 12.99
    published_year INT                     -- Year published, optional
);


-- ─────────────────────────────────────────────
-- STEP 5: Insert some real book data
-- ─────────────────────────────────────────────

-- INSERT INTO adds rows to the table.
-- We don't supply book_id because SERIAL handles that automatically.
INSERT INTO books (title, author_name, price, published_year)
VALUES
    ('The Pragmatic Programmer', 'David Thomas', 39.99, 2019),
    ('Clean Code',               'Robert Martin', 34.50, 2008),
    ('Designing Data-Intensive Applications', 'Martin Kleppmann', 49.99, 2017);


-- ─────────────────────────────────────────────
-- STEP 6: Read the data back
-- ─────────────────────────────────────────────

-- SELECT * means 'give me every column'.
-- FROM books tells PostgreSQL which table to read from.
SELECT * FROM books;


-- ─────────────────────────────────────────────
-- STEP 7: Create a dedicated app user (best practice)
-- ─────────────────────────────────────────────

-- Never connect your app directly with the 'postgres' superuser.
-- Create a limited user that only has access to bookshop_db.
CREATE USER bookshop_app WITH PASSWORD 'use_a_strong_password_here';

-- GRANT gives this user permission to read/write in our database
GRANT ALL PRIVILEGES ON DATABASE bookshop_db TO bookshop_app;

-- Also grant access to the tables inside the database
\c bookshop_db
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bookshop_app;

-- Confirm the user was created
\du

# ─────────────────────────────────────────────
# PYTHON EXAMPLE — using psycopg2
# ─────────────────────────────────────────────

# First, install the driver:
# pip install psycopg2-binary

import psycopg2  # The PostgreSQL driver for Python
import os        # Used to read environment variables safely

# Read connection details from environment variables.
# Set these in your terminal before running:
#   export DB_HOST=localhost
#   export DB_NAME=bookshop_db
#   export DB_USER=bookshop_app
#   export DB_PASSWORD=use_a_strong_password_here

db_host     = os.getenv("DB_HOST", "localhost")       # Default to localhost
db_name     = os.getenv("DB_NAME", "bookshop_db")     # Our database name
db_user     = os.getenv("DB_USER", "bookshop_app")    # Limited app user
db_password = os.getenv("DB_PASSWORD", "")             # Never hardcode this

try:
    # psycopg2.connect opens an actual TCP connection to PostgreSQL.
    # Until this line runs successfully, no SQL can be sent.
    connection = psycopg2.connect(
        host=db_host,
        dbname=db_name,
        user=db_user,
        password=db_password,
        port=5432  # PostgreSQL's default port
    )

    # A cursor is like a temporary workspace for running one SQL statement.
    # You can have multiple cursors open on the same connection.
    cursor = connection.cursor()

    # Execute a SQL query — this does NOT return results yet, just runs the query
    cursor.execute("SELECT book_id, title, author_name, price FROM books ORDER BY price DESC;")

    # fetchall() retrieves every result row as a list of tuples
    all_books = cursor.fetchall()

    print(f"Found {len(all_books)} books in the database:\n")

    # Loop through the results and print each book
    for book_id, title, author_name, price in all_books:
        print(f"  [{book_id}] {title} by {author_name} — ${price}")

except psycopg2.OperationalError as connection_error:
    # This error fires when PostgreSQL isn't running, credentials are wrong,
    # or the database name doesn't exist. The error message tells you which.
    print(f"Could not connect to the database: {connection_error}")

finally:
    # Always close the cursor and connection when you're done.
    # Leaving connections open wastes server resources.
    if 'cursor' in locals():
        cursor.close()
    if 'connection' in locals():
        connection.close()
    print("\nDatabase connection closed cleanly.")


# ─────────────────────────────────────────────
# NODE.JS EXAMPLE — using the 'pg' package
# Save as connect_to_bookshop_db.js and run with: node connect_to_bookshop_db.js
# First install: npm install pg
# ─────────────────────────────────────────────

/*
const { Pool } = require('pg');  // 'Pool' manages a reusable set of connections

// Pool reads from environment variables automatically if you name them correctly.
// Set: DB_HOST, DB_NAME, DB_USER, DB_PASSWORD, DB_PORT in your environment.
const databasePool = new Pool({
  host:     process.env.DB_HOST     || 'localhost',
  database: process.env.DB_NAME     || 'bookshop_db',
  user:     process.env.DB_USER     || 'bookshop_app',
  password: process.env.DB_PASSWORD || '',
  port:     parseInt(process.env.DB_PORT) || 5432,
});

async function fetchAllBooks() {
  let client;

  try {
    // 'connect' borrows a connection from the pool
    client = await databasePool.connect();

    const queryResult = await client.query(
      'SELECT book_id, title, author_name, price FROM books ORDER BY price DESC'
    );

    console.log(`Found ${queryResult.rowCount} books:\n`);

    // queryResult.rows is an array of plain JavaScript objects
    queryResult.rows.forEach(book => {
      console.log(`  [${book.book_id}] ${book.title} by ${book.author_name} — $${book.price}`);
    });

  } catch (queryError) {
    console.error('Database query failed:', queryError.message);
  } finally {
    // Return the connection back to the pool (don't destroy it)
    if (client) client.release();
    await databasePool.end();
  }
}

fetchAllBooks();
*/