skills.rest
Chia1104
Chia1104COMMUNITY
Explore all skills by this author →
Website𝕏@chia7423GitHub

postgresql-backup-restore

Automates verified PostgreSQL and ParadeDB dump and restore workflows with safety guards.

1|Updated Aug 11, 2026
Search Tags:#Docker#postgresql#database migration#restore#backup#pg_dump#paradedb
One-click install
npx skills add https://github.com/Chia1104/agent-air --skill postgresql-backup-restore-chia1104
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgresql-backup-restore
Source: https://github.com/Chia1104/agent-air/tree/main/skills/hermes/database/postgresql-backup-restore
Command: npx skills add https://github.com/Chia1104/agent-air --skill postgresql-backup-restore-chia1104

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill includes references (resource) components.

What problem does it solve? Database migrations and restores often fail silently or overwrite existing data because dumps are unverified, extension-owned tables are mistaken for application data, and restores run without error-stop protection. This Skill provides a disciplined workflow for exporting, validating, and restoring PostgreSQL-compatible databases without data loss. ## Core Features & Use Cases - Safe dump creation: Choose between plain SQL and custom pg_dump formats, write to timestamped .partial files, and verify dump completeness markers before use. - Restore target guarding: Distinguish application-owned tables from extension-owned tables using pg_depend metadata, and abort restores into non-empty targets unless explicitly approved. - ParadeDB handling: Pre-create extensions like pg_search and vector, and avoid extension-schema collisions by excluding paradedb, pgivm, tiger, and topology schemas at dump time. - Post-restore verification: Require zero invalid indexes, zero unvalidated constraints, and exact representative row counts before declaring success. - Use Case: Migrating a Docker-hosted ParadeDB database to a new server—dump with excluded extension schemas, create extensions on the target, restore with ON_ERROR_STOP=1, and validate row counts and index integrity. ## Quick Start Use the postgresql-backup-restore skill to dump my ParadeDB database running in Docker on port 5434 and restore it into a fresh target database with full verification.

Frequently Asked Questions about postgresql-backup-restore

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I safely dump and restore a PostgreSQL database?▼

Use pg_dump with a timestamped .partial output file, verify the dump completion marker and non-zero size, then restore with psql -v ON_ERROR_STOP=1 so the first SQL error halts the run. Always check the target for existing application tables before restoring.

How to migrate a ParadeDB database in Docker?▼

Dump with pg_dump excluding the paradedb, pgivm, tiger, and topology schemas to avoid extension-schema collisions. On the target, run CREATE EXTENSION IF NOT EXISTS for pg_search and vector, then restore the plain SQL dump with ON_ERROR_STOP=1.

Should I use plain SQL or custom format for pg_dump?▼

Plain SQL is transparent and directly restorable with psql, making it ideal for portable full restores. Custom format (pg_dump -Fc) is better when you need selective or parallel restore with pg_restore.

Why does my PostgreSQL restore fail with schema already exists?▼

This happens when a dump contains standalone CREATE SCHEMA statements for extension schemas like paradedb or tiger that already exist on the target. Exclude those schemas at dump time with --exclude-schema flags, since CREATE EXTENSION IF NOT EXISTS alone does not prevent the collision.

How do I verify a PostgreSQL restore completed correctly?▼

Query pg_index for invalid indexes and pg_constraint for unvalidated constraints; both counts must be zero. Also check exact row counts on representative application tables and confirm the expected schemas exist.

Why does a fresh ParadeDB database appear non-empty before restore?▼

Fresh ParadeDB databases contain dozens of extension-owned tables from pg_search, pg_ivm, PostGIS, and vector. Use the pg_depend extension-dependency query to count only application-owned tables; a safe target has zero of those.

Related Skills

Explore More Software Engineering Skills→
SSujitXSSujitX
11

Managed Databases

Provision and manage containerized database instances in Docker environments.

Community
Advanced
wcyganwcygan
3

longhorn-backup-dr

Automate Longhorn backup targets, RecurringJobs, and restores in Kubernetes.

Community
Intermediate
paulanunes85paulanunes85
1

database-management

Automate PostgreSQL and Azure database health checks and connection verification.

Community
Intermediate
rudi-bruchezrudi-bruchez

postgresql-backup-recovery

Manage PostgreSQL backups and recovery with pg_dump, pg_basebackup, and WAL archiving.

Community
Advanced