From: Dan L. <da...@la...> - 2003-05-15 19:55:58
|
One of my plans is to create a PostgreSQL option for bacula. The first step in this plan is a database diagram. I am using PowerDesigner DataArchitect from Sybase. The diagram at present is incomplete and missing information. It is available for review and comment from http://www.freebsddiary.org/tmp/bacula-database.jpg. This diagram contains two tables not seen in the mySQL database: pool_type volume_status This moves from the ENUM type to a standard lookup table. The field definitions need to be confirmed and perhaps optimized (smallint, bigint, bigserial, etc). But I think I have captured the relations between the various tables. It's a starting point. -- Dan Langille : http://www.langille.org/ |
From: Kern S. <ke...@si...> - 2003-05-15 20:29:51
|
Hello Dan, That's a really cool diagram. The fact that only two extra tables showed up pleases me because it means that I did a good job of manually normalizing the tables. In the case of volume_status, in MySQL, it is an ENUM field, so it is essentially normalized, and in the case of pool_type, I should have made it an ENUM, and in any case, it isn't a large enough amount of text to worry about. I notice that all the CHAR fields came across as <undefined>, and for some reason the table file got everything defined as <undefined>. If you are going to use this chart, pay careful attention to certain variable such as max_vol_bytes, which is a 64 bit integer. Best regards, Kern PS: I'm a bit concerned about the one file that did not get put back correctly. What sort of problem did it have? Normally, even if it is already there from a previous restore, the restore would overwrite it unless you specifically specified otherwise. On Thu, 2003-05-15 at 21:54, Dan Langille wrote: > One of my plans is to create a PostgreSQL option for bacula. The > first step in this plan is a database diagram. I am using > PowerDesigner DataArchitect from Sybase. The diagram at present is > incomplete and missing information. It is available for review and > comment from http://www.freebsddiary.org/tmp/bacula-database.jpg. > > This diagram contains two tables not seen in the mySQL database: > > pool_type > volume_status > > This moves from the ENUM type to a standard lookup table. > > The field definitions need to be confirmed and perhaps optimized > (smallint, bigint, bigserial, etc). But I think I have captured the > relations between the various tables. > > It's a starting point. |
From: Dan L. <da...@la...> - 2003-05-15 20:44:14
|
On 15 May 2003 at 22:29, Kern Sibbald wrote: > Hello Dan, > > That's a really cool diagram. The fact that only two extra > tables showed up pleases me because it means that I did a good > job of manually normalizing the tables. I haven > In the case of volume_status, > in MySQL, it is an ENUM field, so it is essentially normalized, and in > the case of pool_type, I should have made it an ENUM, and in any case, > it isn't a large enough amount of text to worry about. Pool Type is an ENUM too: $ grep PoolType /usr/ports/sysutils/bacula-1.30cvs/work/bacula-1.30cvs/src/cats/make_mysql_tables PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration') NOT NULL, > I notice that all the CHAR fields came across as <undefined>, > and for some reason the table file got everything defined as > <undefined>. I did the diagram entirely by hand. Nothing was automated. I just haven't finished it. All character fields will use the PostgreSQL type "text" (variable unlimited length). I hope to get back to this soon. http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=dataty pe-character.html > If you are going to use this chart, pay careful attention to > certain variable such as max_vol_bytes, which is a 64 bit > integer. Thanks. I'll use BIGINT, which is 8 bytes: http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=dataty pe.html#DATATYPE-NUMERIC -- Dan Langille : http://www.langille.org/ |
From: Kern S. <ke...@si...> - 2003-05-15 21:03:15
|
Hello Dan, On Thu, 2003-05-15 at 22:44, Dan Langille wrote: > On 15 May 2003 at 22:29, Kern Sibbald wrote: > > > Hello Dan, > > > > That's a really cool diagram. The fact that only two extra > > tables showed up pleases me because it means that I did a good > > job of manually normalizing the tables. > > I haven > > > In the case of volume_status, > > in MySQL, it is an ENUM field, so it is essentially normalized, and in > > the case of pool_type, I should have made it an ENUM, and in any case, > > it isn't a large enough amount of text to worry about. > > Pool Type is an ENUM too: > > $ grep PoolType /usr/ports/sysutils/bacula-1.30cvs/work/bacula-1.30cvs/src/cats/make_mysql_tables > PoolType ENUM('Backup', 'Copy', 'Cloned', 'Archive', 'Migration') NOT NULL, Oh, I'm happy to see that I was "on the ball" :-) If PostgreSQL does not have an ENUM type, you will probably want to make those two values simply TEXT, which is what I do on SQLite. They don't represent much data, and that saves having to have two different sets of SQL statements. > > > I notice that all the CHAR fields came across as <undefined>, > > and for some reason the table file got everything defined as > > <undefined>. > > I did the diagram entirely by hand. Nothing was automated. Even cooler! > I just haven't finished it. OK, now I understand. > All character fields will use the PostgreSQL > type "text" (variable unlimited length). I hope to get back to this > soon. > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=dataty > pe-character.html > > > If you are going to use this chart, pay careful attention to > > certain variable such as max_vol_bytes, which is a 64 bit > > integer. > > Thanks. I'll use BIGINT, which is 8 bytes: One of the things you will find if you haven't already noticed is that I try to use a minimal set of SQL that works for both SQLite and MySQL. There is only ONE stupid thing that MySQL did that I have not been able to find a single SQL statement that works -- it is the concatenation, which if I remember right is || in ANSI SQL, but MySQL uses || to mean OR (turkeys!). Their concatenation is CONCAT(x, y). > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=dataty > pe.html#DATATYPE-NUMERIC |
From: Dan L. <da...@la...> - 2003-05-15 21:46:46
|
On 15 May 2003 at 23:00, Kern Sibbald wrote: > Hello Dan, > > On Thu, 2003-05-15 at 22:44, Dan Langille wrote: > > On 15 May 2003 at 22:29, Kern Sibbald wrote: > > > > > Hello Dan, > > > > > > That's a really cool diagram. The fact that only two extra > > > tables showed up pleases me because it means that I did a good job > > > of manually normalizing the tables. > > > > I haven I meant to say: I don't know enough about the data to do any analysis of the normalization. > If PostgreSQL does not have an ENUM type, you will probably want > to make those two values simply TEXT, which is what I do on SQLite. > They don't represent much data, and that saves having to have two > different sets of SQL statements. I think it's better to have the lookup tables. It's just set up data. > > All character fields will use the PostgreSQL > > type "text" (variable unlimited length). I hope to get back to this > > soon. > > > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=data > > ty pe-character.html > > > > > If you are going to use this chart, pay careful attention to > > > certain variable such as max_vol_bytes, which is a 64 bit > > > integer. > > > > Thanks. I'll use BIGINT, which is 8 bytes: > > One of the things you will find if you haven't already noticed is that > I try to use a minimal set of SQL that works for both SQLite and > MySQL. There is only ONE stupid thing that MySQL did that I have not > been able to find a single SQL statement that works -- it is the > concatenation, which if I remember right is || in ANSI SQL, but MySQL > uses || to mean OR (turkeys!). Their concatenation is CONCAT(x, y). It should be easy enough to have one SQL for PostgreSQL and another for mySQL, even if the differences are minor. I'll look into the concatenation issue. -- Dan Langille : http://www.langille.org/ |
From: Kern S. <ke...@si...> - 2003-05-15 21:59:36
|
Hello Dan, On Thu, 2003-05-15 at 23:46, Dan Langille wrote: > On 15 May 2003 at 23:00, Kern Sibbald wrote: > > > Hello Dan, > > > > On Thu, 2003-05-15 at 22:44, Dan Langille wrote: > > > On 15 May 2003 at 22:29, Kern Sibbald wrote: > > > > > > > Hello Dan, > > > > > > > > That's a really cool diagram. The fact that only two extra > > > > tables showed up pleases me because it means that I did a good job > > > > of manually normalizing the tables. > > > > > > I haven > > I meant to say: I don't know enough about the data to do any analysis > of the normalization. > > > If PostgreSQL does not have an ENUM type, you will probably want > > to make those two values simply TEXT, which is what I do on SQLite. > > They don't represent much data, and that saves having to have two > > different sets of SQL statements. > > I think it's better to have the lookup tables. It's just set up > data. > > > > All character fields will use the PostgreSQL > > > type "text" (variable unlimited length). I hope to get back to this > > > soon. > > > > > > http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=data > > > ty pe-character.html > > > > > > > If you are going to use this chart, pay careful attention to > > > > certain variable such as max_vol_bytes, which is a 64 bit > > > > integer. > > > > > > Thanks. I'll use BIGINT, which is 8 bytes: > > > > One of the things you will find if you haven't already noticed is that > > I try to use a minimal set of SQL that works for both SQLite and > > MySQL. There is only ONE stupid thing that MySQL did that I have not > > been able to find a single SQL statement that works -- it is the > > concatenation, which if I remember right is || in ANSI SQL, but MySQL > > uses || to mean OR (turkeys!). Their concatenation is CONCAT(x, y). > > It should be easy enough to have one SQL for PostgreSQL and another > for mySQL, even if the differences are minor. I'll look into the > concatenation issue. The problem is that it means #ifdef's which I try VERY hard to avoid as it makes the code MUCH harder to read (at least for me). In one case, the difference is in query.sql a source file that Bacula reads, and there I cannot do #ifdefing. On your other email: I have never tested a file split across three tapes, so there may be a problem, but given that splitting across two tapes has always worked (in all my tests), three tapes should work too. |
From: Dan L. <da...@la...> - 2003-05-16 10:36:27
|
On 15 May 2003 at 23:59, Kern Sibbald wrote: > > > One of the things you will find if you haven't already noticed is > > > that I try to use a minimal set of SQL that works for both SQLite > > > and MySQL. There is only ONE stupid thing that MySQL did that I > > > have not been able to find a single SQL statement that works -- it > > > is the concatenation, which if I remember right is || in ANSI SQL, > > > but MySQL uses || to mean OR (turkeys!). Their concatenation is > > > CONCAT(x, y). > > > > It should be easy enough to have one SQL for PostgreSQL and another > > for mySQL, even if the differences are minor. I'll look into the > > concatenation issue. > > The problem is that it means #ifdef's which I try VERY hard to avoid > as it makes the code MUCH harder to read (at least for me). In one > case, the difference is in query.sql a source file that Bacula reads, > and there I cannot do #ifdefing. What about handling that by ./configure? > On your other email: I have never tested a file split across three > tapes, so there may be a problem, but given that splitting across two > tapes has always worked (in all my tests), three tapes should work > too. When the files are restored, they are different sizes: # ls -l bacula-test-files/one-large-file/largefile2 bacula- retore/home/dan/bacula-test-files/one-large-file/largefile2 -rw-r--r-- 1 dan dan 6253611500 May 9 13:21 bacula- retore/home/dan/bacula-test-files/one-large-file/largefile2 -rw-r--r-- 1 dan dan 6257478656 May 9 13:21 bacula-test-files/one- large-file/largefile2 -- Dan Langille : http://www.langille.org/ |
From: Kern S. <ke...@si...> - 2003-05-16 10:52:21
|
Hello Dan, On Fri, 2003-05-16 at 12:36, Dan Langille wrote: > On 15 May 2003 at 23:59, Kern Sibbald wrote: > > > > > One of the things you will find if you haven't already noticed is > > > > that I try to use a minimal set of SQL that works for both SQLite > > > > and MySQL. There is only ONE stupid thing that MySQL did that I > > > > have not been able to find a single SQL statement that works -- it > > > > is the concatenation, which if I remember right is || in ANSI SQL, > > > > but MySQL uses || to mean OR (turkeys!). Their concatenation is > > > > CONCAT(x, y). > > > > > > It should be easy enough to have one SQL for PostgreSQL and another > > > for mySQL, even if the differences are minor. I'll look into the > > > concatenation issue. > > > > The problem is that it means #ifdef's which I try VERY hard to avoid > > as it makes the code MUCH harder to read (at least for me). In one > > case, the difference is in query.sql a source file that Bacula reads, > > and there I cannot do #ifdefing. > > What about handling that by ./configure? Yes, it is quite possible, but a bit painful. One would need two files and the ability to select between them. Not too hard to do, but not really a standard ./configure feature. I'll think about this a bit. > > > On your other email: I have never tested a file split across three > > tapes, so there may be a problem, but given that splitting across two > > tapes has always worked (in all my tests), three tapes should work > > too. > > When the files are restored, they are different sizes: > > # ls -l bacula-test-files/one-large-file/largefile2 bacula- > retore/home/dan/bacula-test-files/one-large-file/largefile2 > -rw-r--r-- 1 dan dan 6253611500 May 9 13:21 bacula- > retore/home/dan/bacula-test-files/one-large-file/largefile2 > -rw-r--r-- 1 dan dan 6257478656 May 9 13:21 bacula-test-files/one- > large-file/largefile2 Here are a few possibilities/questions: 1. Your large file has a hole in it, and thus Bacula saved and restored the hole making it larger. 2. Was any other process using the file? 3. This file is bigger than 4GB, which means the file addresses do not fit into 32 bits. Maybe there is some subtle problem here. I have written backup files larger than 4GB, but never tested saving/restoring such a large file. 4. What options did you use on your Include record? e.g. compression, sparse, ... 5. Is there anything special about this file? I.e. what kind of data does it contain? I'd like to try simulating it here but before I run a bunch of tests, I'd like to try to make it as similar as possible to your file. 6. Do you know if Bacula simply appended additional data, or is the file really corrupt? An interesting test would be to truncate the restored file to the same size as the original file and then see if the two are the same. Best regards, Kern |
From: Dan L. <da...@la...> - 2003-05-16 11:54:59
|
On 16 May 2003 at 12:49, Kern Sibbald wrote: > Here are a few possibilities/questions: > > 1. Your large file has a hole in it, and thus Bacula saved > and restored the hole making it larger. It was created via a dd from /dev/random, something like this: dd if=/dev/urandom of=largefile-other count=100000 > 2. Was any other process using the file? No, never, well, not as far as I know. > 3. This file is bigger than 4GB, which means the > file addresses do not fit into 32 bits. Maybe there is > some subtle problem here. I have written backup files > larger than 4GB, but never tested saving/restoring such > a large file. OK. > 4. What options did you use on your Include record? > e.g. compression, sparse, ... Just the default values. Here's an example, with the directory commented out: # List of files to be backed up FileSet { Name = "Full Set" Include = signature=MD5 { # # Put your list of files here, one per line or include an # external list with: # # <file-name # # Note: / backs up everything on the root partition. # if you have other partitons such as /usr or /home # you will probably want to add them too. # # By default this is defined to point to the Bacula build # directory to give a reasonable FileSet to backup to # disk storage during initial testing. # # /usr/src # /usr/obj /usr/ports # /home/dan/bacula-test-files } > 5. Is there anything special about this file? I.e. > what kind of data does it contain? I'd like to try > simulating it here but before I run a bunch of tests, > I'd like to try to make it as similar as possible to > your file. Nothing AFAIK, it's just from /dev/random. > 6. Do you know if Bacula simply appended additional data, > or is the file really corrupt? An interesting test > would be to truncate the restored file to the same size > as the original file and then see if the two are the > same. I hand't noticed that the restored file was larger: [dan@undef:~/bacula-retore/home/dan/bacula-test-files/one-large-file] $ ls -l total 6110040 -rw-r--r-- 1 dan dan 6253611500 May 9 13:21 largefile2 [dan@undef:~/bacula-test-files/one-large-file] $ ls -l total 6113816 -rw-r--r-- 1 dan dan 6257478656 May 9 13:21 largefile2 I'll try a truncate next. I'm just making a copy of the restored file now... -- Dan Langille : http://www.langille.org/ |
From: Dan L. <da...@la...> - 2003-05-16 13:58:04
|
On 16 May 2003 at 12:49, Kern Sibbald wrote: > 6. Do you know if Bacula simply appended additional data, > or is the file really corrupt? An interesting test > would be to truncate the restored file to the same size > as the original file and then see if the two are the > same. Bad news, the truncated restored file does not match the original. [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ ls -l total 12220080 -rw-r--r-- 1 dan dan 6253611500 May 9 13:21 largefile2 -rw-r--r-- 1 root dan 6253611500 May 16 09:25 largefile2.RESTORED.ORIGINAL [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ ls ~/bacula-test-files/one-large-file/largefile2 /home/dan/bacula-test-files/one-large-file/largefile2 [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ ls -l ~/bacula-test-files/one-large-file/largefile2 -rw-r--r-- 1 dan dan 6257478656 May 9 13:21 /home/dan/bacula-test-files/one-large-file/largefile2 [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ truncate -r ~/bacula-test-files/one-large-file/largefile2 largefile2 [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ ls -l total 12220088 -rw-r--r-- 1 dan dan 6257478656 May 16 09:55 largefile2 -rw-r--r-- 1 root dan 6253611500 May 16 09:25 largefile2.RESTORED.ORIGINAL [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ diff ~/bacula-test-files/one-large-file/largefile2 largefile2 Binary files /home/dan/bacula-test-files/one-large-file/largefile2 and largefile2 differ [dan@undef:~/bacula-restore/home/dan/bacula-test-files/one-large-file] $ I'm starting another backup of this file onto different media. -- Dan Langille : http://www.langille.org/ |
From: Dan L. <da...@la...> - 2003-05-15 20:47:14
|
On 15 May 2003 at 22:29, Kern Sibbald wrote: > PS: I'm a bit concerned about the one file that did not get > put back correctly. What sort of problem did it have? > Normally, even if it is already there from a previous > restore, the restore would overwrite it unless you specifically > specified otherwise. I don't know what happened. The restore is running again. Let's see how that goes and we'll do some more testing afterwards. -- Dan Langille : http://www.langille.org/ |