This is about one problem I have, and there's also a patch attached
which might be good to incorporate into future version of Bacula.
I'm in the middle of migrating Bacula database from PostgreSQL to
MySQL, and got into kind of trouble because of different constraints on
the columns in those two databases.
The job table has columns poolid and filesetid defined as NOT NULL in
MySQL, but there is no such constraint in PostgreSQL. Also, Bacula
utilizes foreign keys with MySQL, but not with PostgreSQL. Simmilary,
sqlite and sqlite3 backends also utilize foreign keys. While I was
running on PostgreSQL, I got some entries in job table with those
columns set to NULL. Of course, migration is failing on those.
The jobs in question seems to be either restores or failed jobs:
bacula=# select jobid, type, jobstatus from job where poolid is null or
filesetid is null;
jobid | type | jobstatus
-------+------+-----------
1040 | R | T
373 | R | T
98 | B | C
97 | B | R
99 | B | C
100 | B | C
101 | B | C
1146 | B | R
(8 rows)
Two 'R' jobs are also failed/canceled jobs (for whatever reason, Bacula
never marked them as such).
Should existance of these rows in database be considered bug in Bacula?
When using SQLite or MySQL (if InnoDB engine is used), inserting
those rows into database would fail anynow (as well as thousands of
other rows in file table that depend on them).
Now, I guess I could simply delete those, and also all rows from other
tables that reference these rows (file, jobmedia, basefiles, and
unsavedfiles). My wild guess is that if I used MySQL initially, those
rows wouldn't exist anyhow (inserts would fail). Anyhow, looking at
the file table, there's about 32,000 rows that I'll need to delete...
Hopefully I'm not going to nuke anything too usefull...
However, regardless of this problem I have, it might be good move to
utilize same features on all database backends (as long as they support
them), which would basically mean adding foreign key constrains to
PostgreSQL data definitions (yeah, it might slow database inserts a
bit). I've included a patch that does this. The patch might look
bigger than it really is, since I needed to reorder table creation.
Basically, all it does is adding foreign keys to the places where they
are defined in other backends (used MySQL backend as reference). I
haven't made an update script, since updating PostgreSQL tables to
start using foreign keys might require deletion of live data from
database (I'll leave it to someone with more internal knowledge of
Bacula).
When we are at patching... How about adding ON DELETE CASCADE to table
definitions (wherever foreign keys are used)? This would make some
stuff much easier (for example, the task that is now in front of me) if
database backend supports foreign keys (sqlite and postgresql do, mysql
depending on version and storage engine used).
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
--- bacula-1.38.2/src/cats/make_postgresql_tables.in.orig 2006-02-09 13:16:04.000000000 -0600
+++ bacula-1.38.2/src/cats/make_postgresql_tables.in 2006-02-09 13:50:54.000000000 -0600
@@ -24,29 +24,61 @@
CREATE INDEX path_name_idx on path (path);
-CREATE TABLE file
+CREATE TABLE pool
(
- fileid serial not null,
- fileindex integer not null default 0,
- jobid integer not null,
- pathid integer not null,
- filenameid integer not null,
- markid integer not null default 0,
- lstat text not null,
+ poolid serial not null,
+ name text not null,
+ numvols integer not null default 0,
+ maxvols integer not null default 0,
+ useonce smallint not null default 0,
+ usecatalog smallint not null default 0,
+ acceptanyvolume smallint not null default 0,
+ volretention bigint not null default 0,
+ voluseduration bigint not null default 0,
+ maxvoljobs integer not null default 0,
+ maxvolfiles integer not null default 0,
+ maxvolbytes bigint not null default 0,
+ autoprune smallint not null default 0,
+ recycle smallint not null default 0,
+ pooltype text
+ check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
+ labeltype integer not null default 0,
+ labelformat text not null,
+ enabled smallint not null default 1,
+ scratchpoolid integer default 0 references pool,
+ recyclepoolid integer default 0 references pool,
+ NextPoolId integer default 0 references pool,
+ MigrationHighBytes BIGINT DEFAULT 0,
+ MigrationLowBytes BIGINT DEFAULT 0,
+ MigrationTime BIGINT DEFAULT 0,
+ primary key (poolid)
+);
+
+CREATE INDEX pool_name_idx on pool (name);
+
+CREATE TABLE fileset
+(
+ filesetid serial not null,
+ fileset text not null,
md5 text not null,
- primary key (fileid)
+ createtime timestamp without time zone not null,
+ primary key (filesetid)
);
-CREATE INDEX file_jobid_idx on file (jobid);
-CREATE INDEX file_fp_idx on file (filenameid, pathid);
+CREATE INDEX fileset_name_idx on fileset (fileset);
---
--- Possibly add one or more of the following indexes
--- if your Verifies are too slow.
---
--- CREATE INDEX file_pathid_idx on file(pathid);
--- CREATE INDEX file_filenameid_idx on file(filenameid);
--- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
+CREATE TABLE client
+(
+ clientid serial not null,
+ name text not null,
+ uname text not null,
+ autoprune smallint default 0,
+ fileretention bigint not null,
+ jobretention bigint not null,
+ primary key (clientid)
+);
+
+create unique index client_name_idx on client (name);
CREATE TABLE job
(
@@ -55,7 +87,7 @@
name text not null,
type char(1) not null,
level char(1) not null,
- clientid integer,
+ clientid integer not null references client,
jobstatus char(1) not null,
schedtime timestamp without time zone not null,
starttime timestamp without time zone,
@@ -67,8 +99,8 @@
jobbytes bigint not null default 0,
joberrors integer not null default 0,
jobmissingfiles integer not null default 0,
- poolid integer,
- filesetid integer,
+ poolid integer not null references pool,
+ filesetid integer not null references fileset,
purgedfiles smallint not null default 0,
hasbase smallint not null default 0,
primary key (jobid)
@@ -76,42 +108,43 @@
CREATE INDEX job_name_idx on job (name);
-CREATE TABLE fileset
+CREATE TABLE file
(
- filesetid serial not null,
- fileset text not null,
+ fileid serial not null,
+ fileindex integer not null default 0,
+ jobid integer not null references job,
+ pathid integer not null references path,
+ filenameid integer not null references filename,
+ markid integer not null default 0,
+ lstat text not null,
md5 text not null,
- createtime timestamp without time zone not null,
- primary key (filesetid)
+ primary key (fileid)
);
-CREATE INDEX fileset_name_idx on fileset (fileset);
+CREATE INDEX file_jobid_idx on file (jobid);
+CREATE INDEX file_fp_idx on file (filenameid, pathid);
-CREATE TABLE jobmedia
-(
- jobmediaid serial not null,
- jobid integer not null,
- mediaid integer not null,
- firstindex integer not null default 0,
- lastindex integer not null default 0,
- startfile integer not null default 0,
- endfile integer not null default 0,
- startblock bigint not null default 0,
- endblock bigint not null default 0,
- volindex integer not null default 0,
- copy integer not null default 0,
- stripe integer not null default 0,
- primary key (jobmediaid)
-);
+--
+-- Possibly add one or more of the following indexes
+-- if your Verifies are too slow.
+--
+-- CREATE INDEX file_pathid_idx on file(pathid);
+-- CREATE INDEX file_filenameid_idx on file(filenameid);
+-- CREATE INDEX file_jpfid_idx on file (jobid, pathid, filenameid);
-CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
+CREATE TABLE Storage (
+ StorageId SERIAL,
+ Name TEXT NOT NULL,
+ AutoChanger INTEGER DEFAULT 0,
+ PRIMARY KEY(StorageId)
+ );
CREATE TABLE media
(
mediaid serial not null,
volumename text not null,
slot integer not null default 0,
- poolid integer not null,
+ poolid integer not null references pool,
mediatype text not null,
labeltype integer not null default 0,
firstwritten timestamp without time zone,
@@ -137,7 +170,7 @@
maxvolfiles integer not null default 0,
maxvolbytes bigint not null default 0,
inchanger smallint not null default 0,
- StorageId integer default 0,
+ StorageId integer not null default 0 references storage,
mediaaddressing smallint not null default 0,
volreadtime bigint not null default 0,
volwritetime bigint not null default 0,
@@ -148,7 +181,25 @@
create unique index media_volumename_id on media (volumename);
-
+CREATE TABLE jobmedia
+(
+ jobmediaid serial not null,
+ jobid integer not null references job,
+ mediaid integer not null references media,
+ firstindex integer not null default 0,
+ lastindex integer not null default 0,
+ startfile integer not null default 0,
+ endfile integer not null default 0,
+ startblock bigint not null default 0,
+ endblock bigint not null default 0,
+ volindex integer not null default 0,
+ copy integer not null default 0,
+ stripe integer not null default 0,
+ primary key (jobmediaid)
+);
+
+CREATE INDEX job_media_job_id_media_id_idx on jobmedia (jobid, mediaid);
+
CREATE TABLE MediaType (
MediaTypeId SERIAL,
MediaType TEXT NOT NULL,
@@ -156,18 +207,11 @@
PRIMARY KEY(MediaTypeId)
);
-CREATE TABLE Storage (
- StorageId SERIAL,
- Name TEXT NOT NULL,
- AutoChanger INTEGER DEFAULT 0,
- PRIMARY KEY(StorageId)
- );
-
CREATE TABLE Device (
DeviceId SERIAL,
Name TEXT NOT NULL,
- MediaTypeId INTEGER NOT NULL,
- StorageId INTEGER NOT NULL,
+ MediaTypeId INTEGER NOT NULL REFERENCES mediatype,
+ StorageId INTEGER NOT NULL REFERENCES storage,
DevMounts INTEGER NOT NULL DEFAULT 0,
DevReadBytes BIGINT NOT NULL DEFAULT 0,
DevWriteBytes BIGINT NOT NULL DEFAULT 0,
@@ -183,52 +227,6 @@
);
-CREATE TABLE pool
-(
- poolid serial not null,
- name text not null,
- numvols integer not null default 0,
- maxvols integer not null default 0,
- useonce smallint not null default 0,
- usecatalog smallint not null default 0,
- acceptanyvolume smallint not null default 0,
- volretention bigint not null default 0,
- voluseduration bigint not null default 0,
- maxvoljobs integer not null default 0,
- maxvolfiles integer not null default 0,
- maxvolbytes bigint not null default 0,
- autoprune smallint not null default 0,
- recycle smallint not null default 0,
- pooltype text
- check (pooltype in ('Backup','Copy','Cloned','Archive','Migration','Scratch')),
- labeltype integer not null default 0,
- labelformat text not null,
- enabled smallint not null default 1,
- scratchpoolid integer default 0,
- recyclepoolid integer default 0,
- NextPoolId integer default 0,
- MigrationHighBytes BIGINT DEFAULT 0,
- MigrationLowBytes BIGINT DEFAULT 0,
- MigrationTime BIGINT DEFAULT 0,
- primary key (poolid)
-);
-
-CREATE INDEX pool_name_idx on pool (name);
-
-CREATE TABLE client
-(
- clientid serial not null,
- name text not null,
- uname text not null,
- autoprune smallint default 0,
- fileretention bigint not null,
- jobretention bigint not null,
- primary key (clientid)
-);
-
-create unique index client_name_idx on client (name);
-
-
CREATE TABLE counters
(
counter text not null,
@@ -244,19 +242,19 @@
CREATE TABLE basefiles
(
baseid serial not null,
- jobid integer not null,
- fileid integer not null,
+ jobid integer not null references job,
+ fileid integer not null references file,
fileindex integer ,
- basejobid integer ,
+ basejobid integer not null references job,
primary key (baseid)
);
CREATE TABLE unsavedfiles
(
UnsavedId integer not null,
- jobid integer not null,
- pathid integer not null,
- filenameid integer not null,
+ jobid integer not null references job,
+ pathid integer not null references path,
+ filenameid integer not null references filename,
primary key (UnsavedId)
);
