migrate_postgres

{
  "name": "migrate_postgres",
  "tags": [
    "dokploy",
    "postgres",
    "migration",
    "backup"
  ],
  "steps": [
    {
      "id": "get_source",
      "name": "Read source Postgres service",
      "type": "http",
      "calls": "dokploy.http.postgres-one",
      "inputs": {
        "postgresId": "${{ inputs.source_postgres_id }}"
      },
      "outputs": {
        "appName": {
          "type": "string",
          "required": true
        },
        "databaseName": {
          "type": "string",
          "required": true
        },
        "databaseUser": {
          "type": "string",
          "required": true
        }
      }
    },
    {
      "id": "get_target",
      "name": "Read target Postgres service",
      "type": "http",
      "calls": "dokploy.http.postgres-one",
      "inputs": {
        "postgresId": "${{ inputs.target_postgres_id }}"
      },
      "outputs": {
        "appName": {
          "type": "string",
          "required": true
        },
        "databaseName": {
          "type": "string",
          "required": true
        },
        "databaseUser": {
          "type": "string",
          "required": true
        }
      }
    },
    {
      "id": "create_schedule",
      "name": "Create the migration+verify schedule",
      "type": "http",
      "calls": "dokploy.http.schedule-create",
      "inputs": {
        "name": "zeqqat-migrate-${{ inputs.target_postgres_id }}",
        "script": "set -eo pipefail\nSRC=$(docker ps -q -f name=${{ steps.get_source.outputs.appName }} | head -1)\nTGT=$(docker ps -q -f name=${{ steps.get_target.outputs.appName }} | head -1)\nif [ -z \"$SRC\" ] || [ -z \"$TGT\" ]; then echo \"missing container\" >&2; exit 1; fi\ncat > /tmp/zeqqat_checksum.sql <<'ZEQQAT_CHK_SQL'\nSELECT coalesce(md5(string_agg(tbl||':'||n||':'||h, ',' ORDER BY tbl)), 'empty') AS checksum FROM (\n  SELECT c.relname AS tbl,\n    (xpath('/row/h/text()', query_to_xml(format('SELECT coalesce(md5(string_agg(md5(x.*::text), '','' ORDER BY md5(x.*::text))), ''empty'') AS h FROM %I.%I x', n.nspname, c.relname), false, true, '')))[1]::text AS h,\n    (xpath('/row/n/text()', query_to_xml(format('SELECT count(*) AS n FROM %I.%I', n.nspname, c.relname), false, true, '')))[1]::text AS n\n  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace\n  WHERE c.relkind = 'r' AND n.nspname = 'public'\n) s;\nZEQQAT_CHK_SQL\nSRCSUM=$(docker exec -i \"$SRC\" psql -U ${{ steps.get_source.outputs.databaseUser }} -d ${{ steps.get_source.outputs.databaseName }} -tAX -v ON_ERROR_STOP=1 < /tmp/zeqqat_checksum.sql)\ndocker exec \"$SRC\" pg_dump -U ${{ steps.get_source.outputs.databaseUser }} -d ${{ steps.get_source.outputs.databaseName }} --no-owner --no-privileges --clean --if-exists | docker exec -i \"$TGT\" psql -U ${{ steps.get_target.outputs.databaseUser }} -d ${{ steps.get_target.outputs.databaseName }} -v ON_ERROR_STOP=1 -q\nTGTSUM=$(docker exec -i \"$TGT\" psql -U ${{ steps.get_target.outputs.databaseUser }} -d ${{ steps.get_target.outputs.databaseName }} -tAX -v ON_ERROR_STOP=1 < /tmp/zeqqat_checksum.sql)\nif [ \"$SRCSUM\" != \"$TGTSUM\" ]; then echo \"checksum mismatch: source=$SRCSUM target=$TGTSUM\" >&2; exit 1; fi\necho \"migration verified: source=$SRCSUM target=$TGTSUM\"",
        "command": "noop",
        "enabled": true,
        "shellType": "bash",
        "scheduleType": "dokploy-server",
        "cronExpression": "0 0 31 12 *"
      },
      "outputs": {
        "scheduleId": {
          "type": "string",
          "required": true
        }
      },
      "compensate": {
        "calls": "dokploy.http.schedule-delete",
        "inputs": {
          "scheduleId": "${{ steps.create_schedule.outputs.scheduleId }}"
        }
      }
    },
    {
      "id": "run_migration",
      "name": "Fire the migration schedule (blocks; non-zero exit returns HTTP 500 and fails this step)",
      "role": "verification",
      "type": "http",
      "calls": "dokploy.http.schedule-runManually",
      "inputs": {
        "scheduleId": "${{ steps.create_schedule.outputs.scheduleId }}"
      },
      "outputs": {}
    },
    {
      "id": "cleanup",
      "name": "Delete the migration schedule",
      "type": "http",
      "calls": "dokploy.http.schedule-delete",
      "inputs": {
        "scheduleId": "${{ steps.create_schedule.outputs.scheduleId }}"
      },
      "outputs": {}
    }
  ],
  "inputs": {
    "source_postgres_id": {
      "type": "string",
      "required": true
    },
    "target_postgres_id": {
      "type": "string",
      "required": true
    }
  },
  "defaults": {
    "retries": {
      "backoff": "fixed",
      "base_ms": 0,
      "attempts": 1
    }
  },
  "requires": [
    "dokploy"
  ],
  "on_failure": "cascade",
  "description": "Copy one Dokploy Postgres service into another and verify the copy by content checksum, entirely through the Dokploy REST API. The dump, restore, source and target checksums, and the comparison all run inside a one-off Dokploy schedule on the Dokploy server next to the databases, so the workflow needs no direct Postgres connection, no external port, and no tunnel. schedule.runManually blocks until the script finishes and returns HTTP 500 if it exits non-zero, so a checksum mismatch or a failed restore fails the run_migration step, which deletes the schedule via compensation. Safe to retry."
}