Ad-hoc shell script to delete invalid entries of Synapse remote media cache from the database; use if you naively nuked the media repository dir thinking Synapse is smart and will just fetch stuff again

cleanup.sh

#!/bin/sh
for i in "media_store/remote_content remote_media_cache" "media_store/remote_thumbnail remote_media_cache_thumbnails"; do (
	fgxd() { dir="$1"; table="$2"; }
	fgxd $i
	cd "$dir";
	sudo -u postgres psql -U synapse -A -c 'select '"CONCAT(media_origin,'/',filesystem_id)"' from '"$table"';' |
		tail -n+2 | head -n-1 | sed -E 's|/(..)(..)|/\1/\2/|' |
		while read line; do (
			if stat "$line" >/dev/null 2>&1; then
				:;
			else
				printf '%s\n' 'delete from '"$table"' where filesystem_id = '"'$(printf '%s' "$line" | sed -E 's|[^/]*/(..)/(..)/(.*)|\1\2\3|')';";
				printf '%s\n' "$line" >&2;
			fi
		) | ( tail -fn1 ) & done | tee /dev/stderr | sudo -u postgres psql -U synapse -A; wait
); done