pg-migrate-db 1.0.0
Efficient PostgreSQL database migration tool without pg_dump, with data integrity verification
Requires Python: >=3.7
Keywords: postgresql, postgres, database, migration, backup, restore, pg_dump, data-integrity, checksum

# PostgreSQL Database Migration Tool

A robust solution for migrating PostgreSQL databases between servers that cannot be directly connected. This tool extracts database schema and data from a source database, stores it locally with integrity verification, and loads it into a target database.

## Features

- ✅ **No pg_dump Required**: Direct SQL-based extraction and loading
- ✅ **Data Integrity**: SHA256 checksums verify data hasn't been altered
- ✅ **Network Isolation**: Works between databases in different networks
- ✅ **Efficient Chunking**: Handles large databases with chunked data extraction
- ✅ **Complete Migration**: Transfers tables, sequences, views, functions, constraints, and indexes
- ✅ **Database Comparison**: Validate source and target match exactly after migration
- ✅ **Progress Tracking**: Real-time progress display during migration
- ✅ **Resumable**: Can verify and re-run failed migrations
- ✅ **Safe Loading**: Handles foreign key dependencies correctly

## Installation

### Using uv (Recommended)

```bash
# Install uv if you haven't
curl -LsSf https://astral.sh/uv/install.sh | sh

# Install the project dependencies
uv sync

# Run the tool
uv run python migrate.py --help
```

### Using pip

```bash
# Install dependencies
pip install -r requirements.txt

# Make the script executable (optional)
chmod +x migrate.py
```

## Requirements

- Python 3.7+
- psycopg2-binary
- Network access to source database (for extraction)
- Network access to target database (for loading)
- Sufficient disk space for exported data

## Usage

The migration process has four main commands:

### 1. Extract from Source Database

```bash
# Using command line arguments
python migrate.py extract \
  --host source-db.example.com \
  --port 5432 \
  --database mydb \
  --user postgres \
  --password secret123 \
  --export-dir migration_data

# Using connection string
python migrate.py extract \
  --connection-string "postgresql://postgres:secret123@source-db.example.com:5432/mydb" \
  --export-dir migration_data

# Using environment variable for password
export PGPASSWORD=secret123
python migrate.py extract \
  --host source-db.example.com \
  --database mydb \
  --user postgres

# Extract specific schemas only
python migrate.py extract \
  --host source-db.example.com \
  --database mydb \
  --user postgres \
  --schemas public app_schema
```

### 2. Transfer Data (Optional)

If the extraction and loading happen on different machines, transfer the `migration_data` directory:

```bash
# Using scp
scp -r migration_data user@target-machine:/path/to/migration_data

# Using rsync
rsync -avz migration_data/ user@target-machine:/path/to/migration_data/

# Or compress first
tar -czf migration_data.tar.gz migration_data/
scp migration_data.tar.gz user@target-machine:/path/
```

### 3. Verify Data Integrity (Optional but Recommended)

```bash
# Verify exported data hasn't been corrupted
python migrate.py verify --export-dir migration_data
```

### 4. Load into Target Database

```bash
# Using command line arguments
python migrate.py load \
  --host target-db.example.com \
  --port 5432 \
  --database mydb \
  --user postgres \
  --password secret456 \
  --export-dir migration_data

# Using connection string
python migrate.py load \
  --connection-string "postgresql://postgres:secret456@target-db.example.com:5432/mydb" \
  --export-dir migration_data

# Skip verification (faster, not recommended)
python migrate.py load \
  --host target-db.example.com \
  --database mydb \
  --user postgres \
  --no-verify
```

### 5. Compare Source and Target (Validation)

After migration, validate that the target database exactly matches the source:

```bash
# Compare source and target databases
python migrate.py compare \
  --source-host source-db.example.com \
  --target-host target-db.example.com \
  --database mydb \
  --user postgres

# Save detailed comparison report
python migrate.py compare \
  --source-host source-db.example.com \
  --target-host target-db.example.com \
  --database mydb \
  --user postgres \
  --output comparison_report.json

# Compare specific schemas only
python migrate.py compare \
  --source-host source-db.example.com \
  --target-host target-db.example.com \
  --database mydb \
  --user postgres \
  --schemas public app_schema
```

The compare command validates:
- ✅ **Schema**: All tables, columns, and data types match
- ✅ **Data**: Row counts and checksums match exactly
- ✅ **Constraints**: Primary keys, foreign keys, unique constraints
- ✅ **Indexes**: All indexes present and correct
- ✅ **Sequences**: Sequence values preserved

## Export Directory Structure

The tool creates the following structure:

```
migration_data/
├── migration_metadata.json    # Migration metadata and checksums
├── schema/
│   └── schema.json           # Complete database schema
└── data/
    ├── data_info.json        # Data statistics and table checksums
    └── public/               # Schema name
        ├── users/            # Table name
        │   ├── chunk_00000.csv
        │   ├── chunk_00001.csv
        │   └── ...
        └── orders/
            └── chunk_00000.csv
```

## Data Integrity Verification

The tool ensures data integrity through multiple mechanisms:

1. **File Checksums**: SHA256 checksums for all exported files
2. **Table Checksums**: SHA256 checksums of table data
3. **Row Counts**: Verification of row counts after loading
4. **Ordered Extraction**: Uses primary keys for consistent ordering

## Features in Detail

### Schema Migration

- Tables with all column definitions
- Primary keys, foreign keys, unique constraints
- Check constraints and exclusion constraints
- Indexes (including partial and expression indexes)
- Sequences with current values
- Views with definitions
- Functions and stored procedures

### Data Migration

- Chunked extraction for large tables (configurable chunk size)
- CSV format for efficient storage and transfer
- NULL value handling
- Binary data support
- Batch insertion for performance
- Foreign key handling (temporarily disabled during load)

### Error Handling

- Graceful failure with detailed error messages
- Rollback on error during loading
- Warnings for non-critical issues
- Stack traces for debugging

## Advanced Usage

### Environment Variables

```bash
# Set password via environment variable
export PGPASSWORD=your_password

# Then run without --password flag
python migrate.py extract --host db.example.com --database mydb --user postgres
```

### Large Database Optimization

For very large databases, consider:

1. **Increase chunk size** for faster extraction (modify `chunk_size` in extractor.py)
2. **Use compression** when transferring data between machines
3. **Disable verification** during loading if you trust the transfer
4. **Run on a machine with SSD** for better I/O performance

### Selective Migration

To migrate only specific schemas:

```bash
python migrate.py extract \
  --host source-db.example.com \
  --database mydb \
  --user postgres \
  --schemas public app_schema
```

## Security Considerations

- **Passwords**: Use environment variables or connection strings instead of command-line arguments
- **File Permissions**: The exported data contains your database content - protect it appropriately
- **Network Security**: Use SSH tunnels or VPNs when transferring data
- **Target Database**: Ensure the target database is empty or you understand the implications of merging data

## Troubleshooting

### "Permission denied" errors

Ensure your database user has sufficient privileges:

```sql
-- On source database
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO your_user;

-- On target database
GRANT ALL PRIVILEGES ON DATABASE your_db TO your_user;
```

### "Out of memory" errors

Reduce the chunk size in the code or increase available memory.

### Foreign key constraint errors

The tool handles foreign keys automatically, but if you encounter issues:
- Ensure all referenced tables are included in the migration
- Check that the data respects foreign key constraints

### Checksum mismatches

This indicates data corruption during transfer. Re-extract or re-transfer the data.

## Limitations

- Does not support:
  - PostgreSQL extensions (must be installed manually on target)
  - Tablespaces
  - Roles and permissions (must be recreated manually)
  - Row-level security policies
  - Triggers (can be added in future versions)
  - Materialized views (can be added in future versions)
  - Partitioned tables (partial support - treated as regular tables)

## Performance

Typical performance (varies by hardware and network):

- Extraction: ~50,000-100,000 rows/second
- Loading: ~30,000-50,000 rows/second
- A 10GB database typically takes 10-30 minutes for complete migration

## Examples

### Simple Local Migration

```bash
# Extract
python migrate.py extract --host localhost --database olddb --user postgres

# Load
python migrate.py load --host localhost --database newdb --user postgres
```

### Cross-Network Migration

```bash
# On source network: extract data
python migrate.py extract --host 10.0.1.100 --database proddb --user postgres

# Transfer to USB drive or intermediate server
tar -czf migration_data.tar.gz migration_data/

# On target network: extract and load
tar -xzf migration_data.tar.gz
python migrate.py verify  # Verify integrity
python migrate.py load --host 10.0.2.100 --database proddb --user postgres
```

### AWS RDS to On-Premise

```bash
# Extract from RDS
python migrate.py extract \
  --host mydb.abc123.us-east-1.rds.amazonaws.com \
  --database proddb \
  --user admin \
  --password $RDS_PASSWORD

# Download to on-premise server (this step would be done separately)

# Load to on-premise PostgreSQL
python migrate.py load \
  --host 192.168.1.100 \
  --database proddb \
  --user postgres
```

## Contributing

Contributions are welcome! Areas for improvement:

- Support for materialized views
- Support for triggers
- Parallel extraction/loading for better performance
- Incremental migration support
- Better large object (LOB) handling
- Progress bars and better UI

## License

This tool is provided as-is for database migration purposes.

## Support

For issues, questions, or contributions, please refer to the source repository.