Comparing Databases

Advanced techniques for comparing PostgreSQL databases

This guide covers advanced techniques for comparing PostgreSQL databases with PostgresCompare.

Database to Database Comparison

PostgresCompare compares two live PostgreSQL databases. Common scenarios include:

  • Comparing development to production
  • Comparing feature branches
  • Auditing database drift

Comparing Against a pg_dump File

Instead of connecting to two live databases, you can use a pg_dump SQL file or a folder of SQL files as the source or target for a comparison. This is useful when:

  • You store schema definitions in version control and want to compare a committed snapshot against a live database
  • You want to audit a schema without needing a live connection
  • You are working in an environment where only a database export is available

Setting up a file-based comparison

When creating a new project, select SQL File or Folder as the source type for either side instead of choosing an environment. Browse to a .sql file exported by pg_dump, or to a folder containing multiple SQL files.

PostgresCompare accepts:

Format Notes
Plain SQL (pg_dump --format=plain) Used directly
Binary pg_dump (pg_dump --format=custom or --format=directory) Converted via pg_restore automatically — pg_restore must be on your PATH
Folder of SQL files All .sql files in the folder are parsed and merged

The comparison then runs exactly as it would for two live databases — differences are listed, scripts can be generated, and exports work the same way.

Schema Filtering

By default, PostgresCompare compares all schemas. To focus on specific schemas, use the schema pairing options:

Option Description
X Schema to Compare The schema to compare from the X environment
Y Schema to Compare The schema to compare from the Y environment

This also allows cross-schema comparison — comparing a schema named dev in X against prod in Y.

Object Type Filtering

Filter which object types are compared in the project settings. PostgresCompare supports 38 object types:

Default ON: Tables, Views, Materialized Views, Triggers, Functions, Procedures, Indexes, Schemas, Composite Types, Enums, Domains, Extensions, Access Methods, Casts, Conversions, Event Triggers, Foreign Data Wrappers, Operator Families, Foreign Servers, Text Search Parsers, Text Search Templates, Text Search Dictionaries, Text Search Configurations, User Mappings, Publications, Subscriptions, Statistics, Policies, Collations, Sequences, Operators, Foreign Tables, Aggregates, Ranges, Settings

Default OFF: Roles, Databases, Tablespaces

Comparison Options

Fine-tune how objects are compared using the 10 ignore toggles. The options that were active when a comparison ran are saved with the result — click the info icon next to the Re-run comparison button to review them at any time.

Option Default Description
Ignore Whitespace Off Ignore whitespace in function definitions
Ignore Table Partitions Off Ignore partition definitions
Ignore Column Order Off Only compare column existence, not position
Ignore Code Comments On Ignore comments in code
Ignore Case Off Case-insensitive comparison
Ignore Owner Off Don’t compare ownership
Ignore Tablespace On Don’t compare tablespace assignments
Ignore Privileges On Don’t compare GRANT/REVOKE
Ignore Defaults Off Ignore column default differences
Ignore Statistics Off Ignore statistics targets

Filtering and Selecting Changes

The results view lets you filter differences before generating a script:

  • Use the filter dropdowns to narrow results by object type, change type, or schema
  • Use the name filter on any object type column to search within that type — for example, showing only tables whose names contain a specific term, without affecting other object types in the list
  • The select all checkbox in the header toggles only the rows currently visible after filtering — rows hidden by a filter are not affected
  • The header checkbox reflects the actual selection state of visible rows and updates as filters change

This makes it straightforward to select a specific subset of changes — for example, selecting only new tables while leaving function changes unselected.

Starring Comparisons

Mark comparisons as favourites by clicking the star icon on a comparison tile. The star appears on hover and turns amber when active. To show only starred comparisons, click the Starred filter toggle in the comparisons toolbar.

This is useful for pinning the comparisons you return to regularly — for example, your main dev-to-production check — so they are easy to find in a busy project.

Use the global search bar to find projects, environments, and comparison objects from anywhere in the app. Results are grouped by type and update as you type.

Keyboard Navigation

In the difference list, use the up/down arrow keys to move between rows without the mouse. This makes it faster to review a long list of differences when scripting or deploying changes.

Exporting Comparison Results

Export comparison results from the comparison details panel. Click the Export button and choose a format:

Format Description
Excel Spreadsheet with a Summary sheet (including a doughnut chart) and a per-object data sheet
HTML Self-contained file with clickable status filter pills and sortable column headers
PDF Print-ready document generated from the HTML report
JSON Machine-readable export; a companion .schema.json file is written alongside for CI/CD validation
Markdown Plain-text table format for pasting into wikis or pull requests
CSV Simple comma-separated values for spreadsheet import

The export options modal lets you choose which status categories to include. Identical objects are unchecked by default to keep exports focused on differences. Filenames default to the source and target database names plus the date (e.g. devDb_vs_prodDb_2026-03-08.xlsx).

Data Comparison

In addition to schema comparison, PostgresCompare supports data comparison between databases. This allows you to identify row-level differences in table data between your X and Y environments.

Comparing Large Databases

For databases with thousands of objects:

Performance Tips

  1. Use schema filters - Compare only relevant schemas
  2. Limit object types - Disable types you don’t need

Re-running a Comparison

The Re-run comparison button in the comparison toolbar starts a fresh comparison without leaving the current view. This is useful when you’ve made changes to a database and want to see whether a difference has been resolved.

  • Results stream in live as the new comparison runs
  • The toolbar button is disabled while a comparison is in progress
  • The previous comparison is preserved in the history list — navigating away and back will not lose it
  • If you navigate to a different comparison while a re-run is in progress, polling is cancelled automatically

Tracking Schema Changes Over Time

PostgresCompare records every comparison, letting you see how your schema has evolved across runs. To view schema changes between two snapshots:

  1. From the comparisons list, select two comparisons using the checkboxes
  2. Click the Schema Changes button that appears

The schema changes swimlane view

The schema changes view groups database objects by how they changed between the two selected comparisons:

Category Meaning
Fixed Was different or missing; now identical
Regressed Was identical; now different or missing
New Appeared for the first time
Removed No longer present
Changed Different in both snapshots, but the difference changed
Unchanged No change between the two snapshots

Each category is a collapsible section with a count badge and a colour-coded left border. A date range header shows the time span between the two comparisons. Use the name filter to search within the view.

This makes it easy to spot regressions — for example, catching a table that was identical in an earlier comparison but has drifted since a recent deployment.

Object history viewer

Click any object in the differences list and open the history tab to see that specific object’s changes across all comparisons. This gives you a per-object development timeline independent of version control.

The history viewer shows a vertical timeline with colour-coded indicator dots and relative timestamps (e.g. “2 hours ago”). Selecting an entry loads the Monaco diff editor with the older version on the left and the newer version on the right, labelled with their dates. For objects that were created or deleted, a single-panel view with a contextual banner is shown instead of an empty diff.

The Changes tab alongside the diff panel gives a plain-English description of what changed — for example, “column price changed from numeric(10,2) to numeric(12,4)” — without needing to read raw SQL.

Next Steps