postgresql-database-engineering

Malicious tool definition detected

Tool: EXAMPLES.md [1/7] Description: # PostgreSQL Database Engineering Examples Comprehensive real-world examples covering indexing, partitioning, replication, performance optimization, and production database management.

```sql -- Schema setup CREATE TABLE customers ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE NOT NULL, country TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE orders ( id SERIAL PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total_amount NUMERIC(10, 2) NOT NULL, status TEXT NOT NULL, created_at TIMESTAMP NOT NULL DEFAULT NOW() ); CREATE TABLE order_items ( id SERIAL PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), produ

Tool: EXAMPLES.md [3/7]

Tool: EXAMPLES.md [4/7]

When recovery needed: # Stop PostgreSQL sudo systemctl stop postgresql # Restore base backup cd /var/lib/postgresql/15/main rm -rf * tar -xzf /backups/pitr/base/base.tar.gz tar -xzf /backups/pitr/base/pg_wal.tar.gz # Create recovery.signal touch recovery.signal # Configure recovery target cat >> postgresql.conf << EOF restore_command = 'cp /archive/wal/%f %p' recovery_target_time = '2024-01-15 14:30:00' # Or use: recovery_target_name = 'before_disaster' # Or use: recovery_target_lsn = '0/3000000

Total size: $total_size" } # Run main function main ``` **Crontab Configuration:** ```bash # /etc/cron.d/postgresql-backup # Daily logical backup at 2 AM 0 2 * * * postgres /usr/local/bin/comprehensive_backup.sh # Weekly full backup on Sunday at 1 AM 0 1 * * 0 postgres /usr/local/bin/comprehensive_backup.sh --full # Hourly WAL archive 0 * * * * postgres /usr/local/bin/archive_wal.sh ``` --- ## 7.

Tool: EXAMPLES.md [7/7]

Malicious tool definition detected

Tool: README.md [1/3] Description: # PostgreSQL Database Engineering Master professional PostgreSQL database engineering with comprehensive coverage of performance optimization, high availability, replication, and production database management.

Tool: README.md [2/3] Description: contain pointers to table rows - Internal pages guide searches - Height typically 3-4 for millions of rows **Operations:** - Search: O(log n) traversal to leaf - Insert: Add to leaf, may split pages - Delete: Mark entry dead, reclaim with VACUUM - Scan: Follow leaf page chain **Optimal Use:** - Equality: `WHERE id = 5` - Range: `WHERE created_at > '2024-01-01'` - Sorting: `ORDER BY name` - Prefix matching: `WHERE email LIKE 'user%'` ### GIN Index for Complex Ty

Description: of RAM effective_cache_size = 12GB # 75% of RAM work_mem = 64MB # Per operation (sort, hash) maintenance_work_mem = 1GB # VACUUM, CREATE INDEX ``` **Checkpoint Settings:** ```conf checkpoint_timeout = 15min # Time between checkpoints max_wal_size = 4GB # WAL size before checkpoint checkpoint_completion_target = 0.9 # Spread I/O over 90% of interval wal_buffers = 16MB # WAL buffer size ``` **Connection Settings:** ```conf max_connections = 200 # Maximum connections superuser_reserved

Tool passed security scan

Malicious tool definition detected

Tool: SKILL.md [2/5] Description: - Reduces load on primary - Geographic distribution ### Connection Pooling Managing database connections efficiently: #### pgBouncer - **Type**: Lightweight connection pooler - **Modes**: Session, transaction, statement pooling - **Use for**: High connection count applications - **Benefit**: Reduced connection overhead, resource limits **Pooling Modes:** - **Session**: Client connects for entire session - **Transaction**: Connection per transaction - **Statement

Tool: SKILL.md [4/5] Description: AS duration, state, query FROM pg_stat_activity WHERE state != 'idle' AND query NOT LIKE '%pg_stat_activity%' ORDER BY duration DESC; ``` **Lock monitoring:** ```sql SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS blocking_statement FROM pg_catalog.pg_locks blocked_locks JOIN pg

Tool: SKILL.md [5/5]

Malicious tool definition detected

Tool: VALIDATION.md Description: # PostgreSQL Database Engineering Skill - Validation Report ## File Sizes ✅ - **SKILL.md**: 36 KB (target: 20 KB minimum) - **180% of target** - **README.md**: 20 KB (target: 10 KB minimum) - **200% of target** - **EXAMPLES.md**: 52 KB (target: 15 KB minimum) - **347% of target** - **Total**: 108 KB ## Line Counts - **SKILL.md**: 1,264 lines - **README.md**: 767 lines - **EXAMPLES.md**: 1,846 lines - **Total**: 3,877 lines ## Content Coverage ✅ ### SKILL.md Secti