PostgreSQL Recipes

PostgreSQL database administration with psql. Query data, manage users, backups, and server configuration.

8 recipes

pg_dump and pg_restore Workflow

Create reliable database backups with pg_dump and restore them with pg_restore for disaster recovery and environment cloning.

6 steps|10 min|backup, restore, pg_dump

$ pg_dump -U postgres -d mydb -F plain -f /backups/mydb_$(date +%Y%m%d).sql

View Recipe

PgBouncer Connection Pooling Setup

Intermediate

Deploy PgBouncer as a connection pooler to reduce PostgreSQL connection overhead and support thousands of concurrent clients.

5 steps|15 min|pgbouncer, connection-pooling, performance

$ cat > /etc/pgbouncer/pgbouncer.ini << 'INI'

View Recipe

Full-Text Search with tsvector and tsquery

Advanced

Build a powerful full-text search engine using PostgreSQL native tsvector columns, GIN indexes, and ranked query results.

5 steps|18 min|full-text-search, tsvector, tsquery

$ psql -U postgres -d mydb -c "ALTER TABLE articles ADD COLUMN search_vector tsvector GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;"

View Recipe

Schema Migration with psql and Version Control

Intermediate

Manage database schema changes safely using numbered migration files, psql, and version control for reproducible deployments.

5 steps|12 min|migration, schema, version-control

$ psql -U postgres -d mydb -c "CREATE TABLE IF NOT EXISTS schema_migrations (version VARCHAR(14) PRIMARY KEY, applied_at TIMESTAMPTZ DEFAULT NOW(), description TEXT);"

View Recipe

EXPLAIN ANALYZE and Index Optimization

Intermediate

Diagnose slow queries with EXPLAIN ANALYZE and create targeted indexes to dramatically improve PostgreSQL query performance.

5 steps|15 min|performance, explain, indexes

$ psql -U postgres -d mydb -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM orders WHERE customer_id = 1001 AND status = 'pending';"

View Recipe

Streaming Replication Setup

Advanced

Configure PostgreSQL streaming replication with a primary and standby server for high availability and read scaling.

6 steps|20 min|replication, high-availability, streaming

$ psql -U postgres -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'repl_secure_pass';"

View Recipe

Role and Permission Management

Beginner

Create and manage PostgreSQL roles with granular permissions for secure multi-user database access control.

6 steps|10 min|users, roles, permissions

$ psql -U postgres -c "CREATE ROLE app_user WITH LOGIN PASSWORD 'secure_password' VALID UNTIL '2027-01-01';"

View Recipe

Autovacuum Tuning and Maintenance

Intermediate

Configure and monitor PostgreSQL autovacuum to reclaim dead tuples, prevent table bloat, and avoid transaction ID wraparound.

5 steps|12 min|vacuum, autovacuum, maintenance

$ psql -U postgres -c "SELECT name, setting, short_desc FROM pg_settings WHERE name LIKE 'autovacuum%' ORDER BY name;"

View Recipe