Skip to content

Database Checker

The PUM Checker is a powerful tool for comparing two PostgreSQL databases and identifying structural differences. This is particularly useful for:

  • Quality Assurance: Verify that development and production databases are in sync
  • Migration Validation: Confirm that database upgrades were applied correctly
  • Environment Comparison: Compare staging, testing, and production environments
  • CI/CD Integration: Automatically validate database schemas in continuous integration pipelines

How It Works

The Checker compares two PostgreSQL databases by analyzing their metadata from information_schema tables. It performs systematic checks across multiple database elements and generates a detailed report of any differences found.

Comparison Process

  1. Connection: Establishes connections to both databases using either PostgreSQL service names or connection strings
  2. Element Scanning: Queries database metadata for each structural element
  3. Comparison: Compares the results between the two databases
  4. Difference Detection: Identifies added (+) or removed (-) elements
  5. Report Generation: Creates a comprehensive report in text, HTML, or JSON format

Checked Elements

The Checker examines the following database elements:

  • Tables: Table definitions and their schemas
  • Columns: Column names, types, and attributes
  • Constraints: Primary keys, foreign keys, unique constraints, and check constraints
  • Views: View definitions
  • Sequences: Sequence definitions and configurations
  • Indexes: Index definitions and types
  • Triggers: Trigger functions and configurations
  • Functions: Stored procedures and functions
  • Rules: Database rules

Filtering and Exclusions

You can customize what gets compared:

  • Ignore specific elements: Skip certain types of objects (e.g., --ignore views triggers)
  • Exclude schemas: Ignore specific schemas (e.g., -N audit -N logging)
  • Exclude field patterns: Filter out fields matching SQL LIKE patterns (e.g., -P '%_backup')

System schemas (information_schema and pg_%) are automatically excluded from checks.

Output Formats

The Checker supports three output formats:

Text Format (Default)

Simple, readable text output showing differences with + (added) and - (removed) markers.

HTML Format

Interactive HTML report with styling and collapsible sections, ideal for sharing with teams or embedding in documentation.

JSON Format

Structured JSON output for programmatic processing and integration with other tools.

Example Output

Below are real examples of checker output in each format, generated by comparing two database versions where one has been upgraded with schema changes.

??? example "Text Format" 

Tables
- {'table_schema': 'pum_test_checker', 'table_name': 'orders'}
+ {'table_schema': 'pum_test_checker', 'table_name': 'inventory'}
Columns
- {'table_schema': 'pum_test_checker', 'table_name': 'products', 'column_name': 'description', ...}
- {'table_schema': 'pum_test_checker', 'table_name': 'products', 'column_name': 'weight', ...}
- {'table_schema': 'pum_test_checker', 'table_name': 'users', 'column_name': 'status', ...}
- {'table_schema': 'pum_test_checker', 'table_name': 'users', 'column_name': 'phone', ...}
+ {'table_schema': 'pum_test_checker', 'table_name': 'users', 'column_name': 'email', ...}
Constraints
- {'constraint_name': 'users_status_check', ...}
- {'constraint_name': 'products_weight_positive', ...}
+ {'constraint_name': 'check_email', ...}
+ {'constraint_name': 'products_in_stock_check', ...}
Views
- {'table_schema': 'pum_test_checker', 'table_name': 'user_orders', ...}
Sequences
- {'relname': 'invoice_sequence', 'schema_name': 'pum_test_checker'}
- {'relname': 'orders_id_seq', 'schema_name': 'pum_test_checker'}
+ {'relname': 'inventory_id_seq', 'schema_name': 'pum_test_checker'}
Indexes
- {'schema_name': 'pum_test_checker', 'table_name': 'products', 'index_name': 'idx_products_name_desc', ...}
Triggers
- {'schema_name': 'pum_test_checker', 'relname': 'orders', 'tgname': 'orders_update_trigger', ...}
Functions
- {'routine_schema': 'pum_test_checker', 'routine_name': 'get_order_count', ...}
- {'routine_schema': 'pum_test_checker', 'routine_name': 'update_order_timestamp', ...}
Rules
- {'rule_schema': 'pum_test_checker', 'rule_table': 'user_orders', 'rule_name': '_RETURN', ...}

??? example "JSON Format" 

{
  "pg_connection1": "pum_test",
  "pg_connection2": "pum_test_2",
  "timestamp": "2026-01-21T16:11:55.444711",
  "passed": false,
  "total_checks": 9,
  "passed_checks": 0,
  "failed_checks": 9,
  "total_differences": 21,
  "check_results": [
    {
      "name": "Tables",
      "key": "tables",
      "passed": false,
      "difference_count": 2,
      "differences": [
        {
          "type": "removed",
          "content": {
            "table_schema": "pum_test_checker",
            "table_name": "orders"
          }
        },
        {
          "type": "added",
          "content": {
            "table_schema": "pum_test_checker",
            "table_name": "inventory"
          }
        }
      ]
    },
    {
      "name": "Columns",
      "key": "columns",
      "passed": false,
      "difference_count": 5,
      "differences": [
        {
          "type": "removed",
          "content": {
            "table_schema": "pum_test_checker",
            "table_name": "users",
            "column_name": "phone",
            "is_nullable": "YES",
            "data_type": "character varying"
          }
        },
        {
          "type": "added",
          "content": {
            "table_schema": "pum_test_checker",
            "table_name": "users",
            "column_name": "email",
            "is_nullable": "NO",
            "data_type": "character varying"
          }
        }
      ]
    }
  ]
}

HTML Format Example

View full HTML report - Opens in a new window with interactive features and styling.

Usage

For detailed command-line usage and examples, see the check command documentation.

Basic Example

pum -s my_database check production_database

This compares the my_database service against production_database and shows any structural differences.

Advanced Example

```bash
pum -p dev_db check prod_db \
  --ignore triggers functions \
  --exclude-schema audit \
  --format html \
  --output_file comparison_report.html

This creates an HTML report comparing databases while ignoring triggers and functions, and excluding the audit schema.

You can also use full connection strings:

pum -p "postgresql://user:pass@localhost/dev_db" check "postgresql://user:pass@remotehost/prod_db" \
  --format json

Exit Codes

  • 0: Databases are identical (or only ignored differences were found)
  • 1: Differences were detected

This makes the Checker ideal for CI/CD pipelines where you need to fail builds if databases are out of sync.

Programmatic Usage

You can also use the Checker in Python code:

from pum.checker import Checker
from pum.report_generator import ReportGenerator

# Using service names
checker = Checker(
    pg_connection1="development",
    pg_connection2="production",
    exclude_schema=["audit"],
    ignore_list=["triggers"]
)

# Or using connection strings
checker = Checker(
    pg_connection1="postgresql://user:pass@localhost/dev_db",
    pg_connection2="postgresql://user:pass@remotehost/prod_db",
    exclude_schema=["audit"],
    ignore_list=["triggers"]
)

report = checker.run_checks()

if report.passed:
    print("✓ Databases are in sync")
else:
    print(f"✗ Found {report.total_differences} differences")

# Generate HTML report
html = ReportGenerator.generate_html(report)
with open("report.html", "w") as f:
    f.write(html)
```

See Also