pg-turbo

Resilient PostgreSQL dump & restore CLI for large databases over flaky connections.

Uses the PostgreSQL COPY protocol directly with chunked streaming, parallel workers, and per-chunk retry. Designed for RDS over VPN, remote servers, and other scenarios where standard pg_dump/pg_restore fail on large schemas.

Why pg-turbo?

Problem with pg_dump	pg-turbo solution
One large table fails = restart entire dump	Per-chunk retry -- only the failed 250MB chunk retries
Can't parallelize within a single table	Sub-table chunking splits large tables across workers
No resume after interruption	.done markers let you pick up where you left off
Connection drops after hours of dumping	TCP keepalive tuning detects drops in ~60s, auto-retry
Slow on remote databases	Direct COPY protocol, no pg_dump process overhead per table

Quick Start

# Install globally
npm install -g pg-turbo

# Dump a database
pg-turbo dump \
    -d "postgresql://user:pass@host:5432/mydb" \
    --output ./mydb_dump \
    -j 4

# Restore to another database
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump \
    -j 4

Requirements

• Node.js >= 20
• pg_dump and pg_restore (for DDL only -- data uses direct COPY)

Features

• Chunked COPY streaming -- large tables split by PK range or ctid range for parallel transfer
• Volume-balanced chunks -- samples row sizes to create equal-byte chunks (handles skewed data)
• Per-chunk retry with exponential backoff + jitter
• Resume support -- skip completed chunks on re-run
• Snapshot consistency -- all workers see identical data via pg_export_snapshot()
• Streaming compression -- zstd (default) or lz4, never buffers full table in memory
• Live progress dashboard -- per-worker status, overall speed, ETA
• Connection resilience -- TCP keepalive tuning, auto-reconnect on failure
• Archive packaging -- optional .pgt single-file format (tar + zstd)
• Connection string cleaning -- strips GUI params (statusColor, env, etc.) from tools like TablePlus

Usage

Dump

# Dump entire database with 4 parallel workers
pg-turbo dump \
    -d "postgresql://user:pass@host:5432/mydb" \
    --output ./mydb_dump \
    -j 4

# Dump specific schema, force chunking at 256MB
pg-turbo dump \
    -d "postgresql://user:pass@host:5432/mydb" \
    --output ./mydb_dump \
    -n public \
    -j 4 \
    --split-threshold 256MB

# Dump from read replica (skip snapshot)
pg-turbo dump \
    -d "postgresql://readonly:pass@replica:5432/mydb" \
    --output ./mydb_dump \
    -j 8 \
    --no-snapshot

# Preview what would be dumped
pg-turbo dump \
    -d "postgresql://user:pass@host:5432/mydb" \
    --output ./mydb_dump \
    --dry-run

Restore

# Restore to target database
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump \
    -j 4

# Clean restore (drop + recreate schemas first)
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump \
    -j 4 -c

# Restore only data (schema already exists)
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump \
    -j 4 -a

# Restore a single table
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump \
    -t users

# Restore from .pgt archive
pg-turbo restore \
    -d "postgresql://user:pass@host:5432/target_db" \
    --input ./mydb_dump.pgt \
    -j 4

Options

dump

Option	Description	Default
-d, --dbname	PostgreSQL connection string	required
--output	Output directory	required
-n, --schema	Dump only this schema	all user schemas
-j, --jobs	Parallel workers	4
--split-threshold	Chunk tables larger than this	1GB
--max-chunks-per-table	Max chunks per table	32
--retries	Max retries per chunk	5
--retry-delay	Base retry delay (seconds)	5
--compression	zstd or lz4	zstd
--no-snapshot	Skip snapshot (for read replicas)
--no-archive	Skip .pgt archive packaging
--dry-run	Preview without dumping

restore

Option	Description	Default
-d, --dbname	PostgreSQL connection string	required
--input	Input directory or .pgt file	required
-n, --schema	Restore only this schema	all
-t, --table	Restore a single table	all
-j, --jobs	Parallel workers	4
-c, --clean	Drop + recreate schemas first
-a, --data-only	Skip DDL, restore only data
--retries	Max retries per chunk	5
--retry-delay	Base retry delay (seconds)	5
--dry-run	Preview without restoring

Passthrough: args after -- are forwarded to pg_dump/pg_restore for DDL operations.

How It Works

Architecture

Coordinator Connection
  BEGIN REPEATABLE READ
  pg_export_snapshot() --> snapshot_id
  (holds transaction open for consistency)
       |
       +-- Worker 1: SET TRANSACTION SNAPSHOT
       |   COPY (SELECT * FROM big_table WHERE id BETWEEN 0 AND 25000) TO STDOUT
       |     --> zstd compress --> chunk_0000.copy.zst
       |
       +-- Worker 2: SET TRANSACTION SNAPSHOT
       |   COPY (SELECT * FROM big_table WHERE id BETWEEN 25001 AND 50000) TO STDOUT
       |     --> zstd compress --> chunk_0001.copy.zst
       |
       +-- Worker 3: SET TRANSACTION SNAPSHOT
       |   COPY small_table TO STDOUT
       |     --> zstd compress --> chunk_0000.copy.zst
       |
       +-- Worker 4: ...

Chunking Strategies

Strategy	When	How
pk_range	Table > threshold, has integer PK	WHERE id BETWEEN start AND end
ctid_range	Table > threshold, no integer PK, PG 14+	WHERE ctid >= '(page,0)'::tid
none	Table < threshold	Single COPY table TO STDOUT

Large tables are split into volume-balanced chunks by sampling row sizes at evenly-spaced PK points. This handles skewed data distributions where some rows are much larger than others.

Resume

Dump: Each completed chunk writes a .done marker file. Re-running the dump skips chunks that already have markers.

Restore: Completed chunks are tracked in a _pg_turbo._progress table in the target database, committed atomically with the data. Survives crashes.

Output Format

dump_dir/
  manifest.json                    # Metadata: tables, chunks, sequences, options
  _schema_ddl.dump                 # pg_dump custom format (DDL only)
  data/
    public.users/
      chunk_0000.copy.zst          # COPY text format, zstd compressed
      chunk_0000.copy.zst.done     # Resume marker
      chunk_0001.copy.zst
      chunk_0001.copy.zst.done
    public.orders/
      chunk_0000.copy.zst
      chunk_0000.copy.zst.done

Benchmarks

On a 144MB test database (500K rows, local Docker):

Method	Time	vs pg_dump
pg_dump -Fc (single)	2.1s	baseline
pg_dump -Fd -j4	1.6s	1.3x
pg-turbo -j1	1.0s	2.1x
pg-turbo -j4	0.8s	2.6x

The speed advantage comes from direct COPY protocol (no per-table pg_dump process spawning) and overlapping DDL dump with data dump. On remote databases over VPN, the advantage is larger due to connection reuse and chunked retry.

Development

npm install          # Install dependencies
npm run typecheck    # TypeScript strict mode check
npm test             # Unit tests (vitest)
npm run test:integration  # Integration tests (requires Docker)
npm run bench        # Performance benchmarks
npm run lint         # Lint (biome)
npm run format       # Format (biome)

Project Structure

src/
  cli/          # CLI concerns (args, orchestration, terminal UI)
  core/         # Reusable logic (connection, streaming, chunking, schema)
  types/        # Shared TypeScript type definitions
tests/
  unit/         # Vitest unit tests
  integration/  # Docker-based end-to-end tests
bench/          # Performance benchmarks

License

AGPL-3.0