Skip to main content

PostgreSQL

Overview​

PostgreSQL is a powerful, open-source object-relational database system with strong reputation for reliability, feature robustness, and performance.

Installation​

Ubuntu/Debian​

sudo apt update
sudo apt install postgresql postgresql-contrib

macOS​

brew install postgresql

Docker​

docker run --name postgres-db \
  -e POSTGRES_PASSWORD=password \
  -e POSTGRES_DB=mydb \
  -p 5432:5432 \
  -d postgres:latest

Getting Started​

Connect to PostgreSQL​

# Connect as postgres user
sudo -u postgres psql

# Connect to specific database
psql -h localhost -U username -d database_name

Basic Commands​

-- List databases
\l

-- Connect to database
\c database_name

-- List tables
\dt

-- Describe table
\d table_name

-- Quit
\q

Database Operations​

Create Database and User​

-- Create database
CREATE DATABASE myapp;

-- Create user
CREATE USER myuser WITH ENCRYPTED PASSWORD 'mypassword';

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

Basic SQL Operations​

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

-- Insert data
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');

-- Select data
SELECT * FROM users WHERE name = 'John Doe';

-- Update data
UPDATE users SET email = 'newemail@example.com' WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

Configuration​

Main Configuration Files​

  • postgresql.conf - Main configuration file
  • pg_hba.conf - Client authentication configuration

Common Configuration Settings​

# postgresql.conf
listen_addresses = '*'
port = 5432
max_connections = 100
shared_buffers = 128MB

Authentication Configuration​

# pg_hba.conf
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Backup and Restore​

Backup​

# Backup single database
pg_dump database_name > backup.sql

# Backup all databases
pg_dumpall > all_databases_backup.sql

# Custom format backup
pg_dump -Fc database_name > backup.dump

Restore​

# Restore from SQL file
psql database_name < backup.sql

# Restore from custom format
pg_restore -d database_name backup.dump

Performance Tuning​

Common Optimizations​

-- Create indexes
CREATE INDEX idx_users_email ON users(email);

-- Analyze table statistics
ANALYZE users;

-- Vacuum to reclaim space
VACUUM users;

Resources​

Examples​

Coming soon...