PgDeploy

Restore, compare and deploy PostgreSQL databases

PgDeploy is a desktop app for moving PostgreSQL databases between environments. It reads a backup or a live database into a model, compares two of them, then writes out a plan you can read before anything runs. Nothing leaves your machine: no cloud account, no web server, no Docker.

  • Windows, macOS and Linux
  • Your data stays local
  • Free to use
Get in touch See how it works

PgDeploy

PostgreSQL restore & compare

What it does

Built for the awkward jobs

Restoring a client backup into a scratch database. Getting staging to match production. Working out what's different before you touch anything.

Read either kind of source

Point it at a pg_dump archive, custom, tar or directory, or at a live database. Both end up as the same kind of snapshot, so everything downstream works the same way.

Restore anywhere

Any PostgreSQL server you can reach. A brand new database or one that already exists, into the same schema or a different one.

Restore what you want

Everything, schema only, data only, or tables and data. Or go through the objects yourself, with separate ticks for structure, data and indexes.

Move a schema on the way in

Restore public into restored. pg_restore can't do this at all, so PgDeploy renders the archive to SQL, rewrites the schema references line by line, and replays it.

Compare two databases

Object by object, down to column type and nullability. Added, removed, modified and unchanged each get a glyph as well as a colour.

Save the whole setup

Keep a job as a .pgdeploy file and open it again next month. Passwords never go in the file.

Compare

See how two databases differ

Connect both, and PgDeploy walks them object by object: tables, views, functions, indexes, constraints, right down to a column's type and whether it's nullable. What comes back is a tree you can read, not a wall of SQL.

Every row carries a glyph as well as a colour, so the tree still makes sense if green and red look the same to you, or if you print it.

  • Added
  • Removed
  • Modified
  • Unchanged
Comparison staging → production
  • Schema public
  • customerstable
  • phonetext
  • emailvarchar(120) → text
  • legacy_refinteger
  • orderstable
  • invoicestable
  • audit_log_2019table
  • customer_summaryview
How a comparison reads: two schemas lined up, with the changed columns sitting under the table they belong to.

Safety

Nothing runs until you've read the plan

Database work is easy to regret. So PgDeploy does all its thinking first, shows you the result, and waits.

Before anything runs

  • A plan comes first, always. The operations are built and shown before there's anything to press.
  • Destructive steps are named one by one. Not a count at the bottom. You see which object each one touches.
  • The confirmation spells out where it's going. Server, database and schema, and an Export SQL instead button if you'd rather take it away and look at it.
  • Dry run changes nothing. It walks the whole plan and touches no data.
  • Read the SQL yourself. Copy it, save it, or run it. It's the same SQL either way.

While it runs

  • One transaction, if you want one. Wrap a restore up, and a failure leaves the database exactly as it was.
  • Progress, and a cancel button. Long jobs report what they're doing and stop when you ask.
  • Nothing is dropped implicitly. A database or a schema only goes if you asked for it to go.

Passwords

  • Never on a command line. Passwords reach the PostgreSQL tools through PGPASSWORD in the child process environment, where the process list can't leak them to other users on the machine.
  • Project files hold a key, not a secret. The password goes to DPAPI on Windows, the login keychain on macOS, the system keyring on Linux.
Deployment plan 7 operations, 2 destructive
  • Pre-data
  • 01CREATE TABLE public.orders
  • 02ALTER TABLE public.customers ADD COLUMN phone
  • 03ALTER TABLE public.customers ALTER email TYPE text
  • 04 ALTER TABLE public.customers DROP COLUMN legacy_ref
  • Post-data
  • 05CREATE INDEX orders_customer_id_idx
  • 06ADD CONSTRAINT orders_customer_id_fkey
  • 07 DROP TABLE public.audit_log_2019
Operations run in the order PostgreSQL restores an archive: pre-data, then data, then post-data. Indexes and constraints go on after the rows land, and anything that destroys data is flagged on its own row. A deployment carries schema changes, so the data phase is empty here.

2 of these 7 operations destroy data. This cannot be undone.

Server
db.internal:5432
Database
production
Schema
public
Export SQL instead Dry run Run 7 operations Cancel
The confirmation names the server, the database and the schema every time, and offers you a way out that still gets you the SQL.

How it works

Read, compare, plan, then run

01

Read

Open a pg_dump archive or connect to a live database. Either one becomes the same normalised snapshot.

02

Compare

Two snapshots go in, a result comes out. The comparer works on models alone, so no server is touched at this point.

03

Plan

Differences become ordered operations, grouped into the phases PostgreSQL uses, with the destructive ones flagged.

04

Preview or run

Read the SQL and take it away, or hand the plan to pg_restore and psql and watch it go.

Export SQL Run it

Under the hood

For the sceptical reader

pg_catalog, not information_schema

information_schema is the portable option and it's missing most of what a comparison tool needs: partitioning, generated columns, partial indexes, operator classes. It also hides objects the current user doesn't own. pg_catalog has all of it.

PostgreSQL decides what “the same” means

Comparison keys are the server's own pg_get_indexdef, pg_get_constraintdef and pg_get_functiondef output. Two definitions match when PostgreSQL considers them equivalent, not when the text happens to line up.

pg_restore does the restoring

It already handles the archive format, dependency order, parallel workers and COPY streaming. Rewriting that would be a lot of code that could only come out worse.

189 tests, none of them need a database

The comparison engine and the planner take models and return models. They never open a connection, read a file or touch the UI, which is exactly why the rules most likely to be wrong are the ones easiest to test.

Platforms

What it needs

A native window on all three desktops. No browser, no server, nothing to install alongside it except the PostgreSQL tools you probably already have.

Runs on

Windows, macOS, Linux

A real desktop application on each one, not a web page in a wrapper you have to log into.

PostgreSQL client tools

Version 13 or later

pg_restore, pg_dump and psql. They ship with any PostgreSQL install, and PgDeploy finds them on its own.

PostgreSQL server

Version 12 or later

For the databases you point it at, wherever they live.

.NET runtime

Not needed

Builds are self-contained, so the machine it lands on doesn't need anything installed first.

Not there yet

What it doesn't do

Better you read this here than find out halfway through a job.

Want a look at it?

PgDeploy is free to use. There's no public download yet, so send us a note and we'll sort out a build for your platform.

Get in touch

Get in Touch

Let's Start a Conversation

From idea to impact, we design, build, and deliver the technology that moves your business forward. Whether it's custom software, cloud solutions, or a fresh digital strategy, we bring the expertise to make it happen. Got a project? Let's talk. Feel free to get in touch, whether you want to collaborate or simply start a conversation, we'd love to hear from you.

Email

Location

Cambridge, UK