postgresql

PostgreSQL Administration

Installation & Setup

# On Linux (Ubuntu/Debian)
sudo apt-get install postgresql postgresql-contrib

# On macOS
brew install postgresql@15

# Docker installation
docker run --name postgres -e POSTGRES_PASSWORD=password -p 5432:5432 -d postgres:15

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

Connection Basics

# Connect to default database
psql -U postgres

# Connect to specific database
psql -U postgres -d mydb -h localhost -p 5432

# List databases
\l

# List tables in current database
\dt

# Get table info
\d table_name

# Quit psql
\q

User & Role Management

-- Create a new role
CREATE ROLE developer WITH LOGIN PASSWORD 'secure_password';

-- Create superuser role
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin_password';

-- Grant privileges on database
GRANT CONNECT ON DATABASE mydb TO developer;

-- Grant privileges on schema
GRANT USAGE ON SCHEMA public TO developer;

-- Grant privileges on tables
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO developer;

-- Grant privileges on specific table
GRANT SELECT ON employees TO developer;

-- Make role a database owner
ALTER DATABASE mydb OWNER TO developer;

-- Revoke privileges
REVOKE INSERT, UPDATE ON employees FROM developer;

-- Drop role
DROP ROLE developer;

Configuration & Tuning

# PostgreSQL configuration file
sudo nano /etc/postgresql/15/main/postgresql.conf

# Key configuration parameters:

# Memory
shared_buffers = 256MB          # 25% of RAM for dedicated server
effective_cache_size = 1GB      # 50-75% of RAM
work_mem = 64MB                 # RAM per operation

# Connections
max_connections = 200
superuser_reserved_connections = 3

# Write-Ahead Log
wal_level = replica
max_wal_senders = 3
wal_keep_segments = 64

# Query planning
random_page_cost = 1.1          # For SSD
log_min_duration_statement = 1000  # Log slow queries

Backup & Recovery