Skip to content

henriqueatila/golang-gin-best-practices / skills/golang-gin-psql-dba

golang-gin-psql-dba

Flagged

PostgreSQL DBA for Go Gin APIs. Use when designing schemas, analyzing migrations, choosing indexes, optimizing queries, or selecting extensions (pgvector, PostGIS, TimescaleDB).

FileKindSize
SKILL.mdskill md9.9 KB
metadata.jsonconfig668 B
references/backup-and-recovery-managed-and-schedules.mdmarkdown4.3 KB
references/backup-and-recovery-strategies.mdmarkdown4.8 KB
references/backup-and-recovery-wal-and-validation.mdmarkdown2.7 KB
references/extensions-toolkit-crypto-and-uuid.mdmarkdown4.1 KB
references/extensions-toolkit-partman-and-trgm.mdmarkdown4.5 KB
references/extensions-toolkit-pgaudit-and-stats.mdmarkdown4.3 KB
references/extensions-toolkit-setup-and-cron.mdmarkdown3.8 KB
references/index-strategy-maintenance-and-explain.mdmarkdown4.4 KB
references/index-strategy-types-and-variants.mdmarkdown3.8 KB
references/migration-impact-backfill-and-lock-timeout.mdmarkdown3.5 KB
references/migration-impact-lock-timeout.mdmarkdown4.6 KB
references/migration-impact-locks-and-operations.mdmarkdown6.4 KB
references/paradedb-advanced-search-and-hybrid.mdmarkdown4.7 KB
references/paradedb-go-integration-and-tips.mdmarkdown4.9 KB
references/paradedb-setup-and-basic-search.mdmarkdown4.5 KB
references/pgvector-capacity-and-performance.mdmarkdown4.3 KB
references/pgvector-queries-and-go-integration.mdmarkdown4.4 KB
references/pgvector-setup-and-schema.mdmarkdown4.9 KB
references/postgis-go-integration-and-performance.mdmarkdown3.8 KB
references/postgis-setup-types-and-indexes.mdmarkdown4.3 KB
references/query-performance-pool-and-replicas.mdmarkdown4.6 KB
references/query-performance-stats-and-vacuum.mdmarkdown4.2 KB
references/replication-and-ha-clusters.mdmarkdown3.4 KB
references/replication-and-ha-go-readwrite.mdmarkdown4.0 KB
references/replication-and-ha-go-resilience.mdmarkdown4.8 KB
references/replication-and-ha-lag-monitoring.mdmarkdown3.3 KB
references/replication-and-ha-logical.mdmarkdown2.2 KB
references/replication-and-ha-managed-services.mdmarkdown2.0 KB
references/replication-and-ha-overview.mdmarkdown3.1 KB
references/replication-and-ha-streaming-setup.mdmarkdown3.6 KB
references/row-level-security-go-integration-and-pitfalls.mdmarkdown5.5 KB
references/row-level-security-setup-and-policies.mdmarkdown5.8 KB
references/schema-design-complete-example.mdmarkdown6.9 KB
references/schema-design-constraints-and-normalization.mdmarkdown4.2 KB
references/schema-design-naming-and-types.mdmarkdown3.9 KB
references/schema-design-soft-delete-and-tenancy.mdmarkdown5.1 KB
references/timescaledb-aggregates-compression-retention.mdmarkdown5.7 KB
references/timescaledb-go-integration.mdmarkdown3.5 KB
references/timescaledb-setup-and-hypertables.mdmarkdown4.8 KB

references/backup-and-recovery-wal-and-validation.md

View on GitHub
# Backup and Recovery — WAL Archiving, PITR, and Validation

## WAL Archiving and PITR

WAL archiving captures every transaction as it happens. Combined with a base backup, it enables Point-in-Time Recovery (PITR).

### postgresql.conf Settings

```ini
wal_level = replica
archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'
archive_timeout = 60   # seconds; limits RPO when idle

# For pgBackRest or WAL-G:
# archive_command = 'pgbackrest --stanza=mydb archive-push %p'
# archive_command = 'wal-g wal-push %p'
```

Requires a server restart after changes.

### Continuous Archiving Setup

```bash
# 1. Create archive directory
mkdir -p /var/lib/postgresql/wal_archive
chown postgres:postgres /var/lib/postgresql/wal_archive
chmod 700 /var/lib/postgresql/wal_archive

# 2. Take a base backup
pg_basebackup -D /var/backups/base -Xs -P -c fast
```

```sql
-- 3. Verify archiving is working
SELECT pg_switch_wal();
SELECT * FROM pg_stat_archiver;
-- Check: last_archived_wal is recent, failed_count = 0
```

### Point-in-Time Recovery (PITR) — PostgreSQL 12+

`recovery.conf` was removed in PG 12. All recovery parameters now live in `postgresql.conf`.

```ini
# postgresql.conf (on the recovery target server)
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-03-04 14:30:00 UTC'
recovery_target_action = 'promote'
```

```bash
# recovery.signal triggers recovery mode (empty file)
touch /var/lib/postgresql/16/main/recovery.signal
```

### Step-by-Step PITR Procedure

**Scenario:** Accidental `DELETE FROM orders WHERE TRUE` at 14:35 UTC. Recover to 14:30 UTC.

```bash
# 1. Stop PostgreSQL on recovery server
systemctl stop postgresql

# 2. Clear the data directory
rm -rf /var/lib/postgresql/16/main/*

# 3. Restore the most recent base backup
tar -xzf /var/backups/base/base.tar.gz -C /var/lib/postgresql/16/main/

# 4. Configure recovery in postgresql.conf
cat >> /var/lib/postgresql/16/main/postgresql.conf <<EOF
restore_command = 'cp /var/lib/postgresql/wal_archive/%f %p'
recovery_target_time = '2026-03-04 14:30:00 UTC'
recovery_target_action = 'promote'
EOF

# 5. Create recovery.signal
touch /var/lib/postgresql/16/main/recovery.signal

# 6. Fix permissions
chown -R postgres:postgres /var/lib/postgresql/16/main

# 7. Start PostgreSQL — replays WAL up to target time, then promotes
systemctl start postgresql

# 8. Verify data
psql -c "SELECT COUNT(*) FROM orders;"

# 9. Remove recovery_target_time from postgresql.conf, then restart
```


> For backup validation (automated restore test script, Go ValidateRestore, pg_verifybackup), see [backup-and-recovery-managed-and-schedules.md](backup-and-recovery-managed-and-schedules.md).
deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks.Stars: 119kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall
agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json.Stars: 118kUpdated Claude CodeCodexVerifiedInstall