### Install pg-schema-diff CLI using Homebrew

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Installs the pg-schema-diff command-line interface tool using the Homebrew package manager. This is a convenient way to get started with the CLI.

```bash
brew install pg-schema-diff
```

--------------------------------

### Install pg-schema-diff CLI using Go toolchain

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Installs the pg-schema-diff command-line interface tool using the Go toolchain. This method is suitable for Go developers or those who prefer managing Go binaries directly.

```bash
go install github.com/stripe/pg-schema-diff/cmd/pg-schema-diff@latest
```

--------------------------------

### Apply Migration Plan using pg-schema-diff Library (Go)

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Applies a generated migration plan to a PostgreSQL database using the pg-schema-diff Go library. This example iterates through the plan's statements, setting session timeouts and executing each statement.

```go
for _, stmt := range plan.Statements {
	if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION statement_timeout = %d", stmt.Timeout.Milliseconds())); err != nil {
		panic(fmt.Sprintf("setting statement timeout: %s", err))
	}
	if _, err := conn.ExecContext(ctx, fmt.Sprintf("SET SESSION lock_timeout = %d", stmt.LockTimeout.Milliseconds())); err != nil {
		panic(fmt.Sprintf("setting lock timeout: %s", err))
	}
	if _, err := conn.ExecContext(ctx, stmt.ToSQL()); err != nil {
		panic(fmt.Sprintf("executing migration statement. the database maybe be in a dirty state: %s: %s", stmt, err))
	}
}

```

--------------------------------

### Add pg-schema-diff Library using Go Get

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Adds the pg-schema-diff Go library to your project using the 'go get' command. This allows you to integrate schema diffing capabilities into your Go applications.

```bash
go get -u github.com/stripe/pg-schema-diff@latest
```

--------------------------------

### Generate Migration Plan from Schema Directories (Go)

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This Go function generates a migration plan by comparing two directory structures representing PostgreSQL schemas. It utilizes tempdb for creating temporary databases to facilitate schema comparison. Dependencies include 'context', 'database/sql', 'github.com/jackc/pgx/v4', and 'github.com/stripe/pg-schema-diff/pkg/diff'.

```go
package main

import (
    "context"
    "database/sql"

    "github.com/jackc/pgx/v4"
    "github.com/stripe/pg-schema-diff/pkg/diff"
    "github.com/stripe/pg-schema-diff/pkg/tempdb"
)

func planFromDirectories(ctx context.Context, fromDir, toDir string) (diff.Plan, error) {
    // Parse connection config for temp database
    connConfig, err := pgx.ParseConfig("postgres://user:pass@localhost:5432/postgres")
    if err != nil {
        return diff.Plan{}, err
    }

    // Create temp database factory
    tempDbFactory, err := tempdb.NewOnInstanceFactory(ctx,
        func(ctx context.Context, dbName string) (*sql.DB, error) {
            cfg := connConfig.Copy()
            cfg.Database = dbName
            db, err := sql.Open("pgx", cfg.ConnString())
            if err != nil {
                return nil, err
            }
            db.SetMaxOpenConns(5)
            return db, nil
        },
    )
    if err != nil {
        return diff.Plan{}, err
    }
    defer tempDbFactory.Close()

    // Create schema sources from directories
    fromSource, err := diff.DirSchemaSource([]string{fromDir})
    if err != nil {
        return diff.Plan{}, err
    }

    toSource, err := diff.DirSchemaSource([]string{toDir, toDir + "/migrations"})
    if err != nil {
        return diff.Plan{}, err
    }

    // Generate plan comparing directory schemas
    plan, err := diff.Generate(ctx, fromSource, toSource,
        diff.WithTempDbFactory(tempDbFactory),
        diff.WithIncludeSchemas("public", "app"),
        diff.WithExcludeSchemas("temp", "audit"),
    )
    if err != nil {
        return diff.Plan{}, err
    }

    return plan, nil
}

```

--------------------------------

### Generate Migration Plan using pg-schema-diff Library (Go)

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Generates a migration plan by comparing a database schema against a defined DDL source using the pg-schema-diff Go library. It utilizes a temporary database for validation and supports data packing for new tables.

```go
// The tempDbFactory is used in plan generation to extract the new schema and validate the plan
tempDbFactory, err := tempdb.NewOnInstanceFactory(ctx, func(ctx context.Context, dbName string) (*sql.DB, error) {
	copiedConfig := connConfig.Copy()
	copiedConfig.Database = dbName
	return openDbWithPgxConfig(copiedConfig)
})
if err != nil {
	panic("Generating the TempDbFactory failed")
}
defer tempDbFactory.Close()
// Generate the migration plan
plan, err := diff.Generate(ctx, diff.DBSchemaSource(connPool), diff.DDLSchemaSource(ddl),
	diff.WithTempDbFactory(tempDbFactory),
	diff.WithDataPackNewTables(),
)
if err != nil {
	panic("Generating the plan failed")
}	
```

--------------------------------

### Build Docker Images for Testing pg-schema-diff

Source: https://github.com/stripe/pg-schema-diff/blob/main/CONTRIBUTING.md

These commands build Docker images specifically designed to run tests for pg-schema-diff. They allow selection of different PostgreSQL versions by passing a build argument. The images are tagged as 'pg-schema-diff-test-runner' and use the Dockerfile located at './build/Dockerfile.test'.

```bash
docker build -t pg-schema-diff-test-runner -f ./build/Dockerfile.test --build-arg POSTGRES_PACKAGE=postgresql14 .
```

```bash
docker build -t pg-schema-diff-test-runner -f ./build/Dockerfile.test --build-arg POSTGRES_PACKAGE=postgresql15 .
```

--------------------------------

### Generate PostgreSQL Migration Plan in Go

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

Generates a migration plan by comparing a source PostgreSQL database schema with a target schema defined as DDL. It utilizes a temporary database for schema extraction and supports data packing for new tables. Dependencies include 'pgx' for database connection and 'pg-schema-diff' for diffing logic.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/jackc/pgx/v4"
    "github.com/stripe/pg-schema-diff/pkg/diff"
    "github.com/stripe/pg-schema-diff/pkg/tempdb"
)

func main() {
    ctx := context.Background()

    // Connect to source database
    connConfig, err := pgx.ParseConfig("postgres://user:pass@localhost:5432/mydb")
    if err != nil {
        panic(err)
    }

    sourceDB, err := sql.Open("pgx", connConfig.ConnString())
    if err != nil {
        panic(err)
    }
    defer sourceDB.Close()

    // Define target schema as DDL
    targetDDL := []string{
        `CREATE TABLE users (
            id BIGSERIAL PRIMARY KEY,
            email VARCHAR(255) NOT NULL,
            created_at TIMESTAMP NOT NULL
        )`,
        `CREATE INDEX users_email_idx ON users(email)`,
    }

    // Create temp database factory for schema extraction
    tempDbFactory, err := tempdb.NewOnInstanceFactory(ctx,
        func(ctx context.Context, dbName string) (*sql.DB, error) {
            cfg := connConfig.Copy()
            cfg.Database = dbName
            db, err := sql.Open("pgx", cfg.ConnString())
            if err != nil {
                return nil, err
            }
            db.SetMaxOpenConns(5)
            return db, nil
        },
        tempdb.WithRootDatabase(connConfig.Database),
    )
    if err != nil {
        panic(err)
    }
    defer tempDbFactory.Close()

    // Generate migration plan
    plan, err := diff.Generate(ctx,
        diff.DBSchemaSource(sourceDB),
        diff.DDLSchemaSource(targetDDL),
        diff.WithTempDbFactory(tempDbFactory),
        diff.WithDataPackNewTables(),
    )
    if err != nil {
        panic(err)
    }

    // Inspect plan statements
    fmt.Printf("Generated %d migration statements\n", len(plan.Statements))
    for i, stmt := range plan.Statements {
        fmt.Printf("\nStatement %d:\n", i)
        fmt.Printf("  DDL: %s\n", stmt.DDL)
        fmt.Printf("  Timeout: %v\n", stmt.Timeout)
        fmt.Printf("  Lock Timeout: %v\n", stmt.LockTimeout)
        for _, hazard := range stmt.Hazards {
            fmt.Printf("  Hazard: %s - %s\n", hazard.Type, hazard.Message)
        }
    }
}
```

--------------------------------

### Apply Schema to Fresh Database with CLI

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Applies a defined schema to a fresh PostgreSQL database using the pg-schema-diff CLI. It requires a connection string and a directory containing SQL schema files.

```bash
mkdir schema
echo "CREATE TABLE foobar (id int);" > schema/foobar.sql
echo "CREATE TABLE bar (id varchar(255), message TEXT NOT NULL);" > schema/bar.sql
pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema
```

--------------------------------

### Generate SQL Migration Plan from Database to DDL

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This command generates a SQL migration plan by comparing a live database schema to a declarative DDL schema definition. It outputs SQL statements needed to transform the database to match the DDL, including hazard warnings for potentially impactful operations like concurrent index builds. It requires a source database connection string and a directory containing the target DDL files.

```bash
mkdir schema
cat > schema/users.sql << 'EOF'
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL,
    created_at TIMESTAMP NOT NULL
);
CREATE INDEX users_email_idx ON users(email);
EOF

pg-schema-diff plan \
  --from-dsn "postgres://user:pass@localhost:5432/mydb" \
  --to-dir ./schema \
  --output-format sql
```

--------------------------------

### Update Schema and Apply with CLI and Index Build Hazard

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

Updates an existing schema by adding an index and then applies the changes using the pg-schema-diff CLI. It demonstrates how to allow specific hazards, like index builds, during the application process.

```bash
echo "CREATE INDEX message_idx ON bar(message)" >> schema/bar.sql
pg-schema-diff apply --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir schema --allow-hazards INDEX_BUILD
```

--------------------------------

### Execute PostgreSQL Migration Plan in Go

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

Executes a generated PostgreSQL migration plan against a target database using a single connection to maintain session-level settings. It applies statement and lock timeouts for each migration statement. This function requires a database connection and the migration plan generated by `diff.Generate`.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"
    "time"

    "github.com/stripe/pg-schema-diff/pkg/diff"
)

func applyPlan(ctx context.Context, db *sql.DB, plan diff.Plan) error {
    // Get single connection to maintain session-level settings
    conn, err := db.Conn(ctx)
    if err != nil {
        return fmt.Errorf("getting connection: %w", err)
    }
    defer conn.Close()

    // Execute each statement with appropriate timeouts
    for i, stmt := range plan.Statements {
        fmt.Printf("Executing statement %d/%d...\n", i+1, len(plan.Statements))

        // Set session-level statement timeout
        if _, err := conn.ExecContext(ctx,
            fmt.Sprintf("SET SESSION statement_timeout = %d",
            stmt.Timeout.Milliseconds())); err != nil {
            return fmt.Errorf("setting statement timeout: %w", err)
        }

        // Set session-level lock timeout
        if _, err := conn.ExecContext(ctx,
            fmt.Sprintf("SET SESSION lock_timeout = %d",
            stmt.LockTimeout.Milliseconds())); err != nil {
            return fmt.Errorf("setting lock timeout: %w", err)
        }

        // Execute migration statement
        start := time.Now()
        if _, err := conn.ExecContext(ctx, stmt.ToSQL()); err != nil {
            return fmt.Errorf("executing statement %d: %w\nSQL: %s",
                i, err, stmt.DDL)
        }

        fmt.Printf("Completed in %v\n", time.Since(start))
    }

    return nil
}
```

--------------------------------

### Apply Schema Migration to Database

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This command applies a generated schema migration to a target database. It requires the source database connection string and the directory containing the DDL schema definition. Users can explicitly allow certain hazardous operations, such as index builds or exclusive lock acquisitions, and can choose to skip the confirmation prompt for automated deployments.

```bash
# Apply migration with hazard approval
pg-schema-diff apply \
  --from-dsn "postgres://user:pass@localhost:5432/mydb" \
  --to-dir ./schema \
  --allow-hazards INDEX_BUILD,ACQUIRES_ACCESS_EXCLUSIVE_LOCK

# Skipping confirmation prompt for automation
pg-schema-diff apply \
  --from-dsn "postgres://user:pass@localhost:5432/mydb" \
  --to-dir ./schema \
  --allow-hazards INDEX_BUILD \
  --skip-confirm-prompt
```

--------------------------------

### Generate Advanced Migration Plan in Go

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

Generates a database migration plan using advanced options such as temporary database factories, column ordering preferences, schema inclusion/exclusion, and custom logging. This function helps in creating robust and validated migration plans for PostgreSQL databases.

```go
package main

import (
    "context"
    "crypto/rand"
    "database/sql"

    "github.com/stripe/pg-schema-diff/pkg/diff"
    "github.com/stripe/pg-schema-diff/pkg/log"
    "github.com/stripe/pg-schema-diff/pkg/tempdb"
)

type customLogger struct{}

func (l *customLogger) Infof(format string, args ...interface{})  { /* ... */ }
func (l *customLogger) Warnf(format string, args ...interface{})  { /* ... */ }
func (l *customLogger) Errorf(format string, args ...interface{}) { /* ... */ }

func advancedPlanGeneration(ctx context.Context, db *sql.DB,
    targetDDL []string, factory tempdb.Factory) (diff.Plan, error) {

    plan, err := diff.Generate(ctx,
        diff.DBSchemaSource(db),
        diff.DDLSchemaSource(targetDDL),
        // Required: temp database for schema extraction and validation
        diff.WithTempDbFactory(factory),
        // Optimize new table column ordering to minimize padding
        diff.WithDataPackNewTables(),
        // Respect column order changes in existing tables
        diff.WithRespectColumnOrder(),
        // Skip automatic plan validation (faster, less safe)
        diff.WithDoNotValidatePlan(),
        // Custom logger for migration events
        diff.WithLogger(&customLogger{}),
        // Include specific schemas only
        diff.WithIncludeSchemas("public", "app", "reporting"),
        // Exclude specific schemas
        diff.WithExcludeSchemas("temp", "audit", "archive"),
        // Seed random generator for deterministic temp identifiers
        diff.WithRandReader(rand.Reader),
        // Disable concurrent index operations for simpler DDL
        diff.WithNoConcurrentIndexOps(),
    )
    if err != nil {
        return diff.Plan{}, err
    }

    return plan, nil
}
```

--------------------------------

### Plan Online Index Replacement in PostgreSQL with pg-schema-diff

Source: https://github.com/stripe/pg-schema-diff/blob/main/README.md

This snippet demonstrates how to use pg-schema-diff to generate a migration plan for replacing an existing index with a new one. It utilizes concurrent index building to minimize database performance impact and avoid write locks. The process involves renaming the old index, creating the new one concurrently, and finally dropping the old index.

```bash
$ pg-schema-diff plan --from-dsn "postgres://postgres:postgres@localhost:5432/postgres" --to-dir ./schema
```

--------------------------------

### Validate Schema Hash Before Migration (Go)

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This Go function validates the current schema hash of a database against the expected hash in a migration plan before applying the migration. It retrieves the current schema hash using 'schema.GetSchemaHash' and compares it with 'plan.CurrentSchemaHash'. If they match, it proceeds to execute the migration statements, applying session timeouts for each. Dependencies include 'context', 'database/sql', 'fmt', 'github.com/stripe/pg-schema-diff/pkg/diff', and 'github.com/stripe/pg-schema-diff/pkg/schema'.

```go
package main

import (
    "context"
    "database/sql"
    "fmt"

    "github.com/stripe/pg-schema-diff/pkg/diff"
    "github.com/stripe/pg-schema-diff/pkg/schema"
)

func validateAndApply(ctx context.Context, db *sql.DB, plan diff.Plan) error {
    // Get current schema hash from database
    currentHash, err := schema.GetSchemaHash(ctx, db)
    if err != nil {
        return fmt.Errorf("getting current schema hash: %w", err)
    }

    // Compare with plan's expected schema hash
    if currentHash != plan.CurrentSchemaHash {
        return fmt.Errorf(
            "schema hash mismatch: plan expects %s but database has %s. " +
            "The database schema may have changed since the plan was generated",
            plan.CurrentSchemaHash, currentHash,
        )
    }

    // Schema matches, safe to proceed with migration
    conn, err := db.Conn(ctx)
    if err != nil {
        return err
    }
    defer conn.Close()

    for _, stmt := range plan.Statements {
        if _, err := conn.ExecContext(ctx,
            fmt.Sprintf("SET SESSION statement_timeout = %d",
            stmt.Timeout.Milliseconds())); err != nil {
            return err
        }
        if _, err := conn.ExecContext(ctx,
            fmt.Sprintf("SET SESSION lock_timeout = %d",
            stmt.LockTimeout.Milliseconds())); err != nil {
            return err
        }
        if _, err := conn.ExecContext(ctx, stmt.ToSQL()); err != nil {
            return fmt.Errorf("migration failed: %w", err)
        }
    }

    return nil
}

```

--------------------------------

### Modify Migration Plan with Custom Timeouts (Go)

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This Go function modifies an existing migration plan by applying custom timeouts to specific SQL statements using regular expressions. It allows for increasing timeouts for CREATE INDEX statements and decreasing lock timeouts for ALTER TABLE statements. It also demonstrates inserting custom statements with defined timeouts and hazards. Dependencies include 'regexp', 'time', and 'github.com/stripe/pg-schema-diff/pkg/diff'.

```go
package main

import (
    "regexp"
    "time"

    "github.com/stripe/pg-schema-diff/pkg/diff"
)

func modifyPlanTimeouts(plan diff.Plan) (diff.Plan, error) {
    // Apply longer timeout for CREATE INDEX statements
    indexPattern := regexp.MustCompile(`CREATE\s+INDEX`)
    plan = plan.ApplyStatementTimeoutModifier(indexPattern, 30*time.Minute)

    // Apply shorter lock timeout for ALTER TABLE statements
    alterPattern := regexp.MustCompile(`ALTER\s+TABLE`)
    plan = plan.ApplyLockTimeoutModifier(alterPattern, 5*time.Second)

    // Insert custom statement at specific index
    customStmt := diff.Statement{
        DDL:         "SELECT pg_sleep(2)",
        Timeout:     10 * time.Second,
        LockTimeout: 1 * time.Second,
        Hazards: []diff.MigrationHazard{{
            Type:    diff.MigrationHazardTypeIsUserGenerated,
            Message: "Custom delay for production safety",
        }},
    }

    // Insert at index 2 (between statements 1 and 2)
    plan, err := plan.InsertStatement(2, customStmt)
    if err != nil {
        return diff.Plan{}, err
    }

    return plan, nil
}

```

--------------------------------

### Generate Plan with Custom Timeout Modifiers

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This command generates a migration plan while allowing custom timeout modifiers for specific SQL statement patterns. You can define session timeouts for operations like CREATE INDEX or ALTER TABLE, influencing how long these statements are allowed to run before being aborted. The output can be formatted as SQL or JSON.

```bash
pg-schema-diff plan \
  --from-dsn "postgres://user:pass@localhost:5432/mydb" \
  --to-dir ./schema \
  --statement-timeout-modifier 'pattern="CREATE INDEX" timeout=30m' \
  --lock-timeout-modifier 'pattern="ALTER TABLE" timeout=5s' \
  --output-format json
```

--------------------------------

### Plan with Disabled Concurrent Index Operations

Source: https://context7.com/stripe/pg-schema-diff/llms.txt

This command generates a migration plan that explicitly disables the use of the `CONCURRENTLY` keyword for index creation. This results in simpler DDL that may involve exclusive locks, which is useful in scenarios where concurrent operations are not desired or supported. The output format can be specified as SQL.

```bash
pg-schema-diff plan \
  --from-dsn "postgres://user:pass@localhost:5432/mydb" \
  --to-dir ./schema \
  --no-concurrent-index-ops \
  --output-format sql
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.