Liquibase Stuck Changelog Lock Release Guard

id: liquibase-stuck-changelog-lock-release-guard
namespace: company.team
description: Detect an old Liquibase changelog lock and release it only after
  approval and a fresh eligibility read.

inputs:
  - id: jdbc_url
    type: STRING
    defaults: "{{ secret('LIQUIBASE_JDBC_URL') }}"
    description: JDBC URL of the target database.
  - id: username
    type: STRING
    defaults: "{{ secret('LIQUIBASE_USERNAME') }}"
  - id: password
    type: STRING
    defaults: "{{ secret('LIQUIBASE_PASSWORD') }}"
  - id: stale_after_seconds
    type: INT
    defaults: 900
    min: 60
    max: 604800
    description: A lock older than this is a remediation candidate. Set it above the
      longest legitimate migration.
  - id: auto_remediate
    type: BOOL
    defaults: false
  - id: dry_run
    type: BOOL
    defaults: true
  - id: docker_network
    type: STRING
    defaults: bridge
    description: Docker network used by the worker task runner.

variables:
  changelog_yaml: |
    databaseChangeLog:
      - changeSet:
          id: create-accounts
          author: platform
          changes:
            - createTable:
                tableName: accounts
                columns:
                  - column:
                      name: id
                      type: int
                      constraints:
                        primaryKey: true
                  - column:
                      name: email
                      type: varchar(255)
      - changeSet:
          id: add-status
          author: platform
          changes:
            - addColumn:
                tableName: accounts
                columns:
                  - column:
                      name: status
                      type: varchar(32)

tasks:
  - id: inspect_before
    type: io.kestra.plugin.liquibase.CLI
    taskRunner:
      type: io.kestra.plugin.scripts.runner.docker.Docker
      networkMode: "{{ inputs.docker_network }}"
    description: Read the lock holder and age with listLocks, then read pending
      changesets with status before planning any write.
    env:
      LIQUIBASE_COMMAND_URL: "{{ inputs.jdbc_url }}"
      LIQUIBASE_COMMAND_USERNAME: "{{ inputs.username }}"
      LIQUIBASE_COMMAND_PASSWORD: "{{ inputs.password }}"
    inputFiles:
      changelog.yaml: "{{ vars.changelog_yaml }}"
    commands:
      - LIST_EXIT=0
      - liquibase listLocks > /tmp/liquibase-lock-{{ execution.id }}.log 2>&1 ||
        LIST_EXIT=$?
      - STATUS_EXIT=0
      - liquibase status --verbose --changelog-file=changelog.yaml >
        /tmp/liquibase-status-{{ execution.id }}.log 2>&1 || STATUS_EXIT=$?
      - cat /tmp/liquibase-lock-{{ execution.id }}.log
      - cat /tmp/liquibase-status-{{ execution.id }}.log
      - LOCK_LINE=$(grep -E '^ - .+ at ' /tmp/liquibase-lock-{{ execution.id
        }}.log | tail -1 || true)
      - test -n "$LOCK_LINE" || LOCK_LINE=NONE
      - LOCKED=0
      - test "$LOCK_LINE" = NONE || LOCKED=1
      - LOCK_TS=${LOCK_LINE#* at }
      - LOCK_TS=$(printf '%s' "$LOCK_TS" | tr '\342\200\257' ' ' | tr -d ',')
      - LOCK_EPOCH=-1
      - test "$LOCKED" = 0 || LOCK_EPOCH=$(date -d "$LOCK_TS" +%s 2>/dev/null ||
        echo -1)
      - NOW_EPOCH=$(date +%s)
      - AGE_SECONDS=0
      - test "$LOCK_EPOCH" -lt 0 || AGE_SECONDS=$((NOW_EPOCH - LOCK_EPOCH))
      - test "$AGE_SECONDS" -ge 0 || AGE_SECONDS=0
      - PENDING_LINE=$(grep -E '^[0-9]+ changeset' /tmp/liquibase-status-{{
        execution.id }}.log | head -1 || true)
      - PENDING_COUNT=$(printf '%s\n' "$PENDING_LINE" | awk '{print $1}')
      - test -n "$PENDING_COUNT" || PENDING_COUNT=0
      - STALE=0
      - test "$LOCKED" = 1 && test "$LOCK_EPOCH" -ge 0 && test "$AGE_SECONDS"
        -ge "{{ inputs.stale_after_seconds }}" && STALE=1 || true
      - SNAPSHOT=$(printf '%s\n%s\n%s\n%s' "$LOCK_LINE" "$PENDING_LINE"
        "$LIST_EXIT" "$STATUS_EXIT" | sha256sum | cut -d' ' -f1)
      - printf
        'lock_holder_and_time=%s\nlock_age_seconds=%s\npending_changesets=%s\nstale_candidate=%s\n'
        "$LOCK_LINE" "$AGE_SECONDS" "$PENDING_COUNT" "$STALE"
      - printf
        '::{"outputs":{"locked":%s,"stale":%s,"age_seconds":%s,"pending":%s,"snapshot":"%s","list_exit":%s,"status_exit":%s}}::\n'
        "$LOCKED" "$STALE" "$AGE_SECONDS" "$PENDING_COUNT" "$SNAPSHOT"
        "$LIST_EXIT" "$STATUS_EXIT"

  - id: findings
    type: io.kestra.plugin.core.flow.If
    description: Report a stale lock with its holder and age, while leaving a fresh
      lock untouched.
    condition: "{{ outputs.inspect_before.vars.locked == 1 }}"
    then:
      - id: report_lock
        type: io.kestra.plugin.core.log.Log
        message: "Liquibase lock read: holder and granted time are in inspect_before
          logs; age={{ outputs.inspect_before.vars.age_seconds }}s,
          stale_candidate={{ outputs.inspect_before.vars.stale }}, pending={{
          outputs.inspect_before.vars.pending }}."
    else:
      - id: report_unlocked
        type: io.kestra.plugin.core.log.Log
        message: "Liquibase changelog lock table is empty. No release is proposed;
          pending changesets={{ outputs.inspect_before.vars.pending }}."

  - id: remediation_mode
    type: io.kestra.plugin.core.flow.If
    description: Enter the write path only for a stale lock when both safe-mode
      inputs explicitly allow remediation.
    condition: "{{ outputs.inspect_before.vars.stale == 1 and inputs.auto_remediate
      and not inputs.dry_run }}"
    then:
      - id: notify_approval
        type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
        description: Tell the operator which stale lock and pending changesets are
          waiting for approval.
        url: "{{ secret('SLACK_WEBHOOK_URL') }}"
        payload: |
          {
            "text": "Liquibase stale-lock candidate in execution {{ execution.id }}. Lock age={{ outputs.inspect_before.vars.age_seconds }}s, pending changesets={{ outputs.inspect_before.vars.pending }}. Review the inspect_before logs for holder and granted time before approving releaseLocks."
          }

      - id: approval_gate
        type: io.kestra.plugin.core.flow.Pause
        description: Releasing a lock while a migration is still running can corrupt the
          changelog. Approve only after checking the holder and migration host.
        pauseDuration: PT2H
        behavior: FAIL
        onResume:
          - id: approved
            type: BOOL
            defaults: false
            description: Set true only after confirming the migration that owned the lock is
              gone.

      - id: approved_branch
        type: io.kestra.plugin.core.flow.If
        description: Continue only when the reviewer explicitly approved the release.
        condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
        then:
          - id: recheck_before_write
            type: io.kestra.plugin.liquibase.CLI
            taskRunner:
              type: io.kestra.plugin.scripts.runner.docker.Docker
              networkMode: "{{ inputs.docker_network }}"
            description: Re-read listLocks and status immediately before releaseLocks so an
              approval cannot act on a stale snapshot.
            env:
              LIQUIBASE_COMMAND_URL: "{{ inputs.jdbc_url }}"
              LIQUIBASE_COMMAND_USERNAME: "{{ inputs.username }}"
              LIQUIBASE_COMMAND_PASSWORD: "{{ inputs.password }}"
            inputFiles:
              changelog.yaml: "{{ vars.changelog_yaml }}"
            commands:
              - LIST_EXIT=0
              - liquibase listLocks > /tmp/liquibase-lock-{{ execution.id }}.log
                2>&1 || LIST_EXIT=$?
              - STATUS_EXIT=0
              - liquibase status --verbose --changelog-file=changelog.yaml >
                /tmp/liquibase-status-{{ execution.id }}.log 2>&1 ||
                STATUS_EXIT=$?
              - cat /tmp/liquibase-lock-{{ execution.id }}.log
              - cat /tmp/liquibase-status-{{ execution.id }}.log
              - LOCK_LINE=$(grep -E '^ - .+ at ' /tmp/liquibase-lock-{{
                execution.id }}.log | tail -1 || true)
              - test -n "$LOCK_LINE" || LOCK_LINE=NONE
              - LOCKED=0
              - test "$LOCK_LINE" = NONE || LOCKED=1
              - LOCK_TS=${LOCK_LINE#* at }
              - LOCK_TS=$(printf '%s' "$LOCK_TS" | tr '\342\200\257' ' ' | tr -d
                ',')
              - LOCK_EPOCH=-1
              - test "$LOCKED" = 0 || LOCK_EPOCH=$(date -d "$LOCK_TS" +%s
                2>/dev/null || echo -1)
              - NOW_EPOCH=$(date +%s)
              - AGE_SECONDS=0
              - test "$LOCK_EPOCH" -lt 0 || AGE_SECONDS=$((NOW_EPOCH -
                LOCK_EPOCH))
              - test "$AGE_SECONDS" -ge 0 || AGE_SECONDS=0
              - PENDING_LINE=$(grep -E '^[0-9]+ changeset'
                /tmp/liquibase-status-{{ execution.id }}.log | head -1 || true)
              - PENDING_COUNT=$(printf '%s\n' "$PENDING_LINE" | awk '{print $1}')
              - test -n "$PENDING_COUNT" || PENDING_COUNT=0
              - STALE=0
              - test "$LOCKED" = 1 && test "$LOCK_EPOCH" -ge 0 && test
                "$AGE_SECONDS" -ge "{{ inputs.stale_after_seconds }}" && STALE=1
                || true
              - SNAPSHOT=$(printf '%s\n%s\n%s\n%s' "$LOCK_LINE" "$PENDING_LINE"
                "$LIST_EXIT" "$STATUS_EXIT" | sha256sum | cut -d' ' -f1)
              - printf
                'lock_holder_and_time=%s\nlock_age_seconds=%s\npending_changesets=%s\nstale_candidate=%s\n'
                "$LOCK_LINE" "$AGE_SECONDS" "$PENDING_COUNT" "$STALE"
              - printf
                '::{"outputs":{"locked":%s,"stale":%s,"age_seconds":%s,"pending":%s,"snapshot":"%s","list_exit":%s,"status_exit":%s}}::\n'
                "$LOCKED" "$STALE" "$AGE_SECONDS" "$PENDING_COUNT" "$SNAPSHOT"
                "$LIST_EXIT" "$STATUS_EXIT"

          - id: stale_plan_guard
            type: io.kestra.plugin.core.flow.If
            description: Refuse the write if the lock holder, timestamp, pending set or read
              result changed during approval.
            condition: "{{ outputs.recheck_before_write.vars.snapshot !=
              outputs.inspect_before.vars.snapshot or
              outputs.recheck_before_write.vars.stale != 1 }}"
            then:
              - id: fail_stale_plan
                type: io.kestra.plugin.core.execution.Fail
                errorMessage: "Liquibase lock eligibility changed during approval. No
                  releaseLocks command was issued."
            else:
              - id: release_lock
                type: io.kestra.plugin.liquibase.CLI
                taskRunner:
                  type: io.kestra.plugin.scripts.runner.docker.Docker
                  networkMode: "{{ inputs.docker_network }}"
                description: Release the approved stale lock with Liquibase's own releaseLocks
                  command.
                env:
                  LIQUIBASE_COMMAND_URL: "{{ inputs.jdbc_url }}"
                  LIQUIBASE_COMMAND_USERNAME: "{{ inputs.username }}"
                  LIQUIBASE_COMMAND_PASSWORD: "{{ inputs.password }}"
                inputFiles:
                  changelog.yaml: "{{ vars.changelog_yaml }}"
                commands:
                  - liquibase releaseLocks

              - id: verify_release
                type: io.kestra.plugin.liquibase.CLI
                taskRunner:
                  type: io.kestra.plugin.scripts.runner.docker.Docker
                  networkMode: "{{ inputs.docker_network }}"
                description: Re-read the lock table and status then run updateSQL to prove the
                  target can plan the pending migration.
                env:
                  LIQUIBASE_COMMAND_URL: "{{ inputs.jdbc_url }}"
                  LIQUIBASE_COMMAND_USERNAME: "{{ inputs.username }}"
                  LIQUIBASE_COMMAND_PASSWORD: "{{ inputs.password }}"
                inputFiles:
                  changelog.yaml: "{{ vars.changelog_yaml }}"
                commands:
                  - LIST_EXIT=0
                  - liquibase listLocks > /tmp/liquibase-lock-{{ execution.id
                    }}.log 2>&1 || LIST_EXIT=$?
                  - STATUS_EXIT=0
                  - liquibase status --verbose --changelog-file=changelog.yaml >
                    /tmp/liquibase-status-{{ execution.id }}.log 2>&1 ||
                    STATUS_EXIT=$?
                  - SQL_EXIT=0
                  - liquibase updateSQL --changelog-file=changelog.yaml >
                    /tmp/liquibase-update-sql-{{ execution.id }}.log 2>&1 ||
                    SQL_EXIT=$?
                  - cat /tmp/liquibase-lock-{{ execution.id }}.log
                  - cat /tmp/liquibase-status-{{ execution.id }}.log
                  - cat /tmp/liquibase-update-sql-{{ execution.id }}.log
                  - LOCKS_EMPTY=0
                  - grep -q -- ' - No locks' /tmp/liquibase-lock-{{ execution.id
                    }}.log && LOCKS_EMPTY=1 || true
                  - PENDING_LINE=$(grep -E '^[0-9]+ changeset'
                    /tmp/liquibase-status-{{ execution.id }}.log | head -1 ||
                    true)
                  - PENDING_COUNT=$(printf '%s\n' "$PENDING_LINE" | awk '{print
                    $1}')
                  - test -n "$PENDING_COUNT" || PENDING_COUNT=0
                  - printf
                    'locks_empty=%s\npending_changesets=%s\nupdate_sql_exit=%s\n'
                    "$LOCKS_EMPTY" "$PENDING_COUNT" "$SQL_EXIT"
                  - printf
                    '::{"outputs":{"locks_empty":%s,"pending":%s,"list_exit":%s,"status_exit":%s,"update_sql_exit":%s}}::\n'
                    "$LOCKS_EMPTY" "$PENDING_COUNT" "$LIST_EXIT" "$STATUS_EXIT"
                    "$SQL_EXIT"

              - id: verify_result
                type: io.kestra.plugin.core.flow.If
                description: Fail unless Liquibase reports an empty lock table and successfully
                  plans the current changelog.
                condition: "{{ outputs.verify_release.vars.locks_empty != 1 or
                  outputs.verify_release.vars.update_sql_exit != 0 or
                  outputs.verify_release.vars.list_exit != 0 or
                  outputs.verify_release.vars.status_exit != 0 }}"
                then:
                  - id: fail_verification
                    type: io.kestra.plugin.core.execution.Fail
                    errorMessage: "Liquibase releaseLocks did not produce a verifiable recovery: the
                      lock table was not empty or updateSQL/status failed."
                else:
                  - id: recovery_log
                    type: io.kestra.plugin.core.log.Log
                    message: "Liquibase lock released and verified: locks_empty={{
                      outputs.verify_release.vars.locks_empty }},
                      pending_changesets={{ outputs.verify_release.vars.pending
                      }}, updateSQL_exit={{
                      outputs.verify_release.vars.update_sql_exit }}."
        else:
          - id: approval_declined
            type: io.kestra.plugin.core.log.Log
            message: "Approval was declined or omitted. The Liquibase lock was left
              untouched."
    else:
      - id: report_only
        type: io.kestra.plugin.core.log.Log
        message: "No write issued. auto_remediate={{ inputs.auto_remediate }},
          dry_run={{ inputs.dry_run }}, stale_candidate={{
          outputs.inspect_before.vars.stale }}."

triggers:
  - id: every_15_minutes
    type: io.kestra.plugin.core.trigger.Schedule
    cron: "*/15 * * * *"
    disabled: true
    inputs:
      auto_remediate: false
      dry_run: true

errors:
  - id: alert_failure
    type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
    description: Alert the migration channel when detection, approval safety,
      remediation or verification fails.
    url: "{{ secret('SLACK_WEBHOOK_URL') }}"
    payload: |
      {
        "text": "Liquibase stuck-lock guard FAILED in flow {{ flow.id }} (execution {{ execution.id }}). Review the execution logs before retrying."
      }

Safety

A lock left by a killed migration blocks every later deployment. Releasing a lock while its migration is still running can corrupt DATABASECHANGELOG, so this flow only proposes a lock older than the configured grace period and pauses before releaseLocks.

Guard	What it stops
`stale_after_seconds`	A live migration whose lock is still within its normal runtime
Snapshot comparison after `Pause`	Acting on a holder, timestamp or pending set that changed while approval was waiting
`auto_remediate` plus `dry_run`	Writes from the shipped defaults and disabled schedule
Empty-lock and `updateSQL` verification	Calling a successful CLI exit a recovery when the lock remains or SQL cannot be planned

How it works

inspect_before (io.kestra.plugin.liquibase.CLI) runs listLocks and status --verbose against the target changelog. It records the holder, granted timestamp, age, pending count, exit codes and a snapshot hash.
findings (io.kestra.plugin.core.flow.If) logs an empty table, a legitimate young lock or a stale candidate. A healthy database and a migration still inside the grace period are spared.
remediation_mode requires auto_remediate: true and dry_run: false. It posts the stale age and pending count to Slack then waits at approval_gate (io.kestra.plugin.core.flow.Pause).
recheck_before_write repeats both CLI reads after approval. stale_plan_guard compares its snapshot with the original and fails before any write when the target changed.
release_lock (io.kestra.plugin.liquibase.CLI) runs Liquibase releaseLocks for the same changelog. It is the only mutating task.
verify_release reads listLocks and status again then runs updateSQL. verify_result fails unless the lock table is empty and all reads plus SQL planning succeed.
The disabled every_15_minutes schedule forces the safe inputs. The errors block sends a Slack alert for any failed detection, safety gate, write or verification.

What you get

The lock holder and granted time that explain which migration blocked the release.
A stale-lock decision based on a bounded age rather than a blind force-unlock.
A human approval record and a second eligibility read immediately before the write.
Proof from Liquibase itself that the lock table is empty and the pending changesets can be planned.
A repeatable read-only scheduled check with no write enabled by default.

Who it's for

Platform teams whose migration pods can be killed during deploys.
Release engineers who need to unblock a queue without editing DATABASECHANGELOGLOCK by hand.
Database owners who require a visible approval and a post-change proof before the next deploy.

Why orchestrate this with Kestra

A shell alias can run releaseLocks, but it cannot preserve the pre-approval snapshot, refuse a changed target, record who approved the write or fail when the database still cannot plan SQL. Kestra keeps the CLI reads, approval, guarded write and independent verification in one execution history.

Tested end to end

Tested against Kestra 2.0.5, Liquibase 4.29.2 and PostgreSQL 16 in a Docker test fixture. The fixture seeds two changesets, forces DATABASECHANGELOGLOCK with a one-hour-old holder and leaves one changeset pending. The remediation path measures one held lock before, zero locks after, one pending changeset before and a successful updateSQL plan after. Healthy, dry-run, declined, ineffective, repeated and approval-race cases are recorded in the evidence manifest for this blueprint.

Case	Execution	Result
Healthy	`7JEW2E3aa3P61CX54UBzCL`	SUCCESS. No lock held, pending changeset read, nothing written
Dry run	`79XuWzgJSIhfxR2bjXyWl3`	SUCCESS. One-hour-old lock found, lock count still 1
Declined approval	`494kvhHyzeGcfuzu19S272`	SUCCESS. Decline logged, lock count still 1
Remediation	`APusFXL4laAM0qRGk31YA`	SUCCESS. Lock count 1 to 0, `updateSQL` plans the pending changeset, applied changesets kept
Repeat	`5BPm8aKrISTmoc2CcoJg6G`	SUCCESS. Lock table already empty, no write
Release refused	`3wVzdDW30A698E9fKG421Z`	FAILED as intended. Verification gate stopped the run, lock count still 1
Holder changed during the Pause	`5bCbriKDgneQcxwAVpWUEL`	FAILED as intended. New holder detected before `releaseLocks`, lock count still 1

Prerequisites

A PostgreSQL or other Liquibase-supported database reachable by the Kestra worker.
A changelog whose DATABASECHANGELOGLOCK row is managed by Liquibase. Replace the example changelog_yaml variable with the changelog used by the deployment.
A worker with the Liquibase CLI plugin and permission to read the changelog tables plus execute releaseLocks.

Secrets

LIQUIBASE_JDBC_URL: JDBC URL for the target database.
LIQUIBASE_USERNAME: Liquibase database user.
LIQUIBASE_PASSWORD: Liquibase database password.
SLACK_WEBHOOK_URL: Slack incoming webhook for candidate and failure alerts.

Inputs

Input	Default	Purpose
`stale_after_seconds`	`900`	Minimum lock age before a candidate is proposed
`auto_remediate`	`false`	Enables the approval and write branch
`dry_run`	`true`	Keeps the run read-only when true
`docker_network`	`bridge`	Docker network used by the worker task runner

Quick start

Add the four secrets and replace the example changelog_yaml with the production changelog.
Run with the defaults. Confirm the lock holder, age and pending count in inspect_before.
For a confirmed orphan, run with auto_remediate: true and dry_run: false, inspect the Slack alert, then resume the Pause with approved: true.
Treat a failed verification as unresolved. The execution contains the lock and updateSQL output needed for the next investigation.
Enable every_15_minutes only after confirming its forced safe-mode inputs are appropriate for your namespace.

How to extend

Load the changelog from namespace files or a Git checkout while keeping the same file name passed to every CLI task.
Set stale_after_seconds from the longest observed migration plus a review margin.
Add a deployment identifier to the changelog and the Slack payload so the on-call can correlate the lock with release logs.
Chain this guard before the normal migration flow and stop the release when verification fails.

Limits: lock age is parsed from Liquibase's human-readable listLocks output, so a locale or CLI format change that cannot be parsed fails closed and does not release anything. The flow does not prove that a discarded migration left no partial external side effects; inspect the failed migration before approving.

Release a Stuck Liquibase Changelog Lock Safely

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

New to Kestra?

Release a Stuck Liquibase Changelog Lock Safely

Safety

How it works

What you get

Who it's for

Why orchestrate this with Kestra

Tested end to end

Prerequisites

Secrets

Inputs

Quick start

How to extend

Links

Tasks

Orchestrate with Kestra

Share this Blueprint

Create a Blueprint

More Infrastructure Blueprints

Backport Kestra UI hotfixes to a Git review branch

KMS Key Rotation Compliance Gate

Retire Unused Kestra Flows with Quarantine, Approval and Restore

Gate Kestra Subflow Changes Against Every Caller Before Deploying

Gate a Kestra upgrade against every live flow

Kinesis Stream Throughput Quota Gate

More Data Blueprints

Hold Every Release to a Lighthouse Score Budget

Close IFRS 16 leases each month with remeasurements, terminations and the maturity analysis

Kinesis Stream Throughput Quota Gate

KeyDB Active-Replica Multi-Master Peering Sentinel

Changelog CI Validation with Liquibase Update-Testing-Rollback

Alert When Streaming Windows Ship With Late or Missing Rows

New to Kestra?