메뉴
PostgreSQL operations for backups, restores, PITR, pg_dump, pg_restore, pg_basebackup, pgBackRest, WAL archiving, vacuum, autovacuum, bloat, transaction ID wraparound, maintenance jobs, upgrades, and capacity management.
Codex 또는 Claude로 설치 이 Prompt를 복사해 Codex, Claude 또는 다른 어시스턴트에 붙여 넣으면 Skill 페이지를 검토하고 설치를 진행할 수 있습니다.
SOC 직업 분류 기준
Managed PostgreSQL cloud platforms: Amazon RDS for PostgreSQL, Aurora PostgreSQL, Google Cloud SQL, AlloyDB, Azure Database for PostgreSQL Flexible Server, Supabase, Neon, migrations, cloud limits, backups, monitoring, HA, read replicas, and parameter groups.
PostgreSQL SQL engineering and performance: EXPLAIN plans, indexing, statistics, joins, partitioning, schema design, constraints, JSONB, full text, GIN/GiST/BRIN/B-tree indexes, pgvector, extensions, and query tuning.
PostgreSQL replication and high availability: streaming replication, physical standbys, logical replication, publications, subscriptions, replication slots, WAL shipping, failover, Patroni, repmgr, pg_auto_failover, synchronous commit, and disaster recovery.
PostgreSQL infrastructure and instance configuration: memory, shared_buffers, work_mem, WAL, checkpoints, storage, Linux, containers, Kubernetes, connection pooling, pgbouncer, networking, extensions, configuration files, and server sizing.
PostgreSQL performance monitoring and diagnostics: wait events, pg_stat_activity, pg_stat_database, pg_stat_statements, blocking and deadlock analysis, log analysis, baselining, and health checks. Use for slow queries, high CPU, high I/O, lock waits, deadlocks, replication symptoms, query history, and general PostgreSQL troubleshooting.
PostgreSQL security: authentication, pg_hba.conf, SCRAM, SSL/TLS, roles, privileges, grants, default privileges, row-level security, auditing, pgaudit, encryption, secrets, search_path safety, and hardening.
| name | postgres-operations |
| description | PostgreSQL operations for backups, restores, PITR, pg_dump, pg_restore, pg_basebackup, pgBackRest, WAL archiving, vacuum, autovacuum, bloat, transaction ID wraparound, maintenance jobs, upgrades, and capacity management. |
Focus on recoverability, maintenance safety, and capacity. Always distinguish logical backups from physical backups and require restore validation for production.
age(datfrozenxid) first; treat autovacuum_freeze_max_age approach as urgent and 2-billion approach as an emergency.scripts/) before recommending intrusive commands like VACUUM FULL or REINDEX.| Requirement | Use | Why |
|---|---|---|
| Point-in-time recovery, low RPO | Physical base backup + continuous WAL archiving (pgBackRest, Barman, pg_basebackup + archive) | Only WAL replay gives arbitrary-point recovery |
| Portability across versions/platforms, selective objects | Logical (pg_dump/pg_restore, custom or directory format) | Logical dumps restore into different major versions and subsets |
| Whole-cluster roles and tablespaces in logical form | pg_dumpall --globals-only plus per-database pg_dump | pg_dump never captures roles/tablespaces |
| Very large databases, fast delta backups | pgBackRest incremental/differential, or PG 17+ pg_basebackup --incremental | Full copies stop scaling |
| Managed cloud | Provider snapshots + PITR window, plus logical exports for portability | Filesystem access is unavailable |
A backup that has never been restored is a hope, not a backup. Test restores on a schedule and time them against RTO.
Dead tuples that vacuum cannot remove are almost always held by an xmin horizon: the oldest thing still able to see old row versions. Check in order:
idle in transaction sessions (backend_xmin in pg_stat_activity).xmin/catalog_xmin in pg_replication_slots).pg_prepared_xacts).hot_standby_feedback = on.Only after the horizon is clean does autovacuum tuning matter: lower per-table autovacuum_vacuum_scale_factor for large tables, raise autovacuum_vacuum_cost_limit / lower cost delay for throughput, and add workers only when all workers are consistently busy.
VACUUM FULL for routine bloat; it takes an exclusive lock and rewrites the table. Prefer fixing the cause, then pg_repack if a rewrite is truly needed.pg_dump as a PITR strategy. It is a point-in-time snapshot of one database only.--globals-only roles when restoring a logical dump to a new instance.PGDATA while the server runs without pg_backup_start/backup tooling coordination.autovacuum_freeze_max_age to silence wraparound autovacuums instead of letting them finish.pg_upgrade without --check first, or without analyzing (vacuumdb --analyze-in-stages) after; optimizer statistics do not carry over (before PG 18).VACUUM jobs that overlap and starve autovacuum workers.references/backup-maintenance.md - backup method matrix, PITR workflow, vacuum/bloat remediation, wraparound response, and upgrade paths.../postgres/references/best-practices.md - imported domain-expert backup, vacuum, bloat, and configuration material.../postgres/references/architecture.md - MVCC, WAL, checkpoint, heap, FSM/VM, and XID internals.scripts/01-backup-readiness.sql - archiving and WAL settings.scripts/02-vacuum-health.sql - autovacuum and transaction ID risk.scripts/03-table-bloat-signals.sql - dead tuple and table-size signals.scripts/04-xid-wraparound-risk.sql - database and table freeze-age risk.scripts/05-autovacuum-configuration.sql - global and per-table autovacuum settings.scripts/06-relation-capacity.sql - largest relations, tablespaces, and database sizes.scripts/07-maintenance-progress.sql - active vacuum, analyze, cluster, create-index, and base-backup progress.scripts/08-invalid-unlogged-toast.sql - invalid indexes, unlogged tables, TOAST, and persistence risks.postgres-monitoring.postgres-ha-replication.postgres-infrastructure.postgres-cloud.postgres-engineering.