Check index integrity / recompute quota

Sometimes the used space is wrong and may look like this:

See also: https://github.com/sismics/docs/issues/345

Checks on file system

cd /var/docs/
 
find ./ -type f -name '*' -exec du -ch {} + | grep total$
find ./ -type f -name '*_web' -exec du -ch {} + | grep total$
find ./ -type f -name '*_thumb' -exec du -ch {} + | grep total$
 
cd /var/docs/storage/
ll |grep -v "thumb\|web" |wc -l #Anzahl der Dateien, die weder _web noch _thumb sind
ll | grep "web" | wc -l         #Anzahl _web Dateien (sollte idealerweise mit _thumb deckend sein)
ll | grep "thumb" | wc -l       #Anzahl _thumb Dateien (sollte idealerweise mit _web deckend sein)

Checks in Database / Recalculate Quota

Get a list of all files from database which should exist/not exist on filesystem

/*Get all files which should be existent on HDD*/
SELECT
fil_id_c AS "FileID",
fil_iduser_c AS "Besitzer"
FROM t_file
WHERE fil_deletedate_d IS NULL
;
 
/*Get all files which should be deteled from HDD*/
SELECT
fil_id_c AS "FileID",
fil_iduser_c AS "Besitzer"
FROM t_file
WHERE fil_deletedate_d IS NOT NULL
;

Get the filesystem storage list

The following commands generate file output and send them by mail to you. If your application server and your database are on the same server you just can create some bash script to make some complete scripting solution automating psql.

cd /var/docs/
 
#get recent storage list as CSV
ll | grep -v "thumb\|web" | awk 'NR > 3 {print "\"",$5,"\";\"",$9,"\""}' | sed 's/[[:blank:]]//g' | mail -s "Teedy FileSystem" your@mail.address
 
#or get it as SQL statement
ll | grep -v "thumb\|web" | awk 'NR > 3 {print "UPDATE#TMP_QUOTA_CHECK#SET#fil_size=\x27",$5,"\x27#WHERE#fil_id_c=\x27",$9,"\x27;"}' | sed 's/[[:blank:]]//g' | sed 's/#/ /g' | mail -s "Teedy FileSystem" your@mail.address

Create a temporary SQL table to calculate quota

CREATE TABLE TMP_QUOTA_CHECK(
    fil_id_c  character varying(36),
    fil_iduser_c  character varying(36),
    fil_size integer
);

Pre-Fill the table with existing data

INSERT INTO TMP_QUOTA_CHECK SELECT
fil_id_c,
fil_iduser_c
FROM t_file
WHERE fil_deletedate_d IS NULL
;

Insert file size data from upper generated SQL UPDATE awk statements (bash), then perform some check and calculate total quota sizes

SELECT * FROM tmp_quota_check WHERE fil_size IS NOT NULL;
SELECT * FROM tmp_quota_check WHERE fil_size IS NULL; --if your filesystem is consistent this must be empty! If not please check if Teedy failed to delete files in the past
 
SELECT
    CONCAT('UPDATE t_user SET use_storagecurrent_n=''',SUM(fil_size),''' WHERE use_id_c =''',fil_iduser_c,''';')
FROM TMP_QUOTA_CHECK
GROUP BY fil_iduser_c
;

Insert the new values into existing target table using the generated output statements from above

Drop temporary table

DROP TABLE TMP_QUOTA_CHECK;

Removed commit

https://github.com/sismics/docs/commit/d0335b6b161058250ec8cc44eeb2357f96176f54#diff-54e77110186f974f07aeb29c2673496fL690