Skip to content

README.md

Latest commit

 

History

History
315 lines (232 loc) · 9.94 KB

README.md

File metadata and controls

315 lines (232 loc) · 9.94 KB
Raw

Appendix A — Quick Reference

Purpose

This appendix summarizes essential PostgreSQL commands, queries, and Docker workflows used throughout the series.
It’s designed as a one-stop operational cheat sheet — the “what and how” without the full theory.

1. PostgreSQL Quick Start

Start PostgreSQL with Docker

docker run --name postgres -e POSTGRES_PASSWORD=mysecretpassword -p 5432:5432 -d postgres

Connect via psql

docker exec -it postgres psql -U postgres

Because this runs psql inside the container, it usually connects over the local Unix socket and will not prompt for a password.

Exit psql

\q

2. psql Meta-Commands

Command Purpose
\l List databases
\c dbname Connect to database
\dn List schemas
\dt List tables
\dv List views
\df List functions
\di List indexes
\d table_name Describe table columns and constraints
\d+ table_name Show detailed table information
\timing Toggle query timing
\x Toggle expanded display
\set List or set variables
\! Execute shell command from within psql
\password Change password for current user

3. Common SQL Admin Tasks

Operation Command
Create a new database CREATE DATABASE mydb;
Drop a database DROP DATABASE mydb;
Create user CREATE ROLE alice LOGIN PASSWORD 'secret';
Grant privileges GRANT ALL PRIVILEGES ON DATABASE mydb TO alice;
View current connections SELECT * FROM pg_stat_activity;
Kill a backend SELECT pg_terminate_backend(pid);
Analyze statistics ANALYZE;
Manual vacuum VACUUM;
Compact table VACUUM FULL table_name;
Rebuild index REINDEX INDEX idx_name;
Force checkpoint CHECKPOINT;

4. Query Tuning & Monitoring

Query Plan and Timing

EXPLAIN
SELECT *
FROM table_name
WHERE
column = 'value';
EXPLAIN ANALYZE
SELECT *
FROM table_name
WHERE
column = 'value';

I/O and Buffer Use

EXPLAIN (ANALYZE, BUFFERS)
SELECT *
FROM table_name
WHERE
column > 100;

Cache Hit Ratio

SELECT datname,
       blks_hit::float / nullif(blks_hit + blks_read, 0) AS cache_hit_ratio
FROM pg_stat_database;

Top Queries by Execution Time

SELECT query, total_exec_time, calls
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 5;

Requires pg_stat_statements to be preloaded via shared_preload_libraries.

Table and Index Sizes

SELECT relname                                       AS name,
       pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables;

5. Backup & Restore Commands

Logical Backup

pg_dump -U postgres -d mydb -F c -f backup.dump

Restore Logical Backup

pg_restore -U postgres -d targetdb backup.dump

Physical Backup

pg_basebackup -U backup -D /path/to/backup -Fp -Xs -P

Enable WAL Archiving (in postgresql.conf)

archive_mode = on
archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'

Perform Recovery to Specific Time

restore_command = 'cp /archive/%f %p'
recovery_target_time = '2025-03-12 10:30:00'

6. Configuration & Performance Tuning

Parameter Purpose Typical Range
shared_buffers Memory for caching table data 25–40% of RAM
work_mem Per-sort/join memory 4–64MB
maintenance_work_mem Memory for VACUUM, CREATE INDEX 256MB–1GB
effective_cache_size OS-level cache hint ~75% of RAM
wal_buffers WAL write cache 4–16MB
max_connections Concurrent connections 100–500
checkpoint_timeout Time between checkpoints 5–15 min
autovacuum_vacuum_scale_factor Autovacuum trigger ratio 0.1–0.2

View current settings:

SHOW all;

7. Observability Commands

Task SQL Command
Active queries SELECT pid, query, state FROM pg_stat_activity;
Current locks SELECT * FROM pg_locks;
Table I/O stats SELECT * FROM pg_statio_user_tables;
Checkpointer SELECT * FROM pg_stat_checkpointer;
Background writer SELECT * FROM pg_stat_bgwriter;
Dead tuples SELECT relname, n_dead_tup FROM pg_stat_user_tables;
Database stats SELECT * FROM pg_stat_database;
Reset statistics SELECT pg_stat_reset();

8. Index Reference

Task Command
Create index CREATE INDEX idx_name ON table (column);
Create unique index CREATE UNIQUE INDEX idx_name ON table (column);
Create partial index CREATE INDEX idx_partial ON table (column) WHERE column > 100;
Create expression index CREATE INDEX idx_expr ON table ((LOWER(column)));
Rebuild index REINDEX INDEX idx_name;
Drop index DROP INDEX idx_name;

9. User & Role Management

Operation Command
Create role CREATE ROLE user_name LOGIN PASSWORD 'pw';
Grant superuser ALTER ROLE user_name SUPERUSER;
Add role membership GRANT role_name TO user_name;
List roles \du
Change password ALTER ROLE user_name PASSWORD 'newpw';

10. Useful Docker Shortcuts

Task Command
Start PostgreSQL container docker start postgres
Stop PostgreSQL container docker stop postgres
Enter interactive shell docker exec -it postgres bash
Connect to psql docker exec -it postgres psql -U postgres
Copy file to container docker cp file.sql postgres:/tmp/
Copy file from container docker cp postgres:/tmp/file.sql .

11. Diagnostic Patterns

Problem Quick Check
Slow query EXPLAIN ANALYZE
Lock contention pg_locks
High I/O pg_statio_user_tables
High CPU pg_stat_statements
Autovacuum lag pg_stat_user_tables
Missing index EXPLAIN shows sequential scan
Disk usage pg_total_relation_size()

12. Handy Queries for Everyday Ops

Database Size Overview

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database;

Table Fragmentation Check

SELECT relname, n_dead_tup, vacuum_count, autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Index Bloat Detection (approximation)

SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 10;

WAL Statistics

SELECT pg_wal_lsn_diff(pg_current_wal_insert_lsn(), restart_lsn) AS wal_lag
FROM pg_replication_slots;

Mental Model Summary

This appendix is your field guide:

  • When you need a command, you shouldn’t need to think — just reach for it.
  • PostgreSQL’s introspection tools are not “advanced”; they’re everyday instruments.
  • Observability, tuning, and maintenance aren’t separate disciplines — they’re table stakes for production work.

Suggested Use

Print this appendix as a desk card or load it in a separate terminal tab.
Use it as a reference between theory (modules) and real-world operations.

Further Reading and Sources

<-- Back to 20: Backup, Restore & PITR | Appendix B: Common Failure Scenarios -->