+
-- This script does the same as dbcheck, but in full SQL in order to be faster
-- To run it, exec it like this : psql -U bacula bacula (YOUR username and database)
-- then \i dbckeck.sql
--- It will tell you what it does. At the end you'll have to commit yourself. Check the numbers of altered records before ...
+-- It will tell you what it does. At the end you'll have to commit yourself.
+-- Check the numbers of altered records before ...
+--
+-- Notes from Marc Cousin, the author of this script: 01Sep08
+-- The script version won't work better with mysql without indexes.
+
+-- The reason is that the script works with global queries instead of many small
+-- queries like dbcheck. So PostgreSQL can optimise the query by building hash
+-- joins or merge joins.
+
+-- Mysql can't do that (last time I checked, at least ...), and will do nested
+-- loops between job and file for instance. And without the missing indexes,
+-- mysql will still be as slow as with dbcheck, as you'll more or less have
+----thousands of full scans on the job table (where postgresql will do only a few
+-- to build its hash).
+
+-- So for dbcheck with mysql, there is no other solution than adding the missing
+-- indexes (but adding and dropping them just for the dbcheck is a good option).
--repair_bad_paths():
--- - SELECT PathId,Path from Path "
--- "WHERE Path NOT LIKE '%/'
+-- - SELECT PathId,Path from Path "
+-- "WHERE Path NOT LIKE '%/'
-- - ask for confirmation
-- - add a slash, doing one update for each record to be updated ...
--
\t
\a
BEGIN;
+-- Uncomment to raise to '1GB' or more to get better results
+-- SET work_mem TO '1GB';
SELECT('eliminate_admin_records()');
DELETE FROM Job WHERE Job.Type='D';
SELECT('eliminate_duplicate_filenames()');
CREATE TEMP TABLE t1 AS SELECT Name,min(FileNameId) AS minfilenameid FROM FileName GROUP BY Name HAVING count(*) > 1;
+ANALYSE t1;
CREATE TEMP TABLE t2 AS SELECT FileName.Name, FileName.FileNameId, t1.minfilenameid from FileName join t1 ON (FileName.Name=t1.Name) WHERE FileNameId <> minfilenameid;
+ANALYSE t2;
UPDATE File SET FileNameId=(SELECT t2.minfilenameid FROM t2 WHERE t2.FileNameId=File.FileNameId) WHERE FileNameId IN (SELECT FileNameId FROM t2);
DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t2);
DROP TABLE t1;
SELECT('eliminate_duplicate_paths()');
CREATE TEMP TABLE t1 AS SELECT Path,min(PathId) AS minpathid FROM Path GROUP BY Path HAVING count(*) > 1;
+ANALYSE t1;
CREATE TEMP TABLE t2 AS SELECT Path.Path, Path.PathId, t1.minpathid from Path join t1 ON (Path.Path=t1.Path) WHERE PathId <> minpathid;
+ANALYSE t2;
UPDATE Path SET PathId=(SELECT t2.minpathid FROM t2 WHERE t2.PathId=Path.PathId) WHERE PathId IN (SELECT PathId FROM t2);
DELETE FROM Path WHERE PathId IN (SELECT PathId FROM t2);
DROP TABLE t1;
SELECT Path.PathId
FROM Path LEFT OUTER JOIN File ON (Path.PathId=File.PathId)
WHERE File.PathId IS NULL;
+ANALYSE t1;
DELETE FROM Path WHERE PathId IN (SELECT PathID FROM t1);
DROP TABLE t1;
SELECT FileName.FileNameId
FROM FileName LEFT OUTER JOIN File ON (FileName.FileNameId=File.FileNameId)
WHERE File.FileNameId IS NULL;
+ANALYSE t1;
DELETE FROM FileName WHERE FileNameId IN (SELECT FileNameId FROM t1);
DROP TABLE t1;