pgtransfer

command module
v1.2.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Oct 17, 2026 License: MIT Imports: 3 Imported by: 0

README

PGTransfer

Go Version PostgreSQL CI License

PGTransfer is a powerful, secure, and user-friendly command-line tool for PostgreSQL data operations. Built with Go, it provides seamless data import/export capabilities with advanced features like SSH tunneling, connection profiles, and interactive configuration.

🎯 Key Highlights

  • 🔐 Secure SSH Tunneling: Connect to remote databases through bastion hosts with full SSH tunnel support and local port forwarding
  • ⚡ Efficient Operations: Optimized batch processing that handles large datasets while managing memory usage intelligently
  • 🛡️ Robust Security: SSL/TLS encryption, SSH key authentication, and secure local credential storage
  • 🔄 Database Migration: Complete schema and data migration between databases with automatic rollback capabilities
  • 📊 Intelligent Data Processing: Proper handling of PostgreSQL data types with smart CSV formatting and conversion
  • 🤖 AI & Automation Ready: Machine-readable --json output for every command and a built-in Model Context Protocol (MCP) server so agents can drive transfers directly

✨ Features

Core Operations
  • 🔄 Data Transfer: Import and export PostgreSQL tables to/from CSV format with intelligent data type handling
  • 🗄️ Database Dumps: Complete database export/import using PostgreSQL's native tools (pg_dump/pg_restore)
  • 🗄️ Database Migration: Full database migration with schema, data, and selective table transfer
  • 📊 Progress Tracking: Real-time progress indicators with speed metrics and time estimates
Connection & Security
  • 🔐 SSH Tunneling: Production-ready SSH tunnels with local port forwarding for external tools
  • 🛡️ SSL/TLS Support: Configurable SSL modes for secure database connections
  • 🔑 Authentication: Support for password, SSH key, and SSH agent authentication
  • 👤 Profile Management: Reusable connection profiles with secure credential storage
  • 🖥️ Interactive Setup: User-friendly interactive profile configuration with validation
Automation & AI
  • 🤖 JSON Output: A global --json flag (or PGTRANSFER_JSON=1) emits a single, stable JSON envelope for every command
  • 🔌 MCP Server: pgtransfer mcp runs a Model Context Protocol server over stdio, exposing transfers as typed tools for AI hosts
  • 🔒 Secret Redaction: JSON output never includes passwords — profiles report has_password instead

🤖 AI & Automation

PGTransfer is designed to be driven by scripts, CI pipelines, and AI agents.

Machine-readable JSON output

Pass the global --json flag (before the subcommand) to any command to receive a single JSON object on stdout instead of human-friendly text:

pgtransfer --json version
pgtransfer --json profile list
pgtransfer --json test-connection myprofile
pgtransfer --json export csv myprofile out.csv --table users --headers

The flag can also be enabled with an environment variable, which is convenient for agents that cannot easily change the command line:

export PGTRANSFER_JSON=1
pgtransfer version        # emits JSON

The envelope is identical for every command:

{
  "ok": true,
  "command": "export csv",
  "data": { "rows": 250, "output_file": "out.csv" },
  "error": null
}

On failure ok is false, data is omitted, and error carries a stable machine-readable code:

{
  "ok": false,
  "command": "test-connection",
  "error": {
    "code": "profile_not_found",
    "message": "profile \"ghost\" not found"
  }
}

The process always exits non-zero on failure, and exactly one JSON object is written per invocation — including for parser-level problems such as an unknown subcommand or a bad flag (code: "usage_error").

Stable error codes include: usage_error, profile_not_found, no_active_profile, config_load_failed, connection_failed, export_failed, import_failed, dump_failed, restore_failed, migration_failed, rollback_failed, file_exists, file_not_found, truncate_failed, invalid_input.

MCP server (AI agent integration)

pgtransfer mcp runs a Model Context Protocol server over stdio. AI hosts (Claude Desktop, Cursor, custom agent runtimes) can then call pgtransfer capabilities as native typed tools.

pgtransfer mcp

Register it with an MCP host — for Claude Desktop, add this to claude_desktop_config.json:

{
  "mcpServers": {
    "pgtransfer": {
      "command": "pgtransfer",
      "args": ["mcp"]
    }
  }
}

Exposed tools

Tool Purpose
list_profiles List saved profiles (never returns passwords)
test_connection Test connectivity, optionally through an SSH tunnel
export_csv Export a table or a custom query to CSV
import_csv Import a CSV into a table, optionally truncating first
export_dump Create a pg_dump backup (plain/custom/tar/directory)
import_dump Restore a dump, tolerating benign version skew by default
migrate_database Migrate schema/data between databases or profiles

Tool failures are reported in-band with isError: true and a structured {"ok": false, "error": {"code", "message"}} payload, so a model can read the problem and correct itself rather than seeing an opaque protocol error.

Because stdout carries the MCP protocol, the server redirects any incidental library output to stderr; only protocol frames are ever written to stdout.

🔐 SSH Tunnel Support

PGTransfer includes comprehensive SSH tunnel support with automatic port forwarding, allowing secure access to remote PostgreSQL databases through bastion hosts or jump servers.

Key Features
  • 🔄 Automatic Port Management: Dynamically allocates local ports and detects conflicts
  • 🔐 Multiple Authentication: Supports password, SSH key, and SSH agent authentication
  • 🛠️ External Tool Integration: Creates local port forwarding that works with pgAdmin, DBeaver, and other tools
  • ⚡ Connection Pooling: Reuses tunnel connections efficiently across multiple operations
  • 🔍 Health Monitoring: Automatically monitors tunnel health and recovers from connection issues
SSH Configuration Options
Option Description Default
SSH Host Remote server hostname/IP Required
SSH Port SSH server port 22
SSH Username SSH user account Current user
Authentication password/key/agent Interactive
Local Port Local tunnel port Auto-assigned
Keep Alive Tunnel persistence 30s
Authentication Methods

1. SSH Key Authentication (recommended)

# Use specific private key
SSH Key Path: ~/.ssh/production_key

# Use default SSH key
SSH Key Path: ~/.ssh/id_rsa

2. SSH Agent Authentication

# Uses keys loaded in SSH agent
ssh-add ~/.ssh/production_key
# Then select "SSH Agent" in profile setup

3. Password Authentication

# Interactive password prompt
# Less secure, better to use keys when possible
Example Setup
# Create production profile with SSH tunnel
pgtransfer profile add prod-db

# Configuration:
# SSH Host: bastion.company.com
# SSH Username: deploy
# SSH Key: ~/.ssh/production_key
# Database Host: localhost (via tunnel)
# Database Port: 5433 (tunnel local port)
# Target DB: db-internal.company.com:5432
External Tool Integration

Once a profile with SSH tunnel is configured, you can use the local port for external tools:

# Get tunnel info
pgtransfer test-connection prod-db

# Use with external tools
pgadmin4 --host localhost --port 5433
psql -h localhost -p 5433 -U username -d database
Performance & Reliability
  • ⚡ Batch Processing: High-performance batch operations with configurable batch sizes
  • 💾 Memory Efficient: Streaming processing optimized for large datasets
  • 🔄 Rollback Support: Automatic backup creation and rollback capabilities for migrations
  • ⏱️ Timeout Control: Configurable timeouts for long-running operations
  • 📝 Comprehensive Logging: Detailed JSON-formatted operation logs with performance metrics

🚀 Quick Start

Prerequisites
  • Go: Version 1.25 or higher
  • PostgreSQL: Version 13 or higher (client tools: pg_dump, pg_restore, psql)
  • SSH Client: For SSH tunnel connections (OpenSSH recommended)
Installation
Option A — prebuilt binary (no Go required)

Download the archive for your platform from the releases page:

Platform Asset
macOS (Apple Silicon) pgtransfer-<version>-darwin-arm64.tar.gz
macOS (Intel) pgtransfer-<version>-darwin-amd64.tar.gz
Linux x86-64 pgtransfer-<version>-linux-amd64.tar.gz
Linux ARM64 pgtransfer-<version>-linux-arm64.tar.gz

For example, on macOS (Apple Silicon):

V=v1.1.0
A=darwin-arm64
curl -fsSLO "https://github.com/andymarthin/pgtransfer/releases/download/$V/pgtransfer-$V-$A.tar.gz"
tar xzf "pgtransfer-$V-$A.tar.gz"
sudo mv pgtransfer /usr/local/bin/
pgtransfer version

Optionally verify the download against the published checksums:

curl -fsSLO "https://github.com/andymarthin/pgtransfer/releases/download/$V/SHA256SUMS"
shasum -a 256 -c SHA256SUMS --ignore-missing   # Linux: sha256sum -c ...
Option B — install script (Linux, macOS)

A self-contained shell installer downloads the right binary, verifies it against the release's SHA256SUMS, and installs it.

curl -fsSL https://raw.githubusercontent.com/andymarthin/pgtransfer/main/scripts/install.sh | sh

It installs to /usr/local/bin when writable, otherwise to ~/.local/bin (and tells you how to add that to your PATH if needed).

Control the install with environment variables:

# pin a version
curl -fsSL https://raw.githubusercontent.com/andymarthin/pgtransfer/main/scripts/install.sh \
  | PGTRANSFER_VERSION=v1.1.0 sh

# choose the install directory (no sudo needed)
curl -fsSL https://raw.githubusercontent.com/andymarthin/pgtransfer/main/scripts/install.sh \
  | PGTRANSFER_INSTALL="$HOME/bin" sh

# never prompt for sudo (fail instead)
curl -fsSL https://raw.githubusercontent.com/andymarthin/pgtransfer/main/scripts/install.sh \
  | PGTRANSFER_NO_SUDO=1 sh
Variable Purpose
PGTRANSFER_VERSION Release tag to install, e.g. v1.1.0 (default: latest)
PGTRANSFER_INSTALL Install directory (default: /usr/local/bin, else ~/.local/bin)
PGTRANSFER_NO_SUDO Set to 1 to never use sudo

The installer requires tar and one of curl/wget, plus sha256sum, shasum or openssl to verify the download. If it cannot verify the archive against the published checksum, it refuses to install it.

Prefer to read before you pipe to a shell? Inspect it first:

curl -fsSL https://raw.githubusercontent.com/andymarthin/pgtransfer/main/scripts/install.sh | less
Option C — Homebrew (macOS and Linux)

Requires Homebrew. Installs the prebuilt binary for your platform.

brew tap andymarthin/pgtransfer
brew install pgtransfer

Or in a single command:

brew install andymarthin/pgtransfer/pgtransfer

Upgrade later with:

brew update && brew upgrade pgtransfer
Option D — build from source

Requires Go 1.25 or higher.

git clone https://github.com/andymarthin/pgtransfer.git
cd pgtransfer
go mod tidy
go build -o pgtransfer .
sudo mv pgtransfer /usr/local/bin/     # or: export PATH="$PATH:$PWD"
Option E — go install

Requires Go 1.25 or higher. Installs into $(go env GOPATH)/bin, which must be on your PATH.

go install github.com/andymarthin/pgtransfer@latest
Verify
pgtransfer version          # e.g. "pgtransfer v1.1.0"
pgtransfer --help

📋 Usage

Command Overview
# Profile Management
pgtransfer profile add <name>           # Create new connection profile
pgtransfer profile delete <name>        # Delete existing profile
pgtransfer profile list                 # List all profiles

# Connection Testing
pgtransfer test-connection <profile>    # Test database connection

# Data Export
pgtransfer export csv <profile> --table <table> --output <file.csv>
pgtransfer export dump <profile> --output <file.sql> [--format custom|directory|plain]

# Data Import  
pgtransfer import csv <profile> --table <table> --input <file.csv>
pgtransfer import dump <profile> --input <file.sql>

# Database Migration
pgtransfer migrate <source> <target> [--mode schema|data|full] [--tables table1,table2]

# Migration Rollback
pgtransfer migrate rollback <profile> --backup-file <backup.sql>

# AI Agent Integration (Model Context Protocol server over stdio)
pgtransfer mcp

Tip: every command above also accepts the global --json flag for machine-readable output. See AI & Automation.

Quick Examples
# 1. Set up a new profile with SSH tunnel
pgtransfer profile add production
# Follow interactive prompts for SSH and database configuration

# 2. Export production data
pgtransfer export csv production --table users --output users.csv --batch-size 10000

# 3. Migrate to development environment
pgtransfer migrate production development --mode schema

# 4. Create full database backup
pgtransfer export dump production --output backup_$(date +%Y%m%d).sql --format custom
Profile Management

PGTransfer uses connection profiles to manage database credentials and settings securely.

Create a New Profile (Interactive)
pgtransfer profile add myprofile

The interactive setup guides you through comprehensive configuration:

1. SSH Tunnel Configuration (optional but recommended for remote databases)

Enable SSH tunnel? [y/N]: y
SSH Host: bastion.example.com
SSH Port [22]: 22
SSH Username: deploy
SSH Authentication:
  1. Password
  2. SSH Key
  3. SSH Agent
Choose [1-3]: 2
SSH Key Path: ~/.ssh/id_rsa
Local Port [5433]: 5433

2. Database Configuration

Database Host: localhost          # Use localhost when using SSH tunnel
Database Port [5432]: 5433       # Use tunnel local port
Database Name: production_db
Username: app_user
Password: [hidden input]
SSL Mode:
  1. disable
  2. require  
  3. verify-ca
  4. verify-full
Choose [1-4]: 2

3. Automatic Validation

  • SSH tunnel connectivity test
  • Database connection verification
  • Configuration validation and storage
Create a Profile (Command Line)
pgtransfer profile add production \
  --user myuser \
  --password mypassword \
  --host db.example.com \
  --port 5432 \
  --database myapp \
  --sslmode require
SSH Tunnel Configuration

For secure connections through a bastion host:

pgtransfer profile add staging \
  --user dbuser \
  --host localhost \
  --port 5432 \
  --database staging_db \
  --ssh-host bastion.example.com \
  --ssh-user ubuntu \
  --ssh-key ~/.ssh/id_rsa
List Profiles
pgtransfer profile list
Test Connection
pgtransfer test-connection myprofile
Update Existing Profile

When updating an existing profile, PGTransfer will show current values as defaults:

pgtransfer profile add myprofile

⚠️  Profile 'myprofile' already exists with the following configuration:
  Database: myuser@db.example.com:5432/myapp
  SSL Mode: require
  SSH: Not configured

Do you want to overwrite it? [y/N]: y

🧩 Interactive Profile Setup
Database user [myuser]: newuser
Database password: 
Database host [db.example.com]: 
Database port [5432]: 
Database name [myapp]: 
SSL mode [require]: 
Use SSH tunnel? (y/N) [n]:
Data Operations
Export Data

Export a table to CSV:

pgtransfer export csv myprofile public.users users_export.csv --headers

Export with schema flag (defaults to 'public' if not specified):

pgtransfer export csv myprofile users users_export.csv --headers --schema public

With custom batch size for large datasets:

pgtransfer export csv myprofile public.users large_export.csv --headers --batch-size 1000
Database Dump Export

Export complete database to SQL dump:

pgtransfer export dump myprofile database_backup.sql

Export with custom format and compression:

pgtransfer export dump myprofile backup.dump --format custom --compress

Export schema only (no data):

pgtransfer export dump myprofile schema_backup.sql --schema-only

Export data only (no schema):

pgtransfer export dump myprofile data_backup.sql --data-only

Export specific tables:

pgtransfer export dump myprofile users_backup.sql --table users --table orders

Export with advanced options:

pgtransfer export dump myprofile full_backup.dump \
  --format custom \
  --compress \
  --verbose \
  --timeout 300

Supported Formats:

  • plain (default): Standard SQL text format
  • custom: PostgreSQL custom binary format (supports compression)
  • directory: Directory format for parallel processing
  • tar: TAR archive format
Import Data

Import CSV data to a table:

pgtransfer import csv myprofile public.users users_import.csv --headers

Import with schema flag (defaults to 'public' if not specified):

pgtransfer import csv myprofile users users_import.csv --headers --schema public

Import with custom batch size and overwrite existing data:

pgtransfer import csv myprofile public.users users_import.csv --headers --batch-size 1000 --overwrite

Import a header-less CSV (columns are mapped positionally using the target table definition):

pgtransfer import csv myprofile public.users users_no_header.csv

Headers behaviour: when --headers is supplied the first CSV row is treated as the column name list; when omitted, every row is treated as data and the columns are matched positionally against the target table.

Import from SQL dump file (automatically detects plain SQL vs. binary pg_dump formats):

pgtransfer import dump myprofile backup.sql

Import a custom-format dump and drop existing objects first:

pgtransfer import dump myprofile backup.dump --clean

Import with a timeout and strict error handling:

pgtransfer import dump myprofile backup.sql --timeout 300 --strict

Version-skew tolerance: by default, restore tolerates benign errors caused by client/server version differences (for example an unrecognized configuration parameter emitted by a newer pg_dump). Pass --strict to abort on the first error instead.

Database Migration

PGTransfer provides comprehensive database migration capabilities for transferring entire databases or specific components between PostgreSQL instances. You can use either different profiles or the same profile with database overrides.

Migration Modes

Mode 1: Different Profiles Use separate connection profiles for source and target:

pgtransfer migrate database source_profile target_profile

Mode 2: Same Profile with Database Override Use the same connection profile but specify different database names:

pgtransfer migrate database profile --source-database source_db --target-database target_db
Full Database Migration

Migrate complete database (schema + data) with different profiles:

pgtransfer migrate database source_profile target_profile

Migrate complete database using same profile:

pgtransfer migrate database myprofile --source-database prod_db --target-database staging_db
Schema-Only Migration

Transfer only the database structure with different profiles:

pgtransfer migrate database source_profile target_profile --schema-only

Transfer only the database structure using same profile:

pgtransfer migrate database myprofile --source-database prod_db --target-database dev_db --schema-only
Data-Only Migration

Transfer only data (assumes target schema exists) with different profiles:

pgtransfer migrate database source_profile target_profile --data-only

Transfer only data using same profile:

pgtransfer migrate database myprofile --source-database source_db --target-database target_db --data-only
Selective Table Migration

Migrate specific tables with different profiles:

pgtransfer migrate database source_profile target_profile --tables users,orders,products

Migrate specific tables using same profile:

pgtransfer migrate database myprofile --source-database db1 --target-database db2 --tables users,orders,products
Migration with Validation

Pre-validate migration before execution with different profiles:

pgtransfer migrate database source_profile target_profile --validate --verbose

Pre-validate migration using same profile:

pgtransfer migrate database myprofile --source-database source_db --target-database target_db --validate --verbose
Migration with Rollback Support

Enable automatic backup for rollback capability with different profiles:

pgtransfer migrate database source_profile target_profile --enable-rollback

Enable automatic backup using same profile:

pgtransfer migrate database myprofile --source-database source_db --target-database target_db --enable-rollback
Advanced Migration Options

With different profiles:

pgtransfer migrate database source_profile target_profile \
  --tables users,orders \
  --validate \
  --enable-rollback \
  --timeout 1800 \
  --batch-size 1000 \
  --verbose

With same profile and database override:

pgtransfer migrate database myprofile \
  --source-database source_db \
  --target-database target_db \
  --tables users,orders \
  --validate \
  --enable-rollback \
  --timeout 1800 \
  --batch-size 1000 \
  --verbose
Rollback Operations

Rollback a migration using a backup file:

pgtransfer migrate rollback target_profile /path/to/backup.sql --verbose
Migration Features
  • 🔍 Pre-Migration Validation: Connection testing and schema compatibility checks
  • 📊 Progress Tracking: Real-time progress indicators with elapsed time
  • 🔄 Rollback Support: Automatic backup creation for safe rollbacks
  • ⚙️ Flexible Options: Schema-only, data-only, or selective table migration
  • 🛡️ Error Handling: Comprehensive error reporting and graceful failure handling
  • ⏱️ Timeout Control: Configurable timeouts for long-running migrations
  • 📝 Verbose Logging: Detailed operation logs for troubleshooting
Migration Examples

Production Database Refresh (Different Profiles):

# Create backup and migrate with rollback support
pgtransfer migrate database production staging --enable-rollback --verbose

# If issues occur, rollback
pgtransfer migrate rollback staging /path/to/backup_20241028_092222.sql

Production Database Refresh (Same Profile):

# Create backup and migrate with rollback support using same profile
pgtransfer migrate database myprofile \
  --source-database prod_db \
  --target-database staging_db \
  --enable-rollback --verbose

# If issues occur, rollback
pgtransfer migrate rollback staging /path/to/backup_20241028_092222.sql

Development Environment Setup (Different Profiles):

# Schema-only migration for development
pgtransfer migrate database production dev --schema-only

# Add sample data separately
pgtransfer migrate database sample_data dev --data-only --tables users,products

Development Environment Setup (Same Profile):

# Schema-only migration for development using same profile
pgtransfer migrate database myprofile \
  --source-database production_db \
  --target-database dev_db \
  --schema-only

# Add sample data separately
pgtransfer migrate database myprofile \
  --source-database sample_data_db \
  --target-database dev_db \
  --data-only --tables users,products

Selective Data Migration (Different Profiles):

# Migrate specific tables with validation
pgtransfer migrate database prod_replica analytics \
  --tables user_events,transactions,metrics \
  --validate \
  --timeout 3600

Selective Data Migration (Same Profile):

# Migrate specific tables with validation using same profile
pgtransfer migrate database myprofile \
  --source-database prod_replica_db \
  --target-database analytics_db \
  --tables user_events,transactions,metrics \
  --validate \
  --timeout 3600

⚡ Performance & Optimization

PGTransfer is designed for efficient data operations with intelligent batch processing, streaming architecture, and automatic resource management.

Performance Features
  • 🔄 Streaming Processing: Handles datasets larger than available memory
  • 🧠 Adaptive Batching: Adjusts batch sizes dynamically based on data complexity
  • ⚡ Connection Pooling: Reuses database connections efficiently
  • 📊 Real-time Metrics: Provides live performance monitoring with time estimates
  • 💾 Memory Efficiency: Maintains minimal memory usage regardless of dataset size
Batch Size Optimization

Default batch size: 1000 rows - works well for most environments and use cases.

Performance Benchmarks (1M Records, Mixed Data Types)
Batch Size Throughput Memory Usage Best For
500 4,200 rec/sec 35MB Memory-constrained systems
1,000 (default) 6,800 rec/sec 65MB ✅ Optimal for most cases
5,000 8,500 rec/sec 180MB High-performance networks
10,000 9,200 rec/sec 320MB Maximum throughput scenarios
25,000 8,800 rec/sec 750MB Dedicated database servers
Production Examples
Large Table Export
# Standard high-performance export
pgtransfer export csv production --table transactions \
  --output transactions.csv \
  --batch-size 10000 \
  --progress

# Memory-optimized export for constrained environments  
pgtransfer export csv production --table events \
  --output events.csv \
  --batch-size 2500 \
  --progress

# Maximum throughput for dedicated servers
pgtransfer export csv production --table logs \
  --output logs.csv \
  --batch-size 25000 \
  --progress
Large Dataset Import
# Optimized import with progress tracking
pgtransfer import csv staging --table transactions \
  --input transactions.csv \
  --batch-size 5000 \
  --progress

# Safe import with validation
pgtransfer import csv production --table users \
  --input users.csv \
  --batch-size 1000 \
  --validate \
  --progress

# High-speed import for development
pgtransfer import csv dev --table test_data \
  --input large_dataset.csv \
  --batch-size 15000 \
  --skip-validation \
  --progress
Database Migration Performance
# Schema-only migration (fast)
pgtransfer migrate production staging --mode schema

# Data migration with optimization
pgtransfer migrate production staging \
  --mode data \
  --batch-size 10000 \
  --progress

# Selective table migration
pgtransfer migrate production staging \
  --tables users,orders,products \
  --batch-size 5000 \
  --progress
Real-time Progress Tracking

All operations provide comprehensive progress monitoring:

  • 📊 Visual Progress Bar: Real-time completion percentage
  • ⚡ Performance Metrics: Throughput (records/second)
  • ⏱️ Time Tracking: Elapsed time and ETA calculations
  • 💾 Resource Monitoring: Memory usage and batch efficiency
  • 🔍 Operation Details: Current batch, total records, and status
Example Output

CSV Export Progress:

Exporting table 'transactions'...
Progress: 75% |███████████████     | 750,000/1,000,000 records
Speed: 8,500 rec/sec | Elapsed: 1m 28s | ETA: 29s
Batch: 750 (size: 10,000) | Memory: 145MB

Migration Progress:

Migrating database: production → staging
Schema migration: ✅ Complete (2.3s)
Data migration: 45% |█████████       | 4.5M/10M records  
Speed: 12,300 rec/sec | Elapsed: 6m 12s | ETA: 7m 30s
Current table: orders (batch 450/892)

SSH Tunnel Status:

🔐 SSH Tunnel: bastion.company.com:22 → localhost:5433
Status: ✅ Connected | Uptime: 5m 23s
Database: ✅ Connected via tunnel
Memory Management

PGTransfer implements intelligent memory management:

  • Streaming Processing: Data is processed in chunks, not loaded entirely into memory
  • Connection Pooling: Efficient database connection reuse
  • Garbage Collection: Automatic cleanup of processed batches
  • Resource Monitoring: Built-in memory usage tracking

📊 CSV Data Type Handling

PGTransfer provides intelligent CSV formatting that properly handles PostgreSQL data types for seamless import/export operations.

Supported Data Types
PostgreSQL Type CSV Format Example
DATE YYYY-MM-DD 2008-07-06
TIMESTAMP YYYY-MM-DD HH:MM:SS 2025-10-28 01:59:38
NUMERIC(p,s) Decimal notation 63942.00
INTEGER Plain number 457719
VARCHAR/TEXT Quoted strings user_0457719
BOOLEAN true/false true
BYTEA String representation Converted to readable format
Key Improvements
✅ Date and Timestamp Formatting
  • Before: 1960-01-02 00:00:00 +0000 +0000 (with timezone info)
  • After: 1960-01-02 (clean date format)
  • Timestamp: 2025-10-28 01:59:38 (without timezone)
✅ Decimal and Numeric Handling
  • Before: [49 51 57 52 50 46 48 48] (byte array representation)
  • After: 63942.00 (proper decimal format)
✅ Null Value Handling
  • Consistent: Empty strings for NULL values across all data types
  • Import Compatible: Properly recognized during CSV import operations
CSV Export Examples
Standard Table Export
# Export with proper formatting
pgtransfer export csv myprofile public.users users.csv --headers
Custom Query Export
# Export specific columns with formatting
pgtransfer export csv myprofile results.csv \
  --query "SELECT id, name, date_of_birth, salary FROM users WHERE active = true" \
  --headers
CSV Import Compatibility

The improved formatting ensures seamless round-trip operations:

# Export data
pgtransfer export csv source_profile public.users exported_data.csv --headers

# Import to different database
pgtransfer import csv target_profile public.users_copy exported_data.csv --headers

Result: All data types are preserved correctly without manual formatting adjustments.

Advanced Usage
Override Profile Database
pgtransfer export --profile myprofile --database different_db --table users --file export.csv
Skip Connection Testing
pgtransfer profile add myprofile --skip-test
Force Profile Overwrite
pgtransfer profile add myprofile --force

🔧 Configuration

Configuration File

Profiles are stored in ~/.pgtransfer/config.yaml:

profiles:
  local:
    name: local
    user: postgres
    host: localhost
    port: 5432
    database: myapp_dev
    sslmode: disable
    ssh:
      enabled: false
  
  production:
    name: production
    user: app_user
    host: localhost
    port: 5432
    database: myapp_prod
    sslmode: require
    ssh:
      enabled: true
      host: bastion.example.com
      user: ubuntu
      port: 22
      key_path: /home/user/.ssh/id_rsa
      timeout: 10
Logging

Logs are automatically saved to ~/.pgtransfer/logs/ in JSON format:

{
  "timestamp": "2024-01-15T10:30:45Z",
  "level": "info",
  "operation": "export",
  "profile": "production",
  "table": "users",
  "file": "users_export.csv",
  "status": "success",
  "duration_ms": 1250,
  "rows_processed": 10000
}

🛠️ Development & Testing

Development Setup
# Clone and setup
git clone https://github.com/andymarthin/pgtransfer.git
cd pgtransfer

# Install dependencies
go mod tidy

# Build for development
go build -o pgtransfer .

# Fast unit tests (no database required)
go test ./...

# Run linting
go vet ./...
staticcheck ./...
Test Suite

The project has two layers of tests:

Layer Command Requires Scope
Unit go test ./... nothing Pure logic: DSN building, config lifecycle, dump-format detection, CSV value formatting, SSH auth selection, duration formatting
Integration go test -tags=integration ./internal/io/... a live PostgreSQL + SSH server Real round-trips: pg_dump/pg_restore, CSV export/import, and the SSH tunnel path

Unit tests run in CI on every push and pull request. Integration tests run in a separate CI job that provisions PostgreSQL and the repository SSH image.

Running the integration tests

Integration tests are guarded by the integration build tag and read their settings from environment variables. They skip when the prerequisites are absent — so day-to-day go test ./... never needs a database:

# 1. Start the local test stack
cd scripts && docker compose up -d && cd ..

# 2. Run the integration tests against it
PGTRANSFER_TEST_DSN='postgres://testuser:testpass@localhost:5433/testdb?sslmode=disable' \
PGTRANSFER_TEST_SSH_HOST=localhost PGTRANSFER_TEST_SSH_PORT=2222 \
PGTRANSFER_TEST_SSH_USER=sshuser  PGTRANSFER_TEST_SSH_PASSWORD=sshpass \
PGTRANSFER_TEST_DB_HOST=postgres \
  go test -tags=integration -v ./internal/io/...
Variable Default Purpose
PGTRANSFER_TEST_DSN (unset) DSN of the test database; required for the direct tests
PGTRANSFER_TEST_REQUIRE (unset) When 1, missing prerequisites fail instead of skipping (set by CI so a broken pipeline can't pass green)
PGTRANSFER_TEST_SSH_HOST localhost SSH server host for the tunnel test
PGTRANSFER_TEST_SSH_PORT 2222 SSH server port
PGTRANSFER_TEST_SSH_USER (unset) SSH user; when unset the tunnel test skips
PGTRANSFER_TEST_SSH_PASSWORD (unset) SSH password
PGTRANSFER_TEST_DB_HOST postgres Database host as seen from the SSH server
PGTRANSFER_TEST_DB_PORT 5432 Database port as seen from the SSH server
PGTRANSFER_TEST_DB_USER testuser Database user
PGTRANSFER_TEST_DB_PASSWORD testpass Database password
PGTRANSFER_TEST_DB_NAME testdb Database name

Note: PGTRANSFER_TEST_DB_HOST is resolved from the SSH server's point of view, not the test host. With the Docker stack that is the compose service name postgres; in CI the SSH container reaches the host-published database via host.docker.internal.

Local Testing with Docker

The repository ships a complete local testing stack under scripts/ (PostgreSQL with seeded schemas + sample data, an SSH server for tunnel testing, and sample CSV fixtures).

Start the full stack:

cd scripts
docker compose up -d
docker compose ps

This brings up:

Service Host endpoint Credentials
PostgreSQL localhost:5433 (host) → 5432 (container) testuser / testpass, db testdb
SSH server (tunnels) localhost:2222 sshuser / sshpass

Note: the database is published on host port 5433 to avoid clashing with a locally installed PostgreSQL. Inside the Docker network the database is reachable at the service name postgres:5432.

Create a test profile and verify:

./pgtransfer profile add test-docker \
  --host localhost --port 5433 \
  --user testuser --password testpass \
  --database testdb
./pgtransfer test-connection --profile test-docker

Test an SSH tunnel (Postgres is reached via the SSH server's view of the network, so the database host is the compose service name postgres):

./pgtransfer profile add ssh-test \
  --host postgres --port 5432 \
  --user testuser --password testpass \
  --database testdb \
  --ssh-host localhost --ssh-user sshuser --ssh-password sshpass --ssh-port 2222
./pgtransfer test-connection --profile ssh-test

Tear down:

cd scripts
docker compose down          # keep data
docker compose down -v       # remove volumes too
Code Quality
# Format code
go fmt ./...

# Clean up imports
goimports -w .

# Run static analysis
staticcheck ./...

# Clean dependencies
go mod tidy

# Build and test
go build -o pgtransfer . && go test ./...

🔒 Security Features

Authentication & Encryption
  • 🔐 SSH Tunnel Encryption: End-to-end encryption for all database connections
  • 🛡️ SSL/TLS Support: Configurable SSL modes (disable, require, verify-ca, verify-full)
  • 🔑 Multiple Auth Methods: SSH keys, SSH agent, and password authentication
  • 👤 Secure Input: Passwords never echoed to terminal or logged
Data Protection
  • 📁 Secure Storage: Encrypted local storage of connection profiles
  • 🚫 No Credential Logging: Sensitive data excluded from all log files
  • 🔄 Connection Validation: Automatic certificate and host key verification
  • ⏱️ Session Management: Automatic timeout and cleanup of connections
Advanced Security
  • 🏢 Bastion Host Support: Secure access through jump servers and bastion hosts
  • 🔍 Connection Auditing: Detailed logging of connection attempts and operations
  • 🛡️ Network Isolation: Database access only through encrypted tunnels
  • 📋 Security Standards: Supports common enterprise security requirements
Known Limitations
  • SSH host key verification: SSH connections currently accept any host key (InsecureIgnoreHostKey) to keep the initial setup friction low. For hardened environments, pin the expected host key or use known_hosts verification before relying on tunnels for production credentials.
  • Restore error tolerance: import dump tolerates benign client/server version-skew errors by default. Use --strict when you need a restore to fail on the first error.
  • --overwrite semantics: for database migration, overwrite relies on pg_restore --clean --if-exists for binary dumps; plain SQL dumps rely on the DROP statements emitted by pg_dump.

🤝 Contributing

We welcome contributions! Please see our Contributing Guidelines for details.

Development Setup
  1. Fork the repository
  2. Create a feature branch: git checkout -b feature/amazing-feature
  3. Make your changes and add tests
  4. Commit your changes: git commit -m 'Add amazing feature'
  5. Push to the branch: git push origin feature/amazing-feature
  6. Open a Pull Request

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🆘 Support

🏆 Acknowledgments

Core Technologies
  • Go: High-performance systems programming language
  • PostgreSQL: Advanced open-source relational database
  • Cobra: Powerful CLI framework for Go
Key Dependencies
Development Tools

Made with ❤️ for the PostgreSQL community

Documentation

The Go Gopher

There is no documentation for this package.

Source Files

View all Source files

Directories

Path Synopsis
cmd
export
import
migrate
profile
internal
config
db
io
log
mcpserver
Package mcpserver exposes pgtransfer functionality as Model Context Protocol (MCP) tools, so an AI host (Claude Desktop, Cursor, an agent runtime, …) can drive database transfers as native typed tools.
 Package mcpserver exposes pgtransfer functionality as Model Context Protocol (MCP) tools, so an AI host (Claude Desktop, Cursor, an agent runtime, …) can drive database transfers as native typed tools.
output
Package output provides machine-readable output for pgtransfer.
 Package output provides machine-readable output for pgtransfer.
sshx
Package sshx provides shared SSH helper utilities used across pgtransfer.
 Package sshx provides shared SSH helper utilities used across pgtransfer.
utils

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.