henriqueatila/golang-gin-best-practices / skills/golang-gin-psql-dba
golang-gin-psql-dba
FlaggedPostgreSQL DBA for Go Gin APIs. Use when designing schemas, analyzing migrations, choosing indexes, optimizing queries, or selecting extensions (pgvector, PostGIS, TimescaleDB).
references/replication-and-ha-logical.md
View on GitHub# Replication and HA — Logical Replication
## When to Use
- Replicate only selected tables (not the entire cluster)
- Replicate between different PostgreSQL major versions (upgrade path)
- Feed changes to a different database system (Kafka, ClickHouse, read replica with different schema)
- Keep a writable subscriber (analytics database enriched with additional data)
## Publication and Subscription Model
The **publisher** (source) creates a publication that lists which tables to stream. The **subscriber** (destination) creates a subscription pointing at the publisher's connection string.
## CREATE PUBLICATION
```sql
-- On the source database (publisher)
-- Replicate only the orders table
CREATE PUBLICATION orders_pub FOR TABLE orders;
-- Replicate multiple tables
CREATE PUBLICATION app_pub FOR TABLE users, orders, products;
-- Replicate all current and future tables (use cautiously)
-- CREATE PUBLICATION all_pub FOR ALL TABLES;
```
Set `wal_level = logical` in the publisher's `postgresql.conf` (requires restart).
## CREATE SUBSCRIPTION
```sql
-- On the destination database (subscriber)
-- Tables must already exist with compatible schema
CREATE TABLE orders (
id UUID PRIMARY KEY,
user_id UUID NOT NULL,
total NUMERIC(19,4) NOT NULL,
status TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL
);
CREATE SUBSCRIPTION orders_sub
CONNECTION 'host=10.0.0.10 port=5432 dbname=mydb user=replicator password=strong_password_here'
PUBLICATION orders_pub;
```
## Limitations
| Limitation | Detail |
|---|---|
| No DDL replication | `ALTER TABLE`, `CREATE INDEX` must be run manually on subscriber |
| No sequence sync | Sequences on subscriber are independent; IDs may diverge |
| Initial data copy | `pg_dump`/`COPY` happens at subscription creation — can be slow for large tables |
| Subscriber is writable | Local writes to subscribed tables can cause conflicts |
| Truncate support | `TRUNCATE` is replicated only if `publish = 'insert, update, delete, truncate'` (default) |
Monitor subscription status:
```sql
-- On subscriber
SELECT subname, received_lsn, latest_end_lsn, latest_end_time
FROM pg_stat_subscription;
```
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 119k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |
| 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: 118k | Updated | Claude CodeCodex | Verified | Install |