Getting Started with PostgreSQL: A Beginner's Guide

PostgreSQL is a powerful, open-source relational database management system trusted by organisations of all sizes. This guide walks you through everything you need to go from zero to a running database.

Installation

# Ubuntu / Debian
sudo apt update && sudo apt install postgresql postgresql-contrib

# macOS (Homebrew)
brew install postgresql && brew services start postgresql

# Windows — download the installer from postgresql.org

Creating Your First Database

-- Connect as the postgres superuser
sudo -u postgres psql

-- Create a database
CREATE DATABASE my_first_db;

-- Connect to it
\c my_first_db

-- Create a users table
CREATE TABLE users (
    id         SERIAL PRIMARY KEY,
    username   VARCHAR(50)  UNIQUE NOT NULL,
    email      VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert a row
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');

-- Query it back
SELECT * FROM users;

Essential psql Commands

Command	Description
\l	List all databases
\dt	List tables in current database
\d table_name	Describe table structure
pg_dump db > backup.sql	Create SQL dump
psql db < backup.sql	Restore from dump

User Management

CREATE USER myuser WITH PASSWORD 'secure_password';

GRANT ALL PRIVILEGES ON DATABASE my_first_db TO myuser;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myuser;

-- Revoke when the user leaves
REVOKE ALL PRIVILEGES ON DATABASE my_first_db FROM myuser;

Next Steps

• Learn about indexes for query performance
• Understand transactions and ACID guarantees
• Explore backup and recovery strategies
• Study EXPLAIN ANALYZE to interpret query plans