trunk/sql/backup_parts_consistency_report.sql

-- check consistency of data for one DVD image. This is more detailed than
-- error message from BackupPC_Bur
select
        hosts.name || '_' ||
        shares.name || '_' ||
        backups.num as increment_name,
        backups.date::abstime,
        backups.type,
        backups.size,
        backups.parts,
        count(backup_parts.backup_id) as backup_parts,
        backups.inc_size,
        backups.inc_deleted
from backups
join shares on shareid = shares.id
join hosts on backups.hostid = hosts.id
full outer join backup_parts on backups.id = backup_parts.backup_id
where backups.size > 0
group by
        hosts.name,
        shares.name,
        backups.num,
        backups.date,
        backups.type,
        backups.size,
        backups.parts,
        backups.inc_size,
        backups.inc_deleted
having count(backup_parts.backup_id) <> parts
order by date asc
;
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	;