Skip to content

LiorCohen/sdd / plugin/fullstack-typescript/skills/components/database/postgresql

postgresql

Flagged

PostgreSQL database agent: deploy locally (Docker/K8s), manage schemas, run queries, analyze performance, configure permissions, and seed data.

resources/administration.md

View on GitHub
# Administration

Maintenance operations, permissions, backup, and data import/export.

---

## Initial Setup

```sql
-- Create database
CREATE DATABASE myapp;

-- Create application role
CREATE ROLE app_role;
GRANT CONNECT ON DATABASE myapp TO app_role;

-- Create user with role
CREATE USER app_user WITH PASSWORD 'secure_password';
GRANT app_role TO app_user;

-- Create schema
\c myapp
CREATE SCHEMA IF NOT EXISTS app;

-- Grant permissions
GRANT USAGE ON SCHEMA app TO app_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app TO app_role;
ALTER DEFAULT PRIVILEGES IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_role;

-- Set search path
ALTER DATABASE myapp SET search_path TO app, public;
```

---

## VACUUM and ANALYZE

```sql
-- Analyze table statistics
ANALYZE app.users;

-- Reclaim dead tuple space
VACUUM app.users;

-- Full vacuum (rewrites table, requires lock)
VACUUM FULL app.users;

-- Vacuum with parallel workers (PG13+)
VACUUM (PARALLEL 4) app.users;
```

## REINDEX

```sql
-- Rebuild index (locks table)
REINDEX INDEX app.idx_users_email;

-- Rebuild concurrently (PG12+, no lock)
REINDEX INDEX CONCURRENTLY app.idx_users_email;
```

## Database Size

```sql
SELECT pg_size_pretty(pg_database_size('myapp')) AS db_size;

-- Table sizes
SELECT
    relname AS table,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY pg_total_relation_size(relid) DESC;
```

---

## Data Export/Import

### Export to CSV

```sql
\copy (SELECT * FROM app.users) TO 'users.csv' WITH (FORMAT csv, HEADER true);
```

### Import from CSV

```sql
\copy app.users (email, name) FROM 'users.csv' WITH (FORMAT csv, HEADER true);
```

### pg_dump / pg_restore

```bash
# Export database
pg_dump -Fc myapp > myapp.dump

# Export specific tables
pg_dump -Fc -t 'app.users' -t 'app.orders' myapp > tables.dump

# Restore
pg_restore -d myapp myapp.dump

# Schema only
pg_dump -s myapp > schema.sql
```
writing-skillsobra/superpowersUse when creating new skills, editing existing skills, or verifying skills work before deploymentStars: 322kUpdated Claude CodeCodexVerifiedInstall
xlsxanthropics/skillsUse this skill any time a spreadsheet file is the primary input or output. This means any task where the user wants to: open, read, edit, or fix an existing .xlsx, .xlsm, .xltx, .csv, or .tsv file (e.g., adding columns, computing formulas, formatting, charting, cleaning messy data); create a new spreadsheet from scratch or from other data sources; or convert between tabular file formats. Trigger especially when the user references a spreadsheet file by name or path — even casually (like "the xlsx in my downloads") — and wants something done to it or produced from it. Also trigger for cleaning or restructuring messy tabular data files (malformed rows, misplaced headers, junk data) into proper spreadsheets. The deliverable must be a spreadsheet file. Do NOT trigger when the primary deliverable is a Word document, HTML report, standalone Python script, database pipeline, or Google Sheets API integration, even if tabular data is involved.Stars: 198kUpdated Claude CodeCodexVerifiedInstall
shipping-and-launchaddyosmani/agent-skillsPrepares production launches. Use when preparing to deploy to production, or when asking what needs to be in place before shipping. Use when you need a pre-launch checklist, when setting up monitoring, when planning a staged rollout, or when you need a rollback strategy.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
childhoodthedotmack/claude-memBabysit a shipped release after merge, publication, or deployment. Verify published packages, production behavior, installed plugins, and late CI or user reports until the observation window is clean; use babysit for PR readiness before release.Stars: 118kUpdated Claude CodeCodexVerifiedInstall