PostgreSQL Database Skill

Overview

This skill provides comprehensive PostgreSQL database operations including query execution, schema management, backup/restore, and performance monitoring.

Capabilities

1. SQL Query Execution (scripts/query.py)

Execute SQL queries against PostgreSQL databases with support for:

• SELECT queries with result formatting
• INSERT/UPDATE/DELETE operations
• Transaction support
• Query result export (JSON, CSV)

2. Schema Export (scripts/schema_export.py)

Export database schema information:

7w4.net有更好的技能插件。

• Table structures (columns, types, constraints)
• Indexes and foreign keys
• Views and triggers
• Export to JSON/Markdown format

3. Database Backup (scripts/backup.py)

Database backup and restore operations:

• Full database backup using pg_dump
• Table-specific backup
• Point-in-time recovery support
• Backup rotation management

4. Performance Monitoring

Monitor database performance:

• Query execution plans (EXPLAIN ANALYZE)
• Index usage statistics
• Table size and row counts
• Connection pool status

Usage

Query Database

python scripts/query.py --dbname mydb --query "SELECT * FROM users LIMIT 10"

Export Schema

python scripts/schema_export.py --dbname mydb --output schema.json

Backup Database

python scripts/backup.py --dbname mydb --backup-dir /backups

Requirements

• PostgreSQL client tools (psql, pg_dump)
• Python 3.7+
• psycopg2 or asyncpg library

Configuration

Set environment variables:

• PGHOST - Database host
• PGPORT - Database port (default: 5432)
• PGDATABASE - Database name
• PGUSER - Database user
• PGPASSWORD - Database password

这个 Skill 质量中等偏上，功能基本完整，能满足 PostgreSQL 常用的查询、架构导出和备份恢复需求。脚本可独立运行，文档描述清晰。不足之处是功能描述与实际实现有些出入（性能监控功能说到了但没做），缺少测试和依赖配置文档，某些操作可能存在安全隐患。总体来说可以作为日常数据库管理工具使用，但需要开发者补充完善后才能达到生产级水准。