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

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:

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

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

Tracking Changes Over Time

PostgresCompare provides an object history viewer that tracks how database objects change across comparisons. This gives you a development history for your schema, independent of version control.

Next Steps