Preserve recovery records during orphaned pool cleanup

What does this MR do and why?

Pool membership can change while the script runs. A pool with no source project could lose its last member after the recovery CSV is written but before deletion. Previously, that pool could be deleted without a backup.

With this change, that pool survives the current batch because it was not captured in the recovery CSV. The next run can back it up and delete it if it is still eligible. A backed-up pool that gains a source project or member is also kept.

The task also saves the IDs actually deleted to a separate file, so we can compare them with the recovery CSV.

References

• Issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/616939
• Prior MR: !252707 (merged)

How to set up and validate locally

1. In a local GDK Rails console, create three new Shard records named pool-cleanup-demo-a, pool-cleanup-demo-b, and pool-cleanup-demo-c. Leave these names out of the Gitaly storage configuration. Create two pool records on each shard, using the organization of your disposable projects:

   • On each shard, give one pool neither a source nor member projects.
   • Give the second pool on the first shard a source project only; the second pool on the second shard a member project only; and the second pool on the third shard both a source and member project.
   • Set source_project_id on the pool and pool_repository_id on the disposable member project as appropriate. Record the six pool IDs for comparison.

2. Run a dry run from the GitLab directory, using a fresh output path:

   bin/rake gitlab:pool_repositories:cleanup_orphaned_on_missing_shards \
     SHARD_NAMES=pool-cleanup-demo-a,pool-cleanup-demo-b,pool-cleanup-demo-c \
     DRY_RUN=true \
     OUTPUT_FILE=/tmp/pool-cleanup-demo-dry-recovery.csv

   Confirm that the CSV contains the three pools with neither a source nor members, and that all six pool records still exist.

3. Run the cleanup with separate, fresh recovery and audit paths:

   bin/rake gitlab:pool_repositories:cleanup_orphaned_on_missing_shards \
     SHARD_NAMES=pool-cleanup-demo-a,pool-cleanup-demo-b,pool-cleanup-demo-c \
     DRY_RUN=false \
     OUTPUT_FILE=/tmp/pool-cleanup-demo-real-recovery.csv \
     DELETED_IDS_FILE=/tmp/pool-cleanup-demo-real-deleted-ids.csv

   Confirm three recovery records and three deleted IDs, matching the three removed pool records. The source-only, member-only, and source/member controls must survive. Check that the output reports each file's total and path separately. Use new filenames for subsequent runs.

4. Check validation by attempting real runs with no DELETED_IDS_FILE, an existing output file, and the same destination for both files. Also try two paths to the same new filename through a real parent directory and a symlink to that directory. Each attempt should fail before deletion or overwriting an existing file.

Manual results — local GDK, 2026-10-06

Used three unconfigured shards (9, 10, 11) and six pools (138–143):

Shard	Pool	Initial relationships	Normal real-run result
9	138	Neither source nor member	Deleted
9	139	Source project 171	Survived
10	140	Neither source nor member	Deleted
10	141	Member project 172	Survived
11	142	Neither source nor member	Deleted
11	143	Source and member project 173	Survived

• Dry run: three recovery records, no deletions.
• Real run: three recovery records, three deletions, and three audit IDs. The deleted IDs matched 138, 140, and 142.
• Losing a member: paused the shard-10 batch after recovery flush, then unlinked project 172 from pool 141. The batch had zero recovery records and deleted zero rows; pool 141 survived. The next dry run selected it.
• Gaining a member: started with pool 141 eligible, paused after recovery flush, then linked project 172. The batch had one recovery record and deleted zero rows, with the expected mismatch warning; pool 141 survived.

The temporary PAUSE_BEFORE_DELETE hook used for the race checks was removed from the final patch.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

• Automated coverage: 52 examples passed.
• Manual validation: dry/real runs, relationship controls, and both membership race directions checked locally.
• RuboCop passes for all four changed Ruby files.
• Representative Database Lab query plans for the old and new deletion queries.
• Code and database review.