pg_restore_fdw

PostgreSQL backup and restore tool that handles Foreign Data Wrapper configurations cleanly across environments.

What It Does

pg_dump and pg_restore are awkward when a database uses Foreign Data Wrappers, because FDW user mappings often embed credentials that need to change between source and destination. This tool splits the dump into three sections and treats them differently so you can rewrite FDW connection details during restore without losing the speed of parallel custom-format restores.

The pre-data section (schemas, FDW server definitions, user mappings) is dumped as plain SQL so credentials can be edited in place. The data and post-data sections are dumped in custom/compressed format, which lets pg_restore run multiple workers in parallel. A built-in progress monitor logs elapsed time and operation status during long-running restores.

The repo also includes a synthetic-data setup path used to benchmark restore performance against tens of millions of rows.

Tech Stack

• Go (single binary), no external Go dependencies
• Shells out to pg_dump and pg_restore
• Standard library only

Installation

git clone https://github.com/niski84/pg_restore_fdw.git
cd pg_restore_fdw
go build -o pg_restore_fdw .

Requires PostgreSQL 12 or later with pg_dump and pg_restore on PATH.

Usage

The exported workflow functions are the API:

sourceConfig := DBConfig{Host: "src.host", Port: "5432", User: "postgres", Password: "...", DBName: "app"}
destConfig   := DBConfig{Host: "dst.host", Port: "5432", User: "postgres", Password: "...", DBName: "app"}

if err := DumpWorkflow(sourceConfig, destConfig, "dump_dir"); err != nil {
    log.Fatal(err)
}
if err := RestoreWorkflow(sourceConfig, destConfig, "dump_dir"); err != nil {
    log.Fatal(err)
}

Edit main.go to point at your databases, or import the package and drive it from your own code. main.go as-shipped runs an end-to-end benchmark (50M synthetic rows) and validates the restored content.

License

MIT