db-shifter 0.1.9

A smart tool to sync missing rows between two PostgreSQL databases.

db-shifter

Because someone switched your DB URL again.

---

Did your intern point production to the wrong DB?
Did your devops team swear "nothing changed" before disappearing?
Did your CTO say "just restore from backup" like you weren't already stressed?

Yeah. We've all been there.

---

Welcome to db-shifter — the script that digs through your old PostgreSQL database and copies only the missing rows into your new one. Oh, it also syncs columns too — just add the --deep-check flag.

No overwrites. No dumb pg_dump restores. Just calculated migration that doesn't wreck your existing data.

---

Why?

Because your CTO is a clown.
Because your devops team "accidentally" pointed production at the wrong database.
Because you need to copy missing data table-by-table and you're too busy to do it manually.

db-shifter handles the grunt work while you figure out who to blame.

---

Features

Core Functionality

• ✅ Auto-detects all tables in public schema
• ✅ Finds primary keys automatically
• ✅ Copies only missing rows in the new DB
• ✅ Skips duplicates (doesn't ruin your existing data)

Advanced Features

• 🔧 Column-based sync — specify exactly which columns to sync
• 🔄 Circular FK handling — automatically detects and handles circular foreign key dependencies
• 🧠 Smart sync order optimization based on FK relationships
• 🛡️ FK constraint handling — no more FK violation errors

---

Installation

Quick Install

pip install db-shifter

From Source

git clone https://github.com/goodness5/db-shifter.git
cd db-shifter
pip install -e .

---

Usage

Sync Mode (Default)

Basic Sync

db-shifter --old-db-url postgresql://user:pass@oldhost/db \
           --new-db-url postgresql://user:pass@newhost/db

Column-based Sync

Sync only specific columns (useful when schemas differ):

db-shifter --old-db-url postgresql://user:pass@oldhost/db \
           --new-db-url postgresql://user:pass@newhost/db \
           --columns id,name,email,created_at

Single Table Sync

db-shifter --old-db-url postgresql://user:pass@oldhost/db \
           --new-db-url postgresql://user:pass@newhost/db \
           --table users

Deep Check Mode

Compare and update column values for existing rows (not just insert missing rows):

db-shifter --old-db-url postgresql://user:pass@oldhost/db \
           --new-db-url postgresql://user:pass@newhost/db \
           --deep-check

This will:

• Insert missing rows (as usual)
• Compare column values for rows that exist in both databases
• Update rows where column values differ

---

Insert Mode

Insert records directly into a database table. Perfect for adding data without writing SQL.

Insert from JSON String

db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --data '{"name":"John Doe","email":"[e-mail]","age":30}'

Insert Multiple Records from JSON

db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --data '[{"name":"John","email":"[e-mail]"},{"name":"Jane","email":"[e-mail]"}]'

Insert from JSON File

db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --file data.json

Insert from CSV File

db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --file data.csv

Handle Conflicts Gracefully

# Ignore conflicts (skip duplicate records)
db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --file data.json \
           --on-conflict ignore

# Update on conflict (upsert)
db-shifter --db-url postgresql://user:pass@host/db \
           --table users \
           --file data.json \
           --on-conflict update

---

Command-line Options

Sync Mode Options

Flag	Description
--dry-run	Simulate the transfer, no data is hurt
--verbose	Prints detailed logs of every row
--table <name>	Sync just one table
--skip-fk	Ignores foreign key errors
--columns <list>	Sync only specified columns (comma-separated). Primary key is always included.
--deep-check	Deep check: compare column values for existing rows and update differences

Insert Mode Options

Flag	Description
--db-url	Database connection string (required)
--table	Table name to insert into (required)
--data	JSON data (object or array) or path to JSON/CSV file
--file	Path to JSON or CSV file
--dry-run	Simulate the insert, no data is changed
--verbose	Print detailed logs of every record
--skip-fk	Ignore foreign key errors
--on-conflict	What to do on primary key conflict: ignore, update, or error (default)

---

How It Works

1. Connects to both databases
2. Lists all public tables
3. Checks the primary keys
4. Pulls rows missing from the new database
5. Inserts them without wrecking existing rows

---

⚠️ Caution

Important Notes:

• Assumes you have primary keys (don't be a barbarian)
• Circular FK dependencies are detected and handled, but you may need to manually re-add FK constraints after sync
• If you're syncing 50GB of data, don't cry when it takes time
• Backups are your friend. Don't be dumb.

---

Circular Foreign Key Handling

When db-shifter detects circular foreign key dependencies (e.g., Table A → Table B → Table A), it will:

1. Detect the cycle and warn you about it
2. Temporarily disable FK constraints for tables in the cycle during sync
3. Sync all data without FK constraint violations
4. Note: You may need to manually re-add FK constraints after sync completes

Example output:

Circular foreign key dependencies detected:
   Cycle 1: users → profiles → users
   Will sync these tables in multiple passes with FK constraint handling

---

Coming Soon

• 🕐 Timestamp-based syncing (created_at support)
• 🗄️ .sqlite → postgres sync
• 📦 .bak and json data support input
• 🖥️ GUI with a "FIX EVERYTHING" button (for product managers)

---

Contributing

Found a bug? Good.
Fix it, submit a PR, and don't drop your cashapp in the description.

---

License

MIT. Do whatever you want. Just don't call me if you drop prod again.

---

Made with ❤️ (and frustration)

⬆ Back to Top