id: cockroachdb-zone-gc-ttl-floor-guard
namespace: company.team
description: |
Find CockroachDB zone configurations whose gc.ttlseconds is set below a safe floor. A low GC TTL
saves disk by garbage-collecting old MVCC versions sooner. It quietly breaks three things that
all need that history: a BACKUP that runs longer than the TTL, a changefeed that falls behind the
TTL and any AS OF SYSTEM TIME read older than the TTL. With approval, raise each too-low zone to
the floor, then read the zone configurations again and fail the run if any in scope is still below.
inputs:
- id: jdbc_url
type: STRING
displayName: JDBC URL
description: Any CockroachDB node over the Postgres wire protocol. The SQL port
is 26257 by default. Zone configurations are cluster wide, so one run
covers the whole cluster.
defaults: "jdbc:postgresql://localhost:26257/defaultdb?sslmode=require"
- id: db_user
type: STRING
displayName: SQL user
description: The role the queries connect as. It needs to read
crdb_internal.zones, plus the ZONECONFIG privilege on a target to raise
its TTL.
defaults: "root"
- id: password
type: STRING
displayName: SQL password
description: Read from the COCKROACH_PASSWORD secret by default. An insecure
local cluster ignores it.
defaults: "{{ secret('COCKROACH_PASSWORD') }}"
- id: min_gc_ttlseconds
type: INT
displayName: Minimum gc.ttlseconds (the floor)
description: >-
A zone whose own gc.ttlseconds is below this is flagged. The floor has to
cover your longest BACKUP, the furthest a changefeed may fall behind and
the oldest AS OF SYSTEM TIME read you rely on. The default of 14400 is
four hours, which clears a typical nightly backup window.
defaults: 14400
- id: include_internal_zones
type: BOOL
displayName: Also raise CockroachDB internal zones
description: >-
CockroachDB's own system zones use short TTLs on purpose. RANGE liveness
and RANGE timeseries hold transient data that is meant to be collected
quickly, so they are left out by default. Turn this on only when you know
why a system zone would need more history.
defaults: false
- id: max_zones_changed
type: INT
displayName: Maximum zones changed per run
description: Per run ceiling on ALTER CONFIGURE ZONE, lowest TTL first. Whatever
is left over is reported and picked up by the next run.
defaults: 10
- id: auto_remediate
type: BOOL
displayName: Auto remediate
description: Allow ALTER CONFIGURE ZONE. Raising a TTL only keeps more old
versions, so it never breaks a running query. It does cost disk.
defaults: false
- id: dry_run
type: BOOL
displayName: Dry run
description: Must be false, together with auto_remediate, before any zone is
changed. The approval Pause still has to be answered after that.
defaults: true
variables:
zone_cte: |
z AS (
SELECT zone_id, subzone_id, target,
coalesce(database_name, '') AS database_name,
coalesce(table_name, '') AS table_name,
coalesce(index_name, '') AS index_name,
coalesce(partition_name, '') AS partition_name,
substring(raw_config_sql, 'gc\.ttlseconds = ([0-9]+)')::INT8 AS explicit_ttl,
substring(full_config_sql, 'gc\.ttlseconds = ([0-9]+)')::INT8 AS effective_ttl,
((target LIKE 'RANGE %' AND target != 'RANGE default')
OR coalesce(database_name, '') = 'system') AS internal_zone
FROM crdb_internal.zones
)
settings_sql: |
WITH {{ render(vars.zone_cte) }}
SELECT current_database() AS database_name,
coalesce((SELECT effective_ttl FROM z WHERE target = 'RANGE default'), -1) AS default_ttl,
(SELECT count(*) FROM z)::INT8 AS total_zones,
(SELECT count(*) FROM z WHERE explicit_ttl IS NOT NULL
AND explicit_ttl < {{ inputs.min_gc_ttlseconds }}
AND (NOT internal_zone OR {{ inputs.include_internal_zones }}))::INT8 AS below_floor_in_scope,
(SELECT count(*) FROM z WHERE explicit_ttl IS NOT NULL
AND explicit_ttl < {{ inputs.min_gc_ttlseconds }})::INT8 AS below_floor_all
audit_sql: |
WITH {{ render(vars.zone_cte) }}
SELECT zone_id, subzone_id, target, database_name, table_name, index_name, partition_name,
explicit_ttl, effective_ttl, internal_zone,
'ALTER ' || target || ' CONFIGURE ZONE USING gc.ttlseconds = ' || {{ inputs.min_gc_ttlseconds }}::STRING AS fix_sql,
row_number() OVER (ORDER BY explicit_ttl ASC, target)::INT8 AS fix_rank
FROM z
WHERE explicit_ttl IS NOT NULL
AND explicit_ttl < {{ inputs.min_gc_ttlseconds }}
AND (NOT internal_zone OR {{ inputs.include_internal_zones }})
ORDER BY explicit_ttl ASC, target
LIMIT 500
verify_sql: |
WITH targets(target, before_ttl) AS (
VALUES ('', 0::INT8){% for r in outputs.audit.rows %}{% if r.fix_rank <= inputs.max_zones_changed %}, ('{{ r.target | replace({"'": "''"}) }}', {{ r.explicit_ttl }}::INT8){% endif %}{% endfor %}
), live AS (
SELECT target,
substring(full_config_sql, 'gc\.ttlseconds = ([0-9]+)')::INT8 AS effective_ttl
FROM crdb_internal.zones
)
SELECT count(*) FILTER (WHERE t.target <> '')::INT8 AS targets_checked,
count(*) FILTER (WHERE t.target <> ''
AND (l.effective_ttl IS NULL OR l.effective_ttl < {{ inputs.min_gc_ttlseconds }}))::INT8 AS targets_below_floor,
coalesce(string_agg(t.target || ' now ' || coalesce(l.effective_ttl::STRING, 'none') || ' was ' || t.before_ttl::STRING, '; ')
FILTER (WHERE t.target <> ''
AND (l.effective_ttl IS NULL OR l.effective_ttl < {{ inputs.min_gc_ttlseconds }})), '') AS laggards,
coalesce(string_agg(t.target || ' ' || coalesce(l.effective_ttl::STRING, 'none'), '; ')
FILTER (WHERE t.target <> ''), '') AS target_state,
coalesce((SELECT substring(full_config_sql, 'gc\.ttlseconds = ([0-9]+)')::INT8
FROM crdb_internal.zones WHERE target = 'RANGE default'), -1) AS default_ttl_after
FROM targets t LEFT JOIN live l ON l.target = t.target
tasks:
- id: settings_before
type: io.kestra.plugin.jdbc.postgresql.Query
description: >-
Read-only. The cluster default gc.ttlseconds from RANGE default, the
number of zones that carry a configuration and how many of them set their
own gc.ttlseconds below the floor, in scope and in total. The default is
read first because a low RANGE default lowers the effective TTL of every
object that does not override it.
url: "{{ inputs.jdbc_url }}"
username: "{{ inputs.db_user }}"
password: "{{ inputs.password }}"
fetchType: FETCH_ONE
sql: "{{ render(vars.settings_sql) }}"
- id: audit
type: io.kestra.plugin.jdbc.postgresql.Query
description: >-
Read-only. One row per zone whose own gc.ttlseconds is below the floor,
with the explicit value, the effective value, whether it is an internal
zone and the ALTER that would raise it. Internal zones are listed only
when include_internal_zones is on.
url: "{{ inputs.jdbc_url }}"
username: "{{ inputs.db_user }}"
password: "{{ inputs.password }}"
fetchType: FETCH
sql: "{{ render(vars.audit_sql) }}"
- id: summary
type: io.kestra.plugin.core.log.Log
message: >-
CockroachDB GC TTL on {{ outputs.settings_before.row.database_name }}:
RANGE default is {{ outputs.settings_before.row.default_ttl }}s, floor {{
inputs.min_gc_ttlseconds }}s. {{ outputs.settings_before.row.total_zones
}} zones configured, {{ outputs.settings_before.row.below_floor_in_scope
}} below the floor in scope ({{
outputs.settings_before.row.below_floor_all }} including internal zones).
Change cap {{ inputs.max_zones_changed }} per run. {% for r in
outputs.audit.rows %}{{ r.internal_zone ? 'INTERNAL ' : '' }}{{ r.target
}}: own gc.ttlseconds {{ r.explicit_ttl }}s, effective {{ r.effective_ttl
}}s, rank {{ r.fix_rank }}{{ r.fix_rank <= inputs.max_zones_changed ? '
(in this pass)' : ' (next pass)' }}. {% endfor %}
- id: route
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.audit.size > 0 }}"
then:
- id: alert
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'CockroachDB ' ~ inputs.jdbc_url ~ ': ' ~ outputs.audit.size ~ ' zone(s) set gc.ttlseconds below the ' ~ inputs.min_gc_ttlseconds ~ 's floor, which can break a backup, a changefeed or an AS OF SYSTEM TIME read that needs more history. RANGE default is ' ~ outputs.settings_before.row.default_ttl ~ 's. ' ~ (outputs.audit.rows | jq('map("\(.target) \(.explicit_ttl)s") | join("; ")') | first) ~ '.' ~ (inputs.auto_remediate and not inputs.dry_run ? ' Asking for approval to raise up to ' ~ inputs.max_zones_changed ~ ' this pass.' : ' No change applied (auto_remediate=' ~ inputs.auto_remediate ~ ', dry_run=' ~ inputs.dry_run ~ ').')} | toJson }}
- id: remediate_gate
type: io.kestra.plugin.core.flow.If
condition: "{{ inputs.auto_remediate and not inputs.dry_run }}"
then:
- id: approval_gate
type: io.kestra.plugin.core.flow.Pause
description: >-
Hold the run until a human approves. Raising gc.ttlseconds keeps
more old MVCC versions, so it never breaks a running read. It does
cost disk on a high churn table and it is a retention policy
decision, so the change never starts on its own.
pauseDuration: PT2H
behavior: FAIL
onResume:
- id: approved
type: BOOL
displayName: Approve the TTL change
description: Set true to raise the listed zones to the floor. Anything else
leaves every zone as it is.
defaults: false
- id: change_ticket
type: STRING
displayName: Change record
defaults: ""
- id: apply
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.approval_gate.onResume.approved ?? false }}"
then:
- id: remediate
type: io.kestra.plugin.core.flow.Loop
description: One ALTER CONFIGURE ZONE at a time. Zone configurations are written
to a single system table, so parallel writes would conflict.
concurrencyLimit: 1
values: "{{ outputs.audit.rows | jq('map(select(.fix_rank <= ' ~
inputs.max_zones_changed ~ ') | .fix_sql)') | first | toJson
}}"
tasks:
- id: raise_one
type: io.kestra.plugin.jdbc.postgresql.Query
description: A rejected statement leaves the loop running so the re-read below
is what decides the run.
allowFailure: true
url: "{{ inputs.jdbc_url }}"
username: "{{ inputs.db_user }}"
password: "{{ inputs.password }}"
fetchType: NONE
sql: "{{ item.value }}"
- id: verify
type: io.kestra.plugin.jdbc.postgresql.Query
description: Read-only. Reads the zone configurations again and checks that
every zone changed this pass now has an effective
gc.ttlseconds at or above the floor.
url: "{{ inputs.jdbc_url }}"
username: "{{ inputs.db_user }}"
password: "{{ inputs.password }}"
fetchType: FETCH_ONE
sql: "{{ render(vars.verify_sql) }}"
- id: verify_result
type: io.kestra.plugin.core.flow.If
condition: "{{ outputs.verify.row.targets_below_floor > 0 }}"
then:
- id: remediation_failed
type: io.kestra.plugin.core.execution.Fail
errorMessage: >-
The re-read after remediation did not come back clean. {{
outputs.verify.row.targets_below_floor }} of {{
outputs.verify.row.targets_checked }} changed zones still
read below the {{ inputs.min_gc_ttlseconds }}s floor{{
outputs.verify.row.laggards == '' ? '' : ': ' ~
outputs.verify.row.laggards }}. A zone that did not move
means the ALTER was rejected, the role lacks ZONECONFIG on
that target or the target was dropped between the audit
and the change, so read the raise_one logs.
else:
- id: remediated
type: io.kestra.plugin.core.log.Log
message: >-
Raised {{ outputs.verify.row.targets_checked }} zone(s) to
at least {{ inputs.min_gc_ttlseconds }}s, cap {{
inputs.max_zones_changed }} per run. Change record {{
outputs.approval_gate.onResume.change_ticket == '' ? 'not
given' : outputs.approval_gate.onResume.change_ticket }}.
Now: {{ outputs.verify.row.target_state }}. RANGE default
{{ outputs.settings_before.row.default_ttl }}s before, {{
outputs.verify.row.default_ttl_after }}s after. {{
outputs.audit.size > inputs.max_zones_changed ?
(outputs.audit.size - inputs.max_zones_changed) ~ '
zone(s) left for the next run.' : 'Nothing left over.' }}
else:
- id: declined
type: io.kestra.plugin.core.log.Log
message: >-
The approval was not given, so no zone was changed. Would have
run: {{ outputs.audit.rows | jq('map(select(.fix_rank <= ' ~
inputs.max_zones_changed ~ ') | .fix_sql) | if length == 0
then "nothing" else join("; ") end') | first }}.
else:
- id: no_change
type: io.kestra.plugin.core.log.Log
message: >-
Reporting only (auto_remediate={{ inputs.auto_remediate }},
dry_run={{ inputs.dry_run }}). Would run: {{ outputs.audit.rows |
jq('map(select(.fix_rank <= ' ~ inputs.max_zones_changed ~ ') |
.fix_sql) | if length == 0 then "nothing" else join("; ") end') |
first }}.
else:
- id: clean
type: io.kestra.plugin.core.log.Log
message: "Every one of the {{ outputs.settings_before.row.total_zones }}
configured zones has gc.ttlseconds at or above the {{
inputs.min_gc_ttlseconds }}s floor. RANGE default is {{
outputs.settings_before.row.default_ttl }}s."
errors:
- id: alert_flow_failure
type: io.kestra.plugin.slack.notifications.SlackIncomingWebhook
url: "{{ secret('SLACK_WEBHOOK_URL') }}"
payload: |
{{ {'text': 'CockroachDB zone GC TTL floor guard failed on ' ~ inputs.jdbc_url ~ ' (execution ' ~ execution.id ~ '). A TTL set too low is invisible until a backup or a changefeed needs the history that was already collected, so a guard that cannot run is worth reading. Check the logs.'} | toJson }}
triggers:
- id: daily
type: io.kestra.plugin.core.trigger.Schedule
description: Shipped disabled. Always runs in safe mode.
cron: "0 2 * * *"
disabled: true
inputs:
auto_remediate: false
dry_run: true