Migrate from Heroku PostgreSQL to Miget with Zero Downtime

Heroku PostgreSQL doesn't support logical replication, streaming replication, or superuser access. That rules out pg_dump for zero-downtime migrations and blocks tools like AWS DMS or pglogical that need wal_level=logical.

Bucardo solves this. It's an open-source (BSD), trigger-based replication tool that works with standard PostgreSQL permissions - no superuser, no WAL access, no special extensions on the source. You install triggers on the source tables, and Bucardo copies changes to the target in near-real-time.

This guide walks through migrating a Heroku PostgreSQL database to Miget using Bucardo running as a Miget app.

---

Why Bucardo for Heroku?

Heroku PostgreSQL has hard limitations:

Feature	Heroku	Needed for
wal_level=logical	Not available	Logical replication, DMS CDC
REPLICATION role	Not available	Streaming replication
Superuser	Not available	Most migration tools
pg_dump	Available	Offline migration only
Trigger creation	Available	Bucardo

Bucardo is the only zero-downtime migration tool that works within Heroku's constraints. It creates triggers on source tables to capture INSERT/UPDATE/DELETE operations, then replays them on the target.

---

Architecture

┌──────────────────┐                          ┌──────────────────┐
│  Heroku          │     Bucardo (triggers)    │      Miget       │
│  PostgreSQL      │ ◄────────────────────────│   App (Bucardo)  │
│  (source)        │                          │                  │
└──────────────────┘                          └───────┬──────────┘
                                                      │
                                              ┌───────▼──────────┐
                                              │      Miget       │
                                              │   PostgreSQL     │
                                              │    (target)      │
                                              └──────────────────┘

Bucardo runs as a Dockerfile-based app on Miget. It connects to both the Heroku source and the Miget target, installs triggers on the source, and continuously replicates changes. Bucardo stores its catalog in PostgreSQL (the target), so no persistent storage is needed - state survives pod restarts.

---

What You'll Need

• A Heroku PostgreSQL database (any plan)
• A Miget account with a PostgreSQL service
• Your Heroku database credentials (from heroku config)

---

Step 1: Create a PostgreSQL Service on Miget

In your Miget dashboard, go to Services > Create Service > PostgreSQL.

Choose your resource tier and PostgreSQL version (17.x recommended). Enable Public Access so Bucardo can connect from the Miget app.

Note down your connection details:

• Host - e.g., postgres-abc123.db.eu-east-1.onmiget.com
• Port - 5432
• Database - your database name
• Username - your database user
• Password - your database password

---

Step 2: Get Your Heroku Database Credentials

heroku config:get DATABASE_URL --app your-app-name

This returns a URL like:

postgres://username:password@host.amazonaws.com:5432/dbname

Save these - you'll need the host, username, password, and database name.

---

Step 3: Create the Bucardo App on Miget

Create a new application on Miget using the Dockerfile deployment method. Create a Dockerfile with the following content:

FROM debian:bookworm-slim

RUN apt-get update && apt-get install -y --no-install-recommends \
    bucardo \
    postgresql-client \
    && rm -rf /var/lib/apt/lists/*

# Point Bucardo config to persistent storage
ENV BUCARDORC=/bucardo-config/bucardorc

# Keep the container running for SSH access
CMD ["tail", "-f", "/dev/null"]

Deploy this app to Miget. The app doesn't serve HTTP traffic - it's a worker that you interact with via SSH.

Add Persistent Storage

Bucardo stores its catalog in PostgreSQL, but it needs a local config file (bucardorc) to know how to reach the catalog database. Add a persistent volume so this config survives restarts:

1. Go to your app's Add-ons tab
2. Click Add more > Storage
3. Set mount point to /bucardo-config
4. Choose RWO (single-instance) and 1 Gi - plenty for config files and logs

---

Step 4: Copy the Schema

Before setting up replication, copy the schema from Heroku to Miget. SSH into your Bucardo app and run:

# Dump schema only from Heroku
pg_dump "postgres://USER:PASS@heroku-host:5432/heroku_db" \
  --schema-only --no-owner --no-privileges > schema.sql

# Load schema on Miget
PGPASSWORD=miget_pass psql -h miget-host -U miget_user miget_db < schema.sql

Verify the tables exist on the target:

PGPASSWORD=miget_pass psql -h miget-host -U miget_user miget_db \
  -c "\dt"

---

Step 5: Initialize Bucardo

SSH into your Bucardo app and initialize the Bucardo catalog database. Bucardo stores its own metadata in a PostgreSQL database - we'll use a separate database on the Miget instance:

# Initialize Bucardo (creates its catalog schema)
bucardo install --batch \
  --dbhost miget-host \
  --dbport 5432 \
  --dbname miget_db \
  --dbuser miget_user \
  --dbpass miget_pass

---

Step 6: Register Source and Target Databases

# Add Heroku as the source
bucardo add db heroku_source \
  dbhost=heroku-host \
  dbport=5432 \
  dbname=heroku_db \
  dbuser=heroku_user \
  dbpass=heroku_pass

# Add Miget as the target
bucardo add db miget_target \
  dbhost=miget-host \
  dbport=5432 \
  dbname=miget_db \
  dbuser=miget_user \
  dbpass=miget_pass

---

Step 7: Add Tables and Create the Sync

# Discover and add all tables from the source
bucardo add all tables db=heroku_source

# Discover and add all sequences from the source
bucardo add all sequences db=heroku_source

# Create a sync: replicate from Heroku to Miget
bucardo add sync heroku_to_miget \
  relgroup=heroku_source \
  dbs=heroku_source:source,miget_target:target \
  onetimecopy=2

The onetimecopy=2 option tells Bucardo to perform an initial full copy of all data before starting trigger-based replication.

---

Step 8: Start Replication

bucardo start

Monitor the sync progress:

# Check sync status
bucardo status

# Watch the log
tail -f /var/log/bucardo/log.bucardo

The initial copy may take a while depending on your data size. Once complete, Bucardo switches to trigger-based CDC - new inserts, updates, and deletes on Heroku are continuously replicated to Miget.

---

Step 9: Verify the Data

Compare row counts between source and target:

# Count on Heroku
psql "postgres://USER:PASS@heroku-host:5432/heroku_db" \
  -c "SELECT table_name, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

# Count on Miget
PGPASSWORD=miget_pass psql -h miget-host -U miget_user miget_db \
  -c "SELECT table_name, n_live_tup FROM pg_stat_user_tables ORDER BY n_live_tup DESC;"

Insert a test row on Heroku and verify it appears on Miget within seconds:

# Insert on Heroku
psql "postgres://USER:PASS@heroku-host:5432/heroku_db" \
  -c "INSERT INTO your_table (name) VALUES ('migration-test');"

# Force a sync cycle
bucardo kick heroku_to_miget 0

# Check on Miget
PGPASSWORD=miget_pass psql -h miget-host -U miget_user miget_db \
  -c "SELECT * FROM your_table WHERE name = 'migration-test';"

---

Step 10: Cut Over

When you're confident the data is in sync:

1. Enable maintenance mode on your Heroku app to stop writes
2. Run a final sync -bucardo kick heroku_to_miget 0
3. Verify row counts match between source and target
4. Update your app's DATABASE_URL to point to Miget
5. Deploy your app on Miget (or update the environment variable on Heroku if migrating incrementally)
6. Stop Bucardo -bucardo stop
7. Clean up triggers on Heroku -bucardo remove sync heroku_to_miget

---

Step 11: Clean Up

After confirming everything works on Miget:

# Stop and remove Bucardo
bucardo stop
bucardo remove sync heroku_to_miget
bucardo remove db heroku_source
bucardo remove db miget_target

# Delete the Bucardo app on Miget (from dashboard)
# Remove the Heroku PostgreSQL addon
heroku addons:destroy DATABASE --app your-app-name --confirm your-app-name

---

Offline Alternative: pg_dump / pg_restore

If you can tolerate downtime, a simple dump and restore is faster to set up:

# Dump from Heroku
pg_dump "postgres://USER:PASS@heroku-host:5432/heroku_db" \
  --no-owner --no-privileges -Fc > heroku_backup.dump

# Restore to Miget
pg_restore -h miget-host -U miget_user -d miget_db \
  --no-owner --no-privileges heroku_backup.dump

This works for small databases or when a maintenance window is acceptable. For production databases that can't afford downtime, use Bucardo.