Deployment Scripts

Generate and customize SQL deployment scripts

PostgresCompare generates SQL scripts that synchronize your databases. This guide covers how to generate, customize, and safely deploy these scripts.

Generating Scripts

After running a comparison:

  1. Review the differences in the results view
  2. Select which changes to include using the checkboxes
  3. Click Generate Script in the toolbar

The generated script includes all selected changes in dependency order.

Script Structure

Generated scripts include a header with metadata followed by statements grouped into labeled sections:

-- PostgresCompare Deployment Script
-- Version: 1.2.0
-- X: production-db
-- Y: staging-db
-- Generated: 2026-02-18 10:30:00

BEGIN;

/*** Tables ***/

CREATE TABLE public.audit_log (
    id SERIAL PRIMARY KEY,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

/*** Types ***/

CREATE TYPE public.status_type AS ENUM ('active', 'inactive', 'pending');

/*** Column Changes ***/

ALTER TABLE public.users ADD COLUMN last_login TIMESTAMP;

/*** Functions ***/

-- depends on: public.users
CREATE OR REPLACE FUNCTION public.get_active_users()
RETURNS SETOF public.users AS $$
    SELECT * FROM public.users WHERE status = 'active';
$$ LANGUAGE SQL;

/*** Indexes ***/

CREATE INDEX idx_users_status ON public.users(status);

COMMIT;

Sections appear only when the script contains objects of that type. Dependency comments (-- depends on, -- required by) explain the ordering of dependency-sorted objects.

Progress Logging

PostgresCompare injects RAISE NOTICE statements into the generated script after each DDL change, and a final notice when the migration completes. This means that when the script is run — whether through the app or externally via psql or another client — you get progress output as each change executes:

/*** Tables ***/

CREATE TABLE public.audit_log (
    id SERIAL PRIMARY KEY,
    action VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);
DO $$ BEGIN RAISE NOTICE '[✓] % | public.audit_log | create table', clock_timestamp()::time; END $$;

/*** Column Changes ***/

ALTER TABLE public.users ADD COLUMN last_login TIMESTAMP;
DO $$ BEGIN RAISE NOTICE '[✓] % | public.users | alter table', clock_timestamp()::time; END $$;

DO $$ BEGIN RAISE NOTICE '[>>] Migration complete at %', clock_timestamp()::time; END $$;
COMMIT;

When run via psql or another client, the output looks like:

NOTICE:  [✓] 14:23:05.123 | public.audit_log | create table
NOTICE:  [✓] 14:23:05.456 | public.users | alter table
NOTICE:  [>>] Migration complete at 14:23:05.789

Progress logging is on by default. To turn it off, use the toggle in the script toolbar — the script is re-drafted immediately when the setting changes. Progress notices follow statement selection: if a DDL statement is deselected, its corresponding notice is excluded from the script automatically.

When deploying directly through PostgresCompare, NOTICE output is captured and displayed in the running deployment modal alongside the statement-by-statement progress list.

Destructive Change Warnings

Before you run a script, PostgresCompare classifies every statement by risk level and surfaces warnings in the UI:

Level Examples Indicators
Destructive DROP TABLE, DROP COLUMN Red row, warning icon, glyph marker in editor
Warning DROP FUNCTION, DROP VIEW Amber row, warning icon, glyph marker in editor

A summary banner above the statement list shows the total count of destructive and warning statements. Review these carefully before proceeding — destructive statements can cause data loss that cannot be automatically reversed.

Previewing Differences

Hover over any row in the statement list to see a floating diff popover showing the before and after SQL side-by-side. This lets you inspect individual changes without leaving the script screen.

Pre/Post Deploy Scripts

You can attach custom SQL to run before or after the generated deployment script. This is useful for pre-flight checks, disabling triggers, setting session variables, or post-deployment verification steps.

To add pre/post deploy SQL, open the project and use the Pre-Deploy and Post-Deploy script editors on the Scripts tab. Both editors use Monaco with full SQL syntax highlighting. The scripts are saved with the project and included in the generated output.

Pre and post deploy scripts also appear as entries in the statement list alongside the generated changes. Each has its own include/exclude checkbox, so you can toggle them independently. When a script entry is unchecked, its section headers and dependency comments are suppressed automatically, and the deployment will execute exactly what the list shows.

-- Pre-deploy script (runs first)
SET lock_timeout = '5s';

-- PostgresCompare Deployment Script
-- Version: 1.2.0
-- X: production-db
-- Y: staging-db
-- ...
BEGIN;
  -- generated statements --
COMMIT;

-- Post-deploy script (runs last)
ANALYZE public.users;

Selecting Statements and Dependencies

When you select a statement in the list, PostgresCompare automatically selects any statements it depends on. This prevents incomplete deployments caused by missing dependencies — for example, selecting a function that references a type will also select the type.

Statements are grouped under clickable section headers (Tables, Views, Types, Sequences, Column Changes, Constraints, Indexes, Functions, Materialized Views, Triggers, Policies, Privileges, Drop Objects). Click a section header in the list to jump directly to that section in the SQL editor.

Transaction Wrapping

Scripts can be wrapped in a transaction block (BEGIN/COMMIT) so that all changes are applied atomically. If any statement fails, the entire transaction is rolled back.

Direct Deployment

In addition to generating SQL scripts, PostgresCompare can deploy changes directly to the target database:

  1. Review and select the changes to deploy
  2. Click Run script… in the toolbar
  3. Review the confirmation dialog — it shows the target connection and database, and highlights any Destructive (red) or Warning (amber) statements in the script. The Run button turns red when destructive changes are present.
  4. Click Run script to confirm and execute
  5. Monitor deployment progress in the progress tracker
  6. Review the deployment results

Direct deployment provides real-time progress tracking with per-statement logging — each statement is listed as it executes, with its result. This makes it straightforward to see exactly where a deployment is, and to identify which statement caused a failure if something goes wrong.

Deployment History

PostgresCompare tracks deployment history within the project. You can review past deployments to see what was changed and when, providing an audit trail for your database changes.

Post-Deployment Comparison

After deploying changes, run a new comparison to verify the deployment was successful. This confirms that the target database now matches the expected state and helps catch any issues immediately.

Handling Data Migrations

Schema changes often require data migrations. PostgresCompare generates DDL only, but you can add data migration statements to the generated script:

Column Type Changes

-- Generated by PostgresCompare
ALTER TABLE public.orders ALTER COLUMN amount TYPE NUMERIC(12,2);

-- Add manual data conversion
UPDATE public.orders SET amount = ROUND(amount, 2) WHERE amount IS NOT NULL;

Adding NOT NULL Constraints

-- First, handle existing NULL values
UPDATE public.users SET email = '[email protected]' WHERE email IS NULL;

-- Then add the constraint (generated by PostgresCompare)
ALTER TABLE public.users ALTER COLUMN email SET NOT NULL;

Renaming Columns

PostgresCompare may detect renames as drop + create. For data preservation:

-- Instead of DROP + ADD, use RENAME
ALTER TABLE public.users RENAME COLUMN username TO user_name;

Script Templates

Use the built-in pre/post deploy scripts to attach reusable SQL directly to your project. The examples below show common patterns you might add to your pre or post deploy scripts:

Pre-Deployment Checks

-- Check PostgreSQL version
DO $$
BEGIN
    IF current_setting('server_version_num')::int < 120000 THEN
        RAISE EXCEPTION 'PostgreSQL 12 or higher required';
    END IF;
END $$;

-- Check for active connections
DO $$
DECLARE
    active_count INT;
BEGIN
    SELECT COUNT(*) INTO active_count
    FROM pg_stat_activity
    WHERE datname = current_database() AND state = 'active' AND pid != pg_backend_pid();

    IF active_count > 5 THEN
        RAISE WARNING 'Multiple active connections detected: %', active_count;
    END IF;
END $$;

Post-Deployment Verification

-- Verify new objects exist
DO $$
BEGIN
    ASSERT EXISTS (
        SELECT 1 FROM information_schema.tables
        WHERE table_name = 'audit_log'
    ), 'audit_log table was not created';
END $$;

-- Verify constraints
DO $$
BEGIN
    ASSERT EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'users_email_unique'
    ), 'users_email_unique constraint was not created';
END $$;

Splitting Large Deployments

For large deployments, consider splitting changes:

By Risk Level

  1. Low risk: New objects, comments
  2. Medium risk: Modifications to existing objects
  3. High risk: Drops, data type changes

By Application Module

  1. Split changes by feature or module
  2. Deploy and verify incrementally
  3. Easier rollback if issues arise

Version Control

Store deployment scripts in version control:

migrations/
├── 2024-01-15-add-audit-table.sql
├── 2024-01-20-update-user-columns.sql
└── 2024-02-01-add-indexes.sql

Include metadata in script comments:

-- Migration: Add audit table
-- Ticket: PROJ-1234
-- Author: john.doe
-- Generated: PostgresCompare 4.0.1
-- Tested: staging-db 2024-01-14

Next Steps