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/replication-and-ha-clusters.md

View on GitHub
# Replication and HA — Patroni and pg_auto_failover

## High Availability with Patroni

Patroni is a Python daemon wrapping each PostgreSQL node with:
- **Leader election** via DCS: etcd, Consul, or ZooKeeper
- **Automatic failover** — promotes the most up-to-date standby
- **Configuration management** — applies postgresql.conf changes cluster-wide
- **REST API** — `/primary`, `/replica`, `/health` endpoints for load balancers

### Architecture

```
Application
    |
    v
HAProxy (reads /primary endpoint)
    |        \
    v          v
Patroni+PG   Patroni+PG   Patroni+PG
(primary)    (standby)    (standby)
    |              |            |
    +------etcd cluster---------+
```

### Docker Compose: 3-Node Patroni Cluster

Services: `etcd` (v3.5, DCS), `patroni1/2/3` (image `patroni:latest` from zalando/patroni — env: `PATRONI_NAME`, `PATRONI_POSTGRESQL_CONNECT_ADDRESS`, `PATRONI_RESTAPI_CONNECT_ADDRESS`, `PATRONI_ETCD_URL`), `haproxy:2.9` (ports 5432=primary, 5433=replicas, 7000=stats). All on `patroni_net` bridge network.

```ini
# haproxy.cfg
frontend pg_primary
    bind *:5432
    default_backend pg_primary_backend

frontend pg_replica
    bind *:5433
    default_backend pg_replica_backend

backend pg_primary_backend
    option httpchk GET /primary
    http-check expect status 200
    server patroni1 patroni1:5432 check port 8008 inter 2s fall 3 rise 2
    server patroni2 patroni2:5432 check port 8008 inter 2s fall 3 rise 2
    server patroni3 patroni3:5432 check port 8008 inter 2s fall 3 rise 2

backend pg_replica_backend
    option httpchk GET /replica
    http-check expect status 200
    balance roundrobin
    server patroni1 patroni1:5432 check port 8008 inter 2s fall 3 rise 2
    server patroni2 patroni2:5432 check port 8008 inter 2s fall 3 rise 2
    server patroni3 patroni3:5432 check port 8008 inter 2s fall 3 rise 2
```

Application DSNs (static, HAProxy handles routing):

```
PRIMARY_DSN=postgres://app:secret@haproxy:5432/mydb
REPLICA_DSN=postgres://app:secret@haproxy:5433/mydb
```

---

## pg_auto_failover

`pg_auto_failover` provides automatic failover for a **2-node** setup via a dedicated monitor node.

```
Monitor (pgautofailover extension)
    |               |
    v               v
Primary            Secondary
(read/write)       (read-only hot standby)
```

### Setup

```bash
# On monitor host
pg_autoctl create monitor --pgdata /data/monitor --auth trust --ssl-self-signed

# On primary host
pg_autoctl create postgres \
  --pgdata /data/primary \
  --monitor postgres://autoctl_node@monitor:5432/pg_auto_failover \
  --name primary --auth trust

# On secondary host
pg_autoctl create postgres \
  --pgdata /data/secondary \
  --monitor postgres://autoctl_node@monitor:5432/pg_auto_failover \
  --name secondary --auth trust

pg_autoctl run --pgdata /data/primary &
pg_autoctl run --pgdata /data/secondary &

# Check status
pg_autoctl show state --pgdata /data/monitor
```

### pg_auto_failover vs Patroni

| Concern | pg_auto_failover | Patroni |
|---|---|---|
| Setup complexity | Low — 3 commands | High — YAML config + DCS |
| External dependencies | None beyond PostgreSQL | etcd / Consul / ZooKeeper |
| Node count | 2 nodes + 1 monitor | 3+ nodes recommended |
| Failover time | ~30 seconds default | ~10–30 seconds tunable |
| Multi-datacenter | Limited | Full support |
| Best for | Simple 2-node setups | Production clusters with DCS already available |
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