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. Choose the script direction (X to Y or Y to X)
  4. Click Generate Script in the toolbar

The generated script includes all selected changes in dependency order.

Script Structure

A typical deployment script includes:

-- PostgresCompare Deployment Script
-- Generated: 2024-01-15 10:30:00
-- X: production-db
-- Y: staging-db

BEGIN;

-- Create new types
CREATE TYPE public.status_type AS ENUM ('active', 'inactive', 'pending');

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

-- Alter existing tables
ALTER TABLE public.users ADD COLUMN last_login TIMESTAMP;

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

-- Create indexes
CREATE INDEX idx_users_status ON public.users(status);

COMMIT;

Script Direction

PostgresCompare supports two script directions:

  • X to Y - Generate SQL to update Y to match X
  • Y to X - Generate SQL to update X to match Y

Choose the direction based on which database you want to update.

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 the Deploy button
  3. Monitor deployment progress in the progress tracker
  4. Review the deployment results

Direct deployment provides real-time progress tracking and immediate feedback on success or failure.

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

Create reusable templates for common patterns:

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