sql/backup_parts_consistency_report.sql

   1 -- check consistency of data for one DVD image. This is more detailed than
   2 -- error message from BackupPC_Bur
   3 select
   4         hosts.name || '_' ||
   5         shares.name || '_' ||
   6         backups.num as increment_name,
   7         backups.date::abstime,
   8         backups.type,
   9         backups.size,
  10         backups.parts,
  11         count(backup_parts.backup_id) as backup_parts,
  12         backups.inc_size,
  13         backups.inc_deleted
  14 from backups
  15 join shares on shareid = shares.id
  16 join hosts on backups.hostid = hosts.id
  17 full outer join backup_parts on backups.id = backup_parts.backup_id
  18 where backups.size > 0
  19 group by
  20         hosts.name,
  21         shares.name,
  22         backups.num,
  23         backups.date,
  24         backups.type,
  25         backups.size,
  26         backups.parts,
  27         backups.inc_size,
  28         backups.inc_deleted
  29 having count(backup_parts.backup_id) <> parts
  30 order by date asc
  31 ;