#
# Useful SQL statements for content-based backup
# http://256stuff.com/gray/docs/content_based_backup/
#

# if you are running on a different port, connect to the backup db
psql -p 5433 --username=backup backup

# show backups
select date(created),id,collection,"full",new_size,dup_size,new_size + dup_size as total from backups order by created;

# delete all files not in any recognizable backups
delete from files where backup not in (select id from backups);

# show number of files per backup
select backup,count(backup) from files group by backup;

# show files by type
select type,count(type) from files group by type order by count desc;

# delete incrementals from backups if 2 weeks old
delete from backups where not "full" and created < NOW() - INTERVAL '2 weeks';