I would like to remove entries from the (Postgres) database and the filesystem, if there's no matching counterpart. I would like to know if this is correct:
(
psql -A -t -c "SELECT 'db ' || media_origin || '/'
|| substring(filesystem_id for 2) || '/'
|| substring(filesystem_id from 3 for 2) || '/'
|| substring(filesystem_id from 5)
FROM remote_media_cache"
find /var/lib/matrix-synapse/media/remote_content -type f -printf 'fs %P\n'
) |sort -t' ' -k2 |uniq -u -s3 | (
list=
while IFS=' ' read -r where what
do
case "$where" in
fs) echo rm -v /var/lib/matrix-synapse/media/remote_content/$what;;
db) list="${list:+$list,}'$what'";;
esac
done
if test -n "$list"
then
psql_t -c "SELECT FROM remote_media_cache
WHERE media_origin || filesystem_id IN ($(echo "$list" |tr -d /))"
fi
)
(
psql -A -t -c "SELECT 'db ' || media_origin || '/'
|| substring(filesystem_id for 2) || '/'
|| substring(filesystem_id from 3 for 2) || '/'
|| substring(filesystem_id from 5) || '/' || thumbnail_width || '-'
|| thumbnail_height || '-' || replace(thumbnail_type, '/', '-') || '-'
|| thumbnail_method
FROM remote_media_cache_thumbnails"
find /var/lib/matrix-synapse/media/remote_thumbnail -type f -printf 'fs %P\n'
) |sort -t' ' -k2 |uniq -u -s3 | (
list=
while IFS=' ' read -r where what
do
case "$where" in
fs) echo rm -v /var/lib/matrix-synapse/media/remote_thumbnail/$what;;
db) list="${list:+$list,}'$what'";;
esac
done
if test -n "$list"
then
echo "SELECT FROM remote_media_cache_thumbnails
WHERE media_origin || filesystem_id || thumbnail_width
|| thumbnail_height || replace(thumbnail_type, '/', '')
|| thumbnail_method IN ($(echo "$list" |tr -d /-))" |tee /tmp/query |psql_t
fi
)At me, this finds
- 0 entries in remote_media_cache not in the filesystem
- 103 entries in the filesystem not in remote_media_cache
- 28559 (out of 66183) in remote_media_cache_thumbnails missing in the filesystem
- 605 entries in the filesystem missing in remote_media_cache_thumbnails
I would like to remove entries from the (Postgres) database and the filesystem, if there's no matching counterpart. I would like to know if this is correct:
( psql -A -t -c "SELECT 'db ' || media_origin || '/' || substring(filesystem_id for 2) || '/' || substring(filesystem_id from 3 for 2) || '/' || substring(filesystem_id from 5) FROM remote_media_cache" find /var/lib/matrix-synapse/media/remote_content -type f -printf 'fs %P\n' ) |sort -t' ' -k2 |uniq -u -s3 | ( list= while IFS=' ' read -r where what do case "$where" in fs) echo rm -v /var/lib/matrix-synapse/media/remote_content/$what;; db) list="${list:+$list,}'$what'";; esac done if test -n "$list" then psql_t -c "SELECT FROM remote_media_cache WHERE media_origin || filesystem_id IN ($(echo "$list" |tr -d /))" fi ) ( psql -A -t -c "SELECT 'db ' || media_origin || '/' || substring(filesystem_id for 2) || '/' || substring(filesystem_id from 3 for 2) || '/' || substring(filesystem_id from 5) || '/' || thumbnail_width || '-' || thumbnail_height || '-' || replace(thumbnail_type, '/', '-') || '-' || thumbnail_method FROM remote_media_cache_thumbnails" find /var/lib/matrix-synapse/media/remote_thumbnail -type f -printf 'fs %P\n' ) |sort -t' ' -k2 |uniq -u -s3 | ( list= while IFS=' ' read -r where what do case "$where" in fs) echo rm -v /var/lib/matrix-synapse/media/remote_thumbnail/$what;; db) list="${list:+$list,}'$what'";; esac done if test -n "$list" then echo "SELECT FROM remote_media_cache_thumbnails WHERE media_origin || filesystem_id || thumbnail_width || thumbnail_height || replace(thumbnail_type, '/', '') || thumbnail_method IN ($(echo "$list" |tr -d /-))" |tee /tmp/query |psql_t fi )At me, this finds