Re: [Bacula-users] bacula and SQLite
> On Tue, 23 Jul 2019 09:23:10 -0500, Dimitri Maziuk via Bacula-users said: > > On 7/23/2019 6:02 AM, Martin Simmons wrote: > > > If this is being caused by the PathVisibility table then you could try to > > either delete that table if it is empty or alter its schema definition to > > use > > bigint instead of int8 and integer instead of int4. (I'm assuming you are > > working with a copy of the bacula.db file, not the live one.) > > If this is reading from sql dump, s/int8/bigint/g before the conversion > may work I think it reads the SQLite database directly. __Martin ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 7/23/2019 6:02 AM, Martin Simmons wrote: If this is being caused by the PathVisibility table then you could try to either delete that table if it is empty or alter its schema definition to use bigint instead of int8 and integer instead of int4. (I'm assuming you are working with a copy of the bacula.db file, not the live one.) If this is reading from sql dump, s/int8/bigint/g before the conversion may work Dima ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
This looks like a bug in pgloader's type parser so I don't think you fix it with a cast. If this is being caused by the PathVisibility table then you could try to either delete that table if it is empty or alter its schema definition to use bigint instead of int8 and integer instead of int4. (I'm assuming you are working with a copy of the bacula.db file, not the live one.) __Martin > On Mon, 22 Jul 2019 17:03:12 -0700, David Brodbeck said: > > Finally got some time to poke at this again. pgloader looked promising, but > almost immediately I ran into problems with it not understanding some of > SQLite's types: > > What I am doing here? > > At > > int8 > > ^ (Line 1, Column 3, Position 3) > > In context SQLITE-TYPE-NAME: > > While parsing SQLITE-TYPE-NAME. Problem: > > The production > > #\8 > > does not satisfy the predicate ALPHA-CHAR-P. > > > I haven't tried to manually specify a cast yet, but I was curious if anyone > else had run into this. > > > On Fri, May 10, 2019 at 11:08 AM Dimitri Maziuk via Bacula-users < > bacula-users@lists.sourceforge.net> wrote: > > > On 5/9/19 5:41 PM, David Brodbeck wrote: > > > On Wed, May 1, 2019 at 3:14 PM Phil Stracchino > > wrote: > > > > >> Surely you could have just bscanned the media you had? > > >> > > > > ... Obviously now that > > > the SQLite rug is going to be pulled out from under me I may have to > > > revisit the bscan idea. > > > > I haven't tried this myself, this is purely theoretical, etc., etc., but > > there's this: https://github.com/dimitri/pgloader -- just don't delete > > the dump file at the end of catalog backup job, create postgres database > > using bacula's scripts, and see if you can get that dump file in. > > > > Of course if you only have a couple of volumes, on-disk, bscan'ing them > > in will be faster. > > -- > > Dimitri Maziuk > > Programmer/sysadmin > > BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu > > > > ___ > > Bacula-users mailing list > > Bacula-users@lists.sourceforge.net > > https://lists.sourceforge.net/lists/listinfo/bacula-users > > > > > -- > David Brodbeck > System Administrator, Department of Mathematics > University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Finally got some time to poke at this again. pgloader looked promising, but almost immediately I ran into problems with it not understanding some of SQLite's types: What I am doing here? > At > int8 > ^ (Line 1, Column 3, Position 3) > In context SQLITE-TYPE-NAME: > While parsing SQLITE-TYPE-NAME. Problem: > The production > #\8 > does not satisfy the predicate ALPHA-CHAR-P. I haven't tried to manually specify a cast yet, but I was curious if anyone else had run into this. On Fri, May 10, 2019 at 11:08 AM Dimitri Maziuk via Bacula-users < bacula-users@lists.sourceforge.net> wrote: > On 5/9/19 5:41 PM, David Brodbeck wrote: > > On Wed, May 1, 2019 at 3:14 PM Phil Stracchino > wrote: > > >> Surely you could have just bscanned the media you had? > >> > > ... Obviously now that > > the SQLite rug is going to be pulled out from under me I may have to > > revisit the bscan idea. > > I haven't tried this myself, this is purely theoretical, etc., etc., but > there's this: https://github.com/dimitri/pgloader -- just don't delete > the dump file at the end of catalog backup job, create postgres database > using bacula's scripts, and see if you can get that dump file in. > > Of course if you only have a couple of volumes, on-disk, bscan'ing them > in will be faster. > -- > Dimitri Maziuk > Programmer/sysadmin > BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- David Brodbeck System Administrator, Department of Mathematics University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 5/9/19 5:41 PM, David Brodbeck wrote: > On Wed, May 1, 2019 at 3:14 PM Phil Stracchino wrote: >> Surely you could have just bscanned the media you had? >> ... Obviously now that > the SQLite rug is going to be pulled out from under me I may have to > revisit the bscan idea. I haven't tried this myself, this is purely theoretical, etc., etc., but there's this: https://github.com/dimitri/pgloader -- just don't delete the dump file at the end of catalog backup job, create postgres database using bacula's scripts, and see if you can get that dump file in. Of course if you only have a couple of volumes, on-disk, bscan'ing them in will be faster. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Wed, May 1, 2019 at 3:14 PM Phil Stracchino wrote: > On 5/1/19 5:57 PM, David Brodbeck wrote: > > To be honest, my mistake was I didn't realize that by "testing purposes" > > they meant "a temporary, throwaway system where you delete everything > > before moving on to production." By the time I decided Bacula was doing > > the job for me and it was time to move to a more robust backend, I had > > too much backup data to just start over from scratch. I didn't want to > > have to tell someone "I had a backup of that, but I deleted it." > > Basically I failed to look two steps ahead to see if there would be a > > migration path forward. > > Surely you could have just bscanned the media you had? > Yes, but I considered that a last resort, since bscan appears to be for recovering from Dire Emergencies and I wasn't clear on whether it'd give me a consistent catalog going forward. Since the system was actually working fine with SQLite, it seemed better to just keep going. Obviously now that the SQLite rug is going to be pulled out from under me I may have to revisit the bscan idea. -- David Brodbeck System Administrator, Department of Mathematics University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 5/2/19 1:35 PM, Tilman Schmidt wrote: > Am 02.05.2019 um 00:14 schrieb Phil Stracchino: >> Surely you could have just bscanned the media you had? > > s/just/hired a few temps who'd/ :-) Might still be cheaper than hiring one database guru to hand-edit catalog.sql for psql. ;) -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Am 02.05.2019 um 00:14 schrieb Phil Stracchino: > Surely you could have just bscanned the media you had? s/just/hired a few temps who'd/ :-) ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 5/1/19 5:57 PM, David Brodbeck wrote: > To be honest, my mistake was I didn't realize that by "testing purposes" > they meant "a temporary, throwaway system where you delete everything > before moving on to production." By the time I decided Bacula was doing > the job for me and it was time to move to a more robust backend, I had > too much backup data to just start over from scratch. I didn't want to > have to tell someone "I had a backup of that, but I deleted it." > Basically I failed to look two steps ahead to see if there would be a > migration path forward. Surely you could have just bscanned the media you had? -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
To be honest, my mistake was I didn't realize that by "testing purposes" they meant "a temporary, throwaway system where you delete everything before moving on to production." By the time I decided Bacula was doing the job for me and it was time to move to a more robust backend, I had too much backup data to just start over from scratch. I didn't want to have to tell someone "I had a backup of that, but I deleted it." Basically I failed to look two steps ahead to see if there would be a migration path forward. I haven't yet had an SQLite problem I couldn't fix with the command line tools, in spite of once running out of disk space on the partition the DB was on. My biggest complaint about it is if you have backups with millions of files, operations on them can tie up the director for quite a while. That's the main reason I'd like to switch to a proper DB server. On Mon, Apr 29, 2019 at 8:54 AM Phil Stracchino wrote: > > > "For testing purposes." > > Was SQLite *ever* actually *recommended* for production? > -- David Brodbeck System Administrator, Department of Mathematics University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 5/1/19 3:47 AM, Kern Sibbald wrote: > Aside from the work it takes to maintain the code, please be aware that if > ever > a SQLite database > becomes corrupted, to the best of my knowledge there are no tools to correct > it. That is a valid argument, however, the fix for that is rather simple: don't run /usr/libexec/bacula/delete_catalog_backup after BackupCatalog. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Wed, May 1, 2019, at 18:23, Dan Langille wrote: > Please move from SQLite to PostgreSQL. You will be better off. The problem is that we are lacking a documented way to do so. -- Tilman Schmidt til...@imap.cc ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Wed, May 1, 2019, at 4:59 AM, Kern Sibbald wrote: > Hello, > > Just so everyone knows how I feel about SQLite -- it is a great program, > written by a very good programmer. It is probably closer to Postgres > than to MySQL in its syntax. An aside... There is a reason for that similarity. When SQLite was being written, PostgreSQL was the reference for what to do. D. Richard Hipp gave the keynote talk at PGCon 2014. I was present. The slides are at: https://www.pgcon.org/2014/schedule/events/736.en.html > However, it is really not an appropriate > database for many reasons for Bacula, and maintaining the update tables > scripts is very time consuming. I will continue to keep the source code > as long as Sven and others contribute what is necessary to keep it > working. In that talk, you will find these quotes: “SQLite is not a replacement for PostgreSQL. SQLite is a replacement for fopen().” "Use PostgreSQL as an enterprise data depot, and use SQLite for application files." The Catalog is definitely not an application file. Please move from SQLite to PostgreSQL. You will be better off. ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, Just so everyone knows how I feel about SQLite -- it is a great program, written by a very good programmer. It is probably closer to Postgres than to MySQL in its syntax. However, it is really not an appropriate database for many reasons for Bacula, and maintaining the update tables scripts is very time consuming. I will continue to keep the source code as long as Sven and others contribute what is necessary to keep it working. Now to the point of my email: There is a migration script in /examples/database that should convert an SQLite database to Postgres. I have no idea if it works. If any of you have working scripts (I think I read about one recently), please attach it to a bug report (best way to ensure it is not lost). If I get any more scripts, I will happily commit them to the examples directory. Best regards, Kern On 4/30/19 8:03 AM, Josip Deanovic wrote: > On Monday 2019-04-29 14:36:07 Dimitri Maziuk via Bacula-users wrote: >> On 4/29/19 1:57 PM, Josip Deanovic wrote: >>> Because of the reasons Kern already wrote. >> Yes, but they have nothing to do with the question. > It does. It implies that it is not supported by the authors/maintainers > of the Bacula and therefore such setup is not future-proof. > > Not that I wouldn't like to see the SQLite continue although I don't > use it (I like to have as many options as possible) but if it complicates > maintenance and authors/maintainers are not ready to continue it, I > don't see any other option but discontinue as it would only hurt people > who try to use it and rely on it in the future while the support breaks > again and there will be nobody to fix it (the last good people from Debian > jumped in and made it work again). > > Maybe an effort is needed to help people to move from the SQLite > to mysql/postgres. E.g. some migration scripts or something. > I am not sure if those exist already. I think somebody said that > they don't exist. > > > Regards! > ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, I want to second this email, and thank Sven and the others for maintaining SQLite in Bacula. I will probably make one more release of 9.4.x which will have SQLite in it, but please note that the next version of Bacula will have a major change to the database catalog, and unless someone works *very* hard, SQLite will not be supported in that version. I will announce when the new database version is ready for testing, so that if the community would like to keep SQLite working, there should be sufficient time before the release. Aside from the work it takes to maintain the code, please be aware that if ever a SQLite database becomes corrupted, to the best of my knowledge there are no tools to correct it. For MySQL and Postgres there are tools that work quite well. Best regards, Kern On 4/29/19 9:16 PM, Sven Hartge wrote: On 29.04.19 20:57, Josip Deanovic wrote: It's not that SQLite is bad or does not have the needed capabilities but it simply adds to the complexity of the source and maintenance of bacula features. Also besides Carsten Leonhardt and me from Debian nobody has been testing or even maintaining the SQLite database scripts. SQLite is only available still because we tested and submitted patches to keep it working. Grüße, Sven. ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Monday 2019-04-29 14:36:07 Dimitri Maziuk via Bacula-users wrote: > On 4/29/19 1:57 PM, Josip Deanovic wrote: > > Because of the reasons Kern already wrote. > > Yes, but they have nothing to do with the question. It does. It implies that it is not supported by the authors/maintainers of the Bacula and therefore such setup is not future-proof. Not that I wouldn't like to see the SQLite continue although I don't use it (I like to have as many options as possible) but if it complicates maintenance and authors/maintainers are not ready to continue it, I don't see any other option but discontinue as it would only hurt people who try to use it and rely on it in the future while the support breaks again and there will be nobody to fix it (the last good people from Debian jumped in and made it work again). Maybe an effort is needed to help people to move from the SQLite to mysql/postgres. E.g. some migration scripts or something. I am not sure if those exist already. I think somebody said that they don't exist. Regards! -- Josip Deanovic ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 29.04.19 20:57, Josip Deanovic wrote: > It's not that SQLite is bad or does not have the needed capabilities > but it simply adds to the complexity of the source and maintenance of > bacula features. Also besides Carsten Leonhardt and me from Debian nobody has been testing or even maintaining the SQLite database scripts. SQLite is only available still because we tested and submitted patches to keep it working. Grüße, Sven. signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 4/29/19 1:57 PM, Josip Deanovic wrote: > Because of the reasons Kern already wrote. Yes, but they have nothing to do with the question. > It's not that SQLite is bad or does not have the needed capabilities Precisely: back when it was supported there was nothing wrong with using it, provided one understood how it scales. -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Monday 2019-04-29 12:01:03 Dimitri Maziuk via Bacula-users wrote: > On 4/29/19 10:54 AM, Phil Stracchino wrote: > > Was SQLite *ever* actually *recommended* for production? > > For a small dataset with a very limited number of volumes and backup > cycles, why not? Because of the reasons Kern already wrote. It's not that SQLite is bad or does not have the needed capabilities but it simply adds to the complexity of the source and maintenance of bacula features. Regards! -- Josip Deanovic ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 4/29/19 10:54 AM, Phil Stracchino wrote: > Was SQLite *ever* actually *recommended* for production? For a small dataset with a very limited number of volumes and backup cycles, why not? -- Dimitri Maziuk Programmer/sysadmin BioMagResBank, UW-Madison -- http://www.bmrb.wisc.edu signature.asc Description: OpenPGP digital signature ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 4/29/19 11:49 AM, David Brodbeck wrote: > > > On Sat, Apr 20, 2019 at 4:32 AM Gary R. Schmidt > mailto:g...@mcleod-schmidt.id.au>> wrote: > > SQLite was deprecated in Bacula something of the order of ten years > ago, > which is about when I installed Bacula at my current $ORK. > > > Interestingly, the 9.0.4 release notes say that sqlite has been > deprecated "for a long time," but I haven't been able to find exactly > when this was announced. In fact as recently as v7.4 the installation > chapter in the manual recommended starting out with SQLite for testing > purposes. The most recent v7.4 release came out in 2017, a lot less than > 10 years ago. "For testing purposes." Was SQLite *ever* actually *recommended* for production? -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958 ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On Sat, Apr 20, 2019 at 4:32 AM Gary R. Schmidt wrote: > SQLite was deprecated in Bacula something of the order of ten years ago, > which is about when I installed Bacula at my current $ORK. > Interestingly, the 9.0.4 release notes say that sqlite has been deprecated "for a long time," but I haven't been able to find exactly when this was announced. In fact as recently as v7.4 the installation chapter in the manual recommended starting out with SQLite for testing purposes. The most recent v7.4 release came out in 2017, a lot less than 10 years ago. -- David Brodbeck System Administrator, Department of Mathematics University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, sob., 20 kwi 2019 o 12:33 Tilman Schmidt napisał(a): > Hi Radosław, > > On Fri, Apr 19, 2019, at 11:40, Radosław Korzeniewski wrote: > > What "resources" are you referring to? My current phone has 64bit OS and > 4GB of RAM (top models has 8G). Having a real bare metal server with > hundreds GB of ram is not a big deal. Storage space is extremely cheap too. > Unless you run your Bacula on wrist watch or 30Y hardware you do not need > to worry about any RDBMS for Bacula catalog. > > > That's the situation today. Of course nobody in his or her right mind > would use SQLite for a new Bacula installation. > > But this thread was originally about an existing installation. Ten years > ago (just as an example, when I did one Bacula installation I am still > running) there were no phones with 4 GB of RAM. You were lucky if you had > that much memory in your server. Back then, SQLite was even the default > backend offered by Bacula during installation. So it was quite reasonable > to use it in a small environment. (Let's say less than 10 servers.) > Yes! Back then SQLite was supported. > > How Great! We removed this legacy in the end! Let's celebrate! :) > > You see it as a loss, OK. I see it as a great step forward. > > > It would be if there was a migration path for all those faithful Bacula > users who've been using and promoting the product for many years and who > have been misled into installing it with the SQLite backend initially. As > it stands, they find themselves in a dead end now. Can't you understand > that they are not in the mood to celebrate? > I fully understand it and you have a full right to be in bad mood. I show a different point of view for the same situation. Yes. It would be the best if a such migration path (FAQ, Whitepaper, How-To, etc.) from SQLite to other RDBMS will be provided. I think nobody realized that are some users who still running Bacula with it. best regards -- Radosław Korzeniewski rados...@korzeniewski.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
David Brodbeck wrote: I've been wondering the same thing, especially since there seems to be no official way to migrate an SQLite database to PostgreSQL or MariaDB. I'm actually not opposed to doing using one of those (I feel like a "real" RDBMS might offer better performance and stability in my case -- my installation has grown to about 60 clients) but everyone who's done it seems to have rolled their own script to do the conversion. Descriptions of the process in the mailing list archives are pretty vague, and I'm not confident I understand the Bacula schema well enough to pull it off safely. I wrote my own tiny script now: #!/bin/sh -e date sqlite3 /var/db/bacula/bacula.db '.dump' > sqlite.dump echo 'SET FOREIGN_KEY_CHECKS=0;' > mysql.dump grep INSERT sqlite.dump | grep -vE '^INSERT INTO (Status|Version)' >> mysql.dump echo 'SET FOREIGN_KEY_CHECKS=1;' >> mysql.dump /usr/bin/time -l mysql bacula < mysql.dump date Output: root@arborio /root> sh sqlite2mysql.sh Mon Apr 22 11:26:54 CEST 2019 mysql: Unknown OS character set 'ISO8859-15'. mysql: Switching to the default character set 'latin1'. 28664.84 real40.49 user54.83 sys 6848 maximum resident set size 2459 average shared memory size 866 average unshared data size 126 average unshared stack size 411 page reclaims 43 page faults 0 swaps 30 block input operations 0 block output operations 2520338 messages sent 2520338 messages received 0 signals received 2520411 voluntary context switches 3615 involuntary context switches Mon Apr 22 19:24:55 CEST 2019 root@arborio /root> wc -l mysql.dump 2520335 mysql.dump root@arborio /root> So it took nearly 8hrs to process 2,520,335 inserts. Prior to the process the "create-table" script for MySQL provided by Bacula was executed. Best regards, Oliver ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
On 20/04/2019 20:14, Tilman Schmidt wrote: Hi Radosław, On Fri, Apr 19, 2019, at 11:40, Radosław Korzeniewski wrote: What "resources" are you referring to? My current phone has 64bit OS and 4GB of RAM (top models has 8G). Having a real bare metal server with hundreds GB of ram is not a big deal. Storage space is extremely cheap too. Unless you run your Bacula on wrist watch or 30Y hardware you do not need to worry about any RDBMS for Bacula catalog. That's the situation today. Of course nobody in his or her right mind would use SQLite for a new Bacula installation. But this thread was originally about an existing installation. Ten years ago (just as an example, when I did one Bacula installation I am still running) there were no phones with 4 GB of RAM. You were lucky if you had that much memory in your server. Back then, SQLite was even the default backend offered by Bacula during installation. So it was quite reasonable to use it in a small environment. (Let's say less than 10 servers.) How Great! We removed this legacy in the end! Let's celebrate! :) You see it as a loss, OK. I see it as a great step forward. It would be if there was a migration path for all those faithful Bacula users who've been using and promoting the product for many years and who have been misled into installing it with the SQLite backend initially. As it stands, they find themselves in a dead end now. Can't you understand that they are not in the mood to celebrate? SQLite was deprecated in Bacula something of the order of ten years ago, which is about when I installed Bacula at my current $ORK. I did not even consider SQLite as the back-end for Bacula, not because there was anything wrong with SQLite, I had used it previously and it was and is in use with our current products, it was just not suited to Bacula in 2009. Why? Because MySQL was available, and stable, and offloads the proverbial metric tonne of processing, and on a machine with 4Gb or RAM (I still have the box, but it no longer does the Bacula thing, that's now done on a 72Gb system with an FC tape) it just made so much more sense. Maybe my time writing databases (as in the underlying code) has given me a different view on things, but I don't consider transferring data between different databases a difficult thing, merely fiddly and a bit time consuming. I switched our system from MySQL to PostGRES eighteen months ago, and after a bit of fiddling with the options of "mysqldump" and some judicious use of "grep -v" and "sed" it just worked. There are about 1,500,000,000 entries in the "file" table, it took a couple of hours, but what's time to a computer? SQLite is a brilliant tool for single-user environments, but less brilliant where you have an OS that allows you to do more things. SQLite tracks PostGRES development as a guide to where it should go next, moving from SQLite to PostGRES should be quite easy, possibly the output of ".dump" (| grep -v "CREATE TABLE") would be very compatible. Cheers, GaryB-) ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hi Radosław, On Fri, Apr 19, 2019, at 11:40, Radosław Korzeniewski wrote: > What "resources" are you referring to? My current phone has 64bit OS and 4GB > of RAM (top models has 8G). Having a real bare metal server with hundreds GB > of ram is not a big deal. Storage space is extremely cheap too. Unless you > run your Bacula on wrist watch or 30Y hardware you do not need to worry about > any RDBMS for Bacula catalog. That's the situation today. Of course nobody in his or her right mind would use SQLite for a new Bacula installation. But this thread was originally about an existing installation. Ten years ago (just as an example, when I did one Bacula installation I am still running) there were no phones with 4 GB of RAM. You were lucky if you had that much memory in your server. Back then, SQLite was even the default backend offered by Bacula during installation. So it was quite reasonable to use it in a small environment. (Let's say less than 10 servers.) > How Great! We removed this legacy in the end! Let's celebrate! :) > > You see it as a loss, OK. I see it as a great step forward. It would be if there was a migration path for all those faithful Bacula users who've been using and promoting the product for many years and who have been misled into installing it with the SQLite backend initially. As it stands, they find themselves in a dead end now. Can't you understand that they are not in the mood to celebrate? Regards, Tilman -- Tilman Schmidt til...@imap.cc ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, pt., 19 kwi 2019 o 13:32 Oliver Lehmann napisał(a): > Hi Radoslaw, > > thanks for your mail. > > I would understand that if the developers say that they don't > want to support SQLite because of the extra work it creates > for them to support it. But this would be more or less the > only reason I could accept ;-) > As I mention in my email, I do not know the exact reason why Kern dropped SQLite support from Bacula. What I show in my email was my and only my suspicious and opinion. > > SQLite is a good solution for an embedded RDBMS. > Yes, you are absolutely right! But Bacula is no longer an embedded solution... best regards -- Radosław Korzeniewski rados...@korzeniewski.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, The principal reason for dropping SQLite is as Radek stated -- it is simply extra overhead that we do not need. This is particularly evident when we have database schema changes. Doing schema changes with an existing database can be quite complicated and prone to errors with SQLite, while with MySQL and Postgres, it is generally very easy. So mainly it is a matter of not having the time and community input to continue to support SQLite. In addition, though I prefer postgres, using MySQL is trivial. Install mysql, then install Bacula with the mysql driver and it works out of the box. Though the complexity of MySQL is more than SQLite, the ease of installation with Bacula is essentially identical. Best regards, Kern On 4/19/19 1:32 PM, Oliver Lehmann wrote: Hi Radoslaw, thanks for your mail. I would understand that if the developers say that they don't want to support SQLite because of the extra work it creates for them to support it. But this would be more or less the only reason I could accept ;-) SQLite is a good solution for an embedded RDBMS. If it just comes to some CRUD operation for a single application it is the easiest solution when it comes to maintenance and complexity of systems. You should know, that with each piece you add to your landscape, the complexity gains and the reliability as well as the availability goes down. So - SQLite is perfect here for me. It is just a library. No server which needs to be up and monitored for correct working. No configuration. Easy upgradeable. Of course in big installations which trillions of files and where performance really matters - SQLite is no alternative. In this environment, you probably have already an RDBMS sitting around which is activly monitored, and tweaked anyway. You will always use this instead of bringing another solution which does "the same" on-line. But in small installations, why would someone wants to setup and maintain a RDBMS for just a backup solution? When it comes to limits: https://www.sqlite.org/limits.html Of course there might be some SQL syntax which is not supported by SQLite - this could be a criteria. But let me tell you - if it is just "get the work done" and not caring about best performance - every SQL compatibily issue can be worked around. Says the since-2003-database-developer. I definitly like to have small units of systems with the least possible dependencies between each other. Performance together with bacula is not a matter for me. A Full-Backup of my Samba node handles 620k files with a Transfer rate of 66.89 MB/sec at a Image size of 1.874 TB. It takes around 7:47 hours for this backup. Running with SQLite. If it might speed up to 7 hours is not crucial for me. System simplicity is more important to me for a backup solution than performance. The while SQLite DB-file is 310MB in size. I just back this up instead of dumping the content and save the dump. This is also perfect in case of disaster recovery. You just need to get the DB file back and are back working in some seconds. No need to install and configure a RDBMS Just my two cents. I guess the decision is already made anyway and not open to discussion any more. I just wanted to state that I'm sad with this decision - but this is my problem ;) Best regards, Oliver ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hi Radoslaw, thanks for your mail. I would understand that if the developers say that they don't want to support SQLite because of the extra work it creates for them to support it. But this would be more or less the only reason I could accept ;-) SQLite is a good solution for an embedded RDBMS. If it just comes to some CRUD operation for a single application it is the easiest solution when it comes to maintenance and complexity of systems. You should know, that with each piece you add to your landscape, the complexity gains and the reliability as well as the availability goes down. So - SQLite is perfect here for me. It is just a library. No server which needs to be up and monitored for correct working. No configuration. Easy upgradeable. Of course in big installations which trillions of files and where performance really matters - SQLite is no alternative. In this environment, you probably have already an RDBMS sitting around which is activly monitored, and tweaked anyway. You will always use this instead of bringing another solution which does "the same" on-line. But in small installations, why would someone wants to setup and maintain a RDBMS for just a backup solution? When it comes to limits: https://www.sqlite.org/limits.html Of course there might be some SQL syntax which is not supported by SQLite - this could be a criteria. But let me tell you - if it is just "get the work done" and not caring about best performance - every SQL compatibily issue can be worked around. Says the since-2003-database-developer. I definitly like to have small units of systems with the least possible dependencies between each other. Performance together with bacula is not a matter for me. A Full-Backup of my Samba node handles 620k files with a Transfer rate of 66.89 MB/sec at a Image size of 1.874 TB. It takes around 7:47 hours for this backup. Running with SQLite. If it might speed up to 7 hours is not crucial for me. System simplicity is more important to me for a backup solution than performance. The while SQLite DB-file is 310MB in size. I just back this up instead of dumping the content and save the dump. This is also perfect in case of disaster recovery. You just need to get the DB file back and are back working in some seconds. No need to install and configure a RDBMS Just my two cents. I guess the decision is already made anyway and not open to discussion any more. I just wanted to state that I'm sad with this decision - but this is my problem ;) Best regards, Oliver ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
Hello, śr., 10 kwi 2019 o 02:37 David Brodbeck napisał(a): > I've been wondering the same thing, especially since there seems to be no > official way to migrate an SQLite database to PostgreSQL or MariaDB. I'm > actually not opposed to doing using one of those (I feel like a "real" > RDBMS might offer better performance and stability in my case -- my > installation has grown to about 60 clients) but everyone who's done it > seems to have rolled their own script to do the conversion. Descriptions of > the process in the mailing list archives are pretty vague, and I'm not > confident I understand the Bacula schema well enough to pull it off safely. > > I'm hoping if they decide to pull the plug on SQLite completely they'll > provide some kind of migration path for those of us who made the > (apparently bad) decision to choose it early on, when it was supported. > > > On Fri, Mar 29, 2019 at 11:51 AM Oliver Lehmann > wrote: > >> Hi, >> >> I just migrated my Bacula installation from 7 to 9. >> During executing the DB schema update Tool, I noticed, that SQLite got >> deprecated. >> >> I wonder why? :( >> > The exact reason should be explained by Kern itself. Below is my thoughts about it: SQLite is a very limited "database" engine. Bacula internals requires ("support" depends from the point of view) a dedicated SQL queries prepared for every catalog backend. Maintaining this backend requires more and more work and brings no real value to Bacula. It is possible that developers faced a technical limitation with SQLite during new feature development. Today a single "standard" workstation could generate more then 6M files in catalog database, so even with an extremely small home network you can reach millions of entries catalog level. This level is not a target for SQLite, sorry. I doubt it return any time. > >> I didn't wanted to install, maintain a full RDBMS which eats up >> ressources and might contain security issues just to get my backups >> going. > > :) What "resources" are you referring to? My current phone has 64bit OS and 4GB of RAM (top models has 8G). Having a real bare metal server with hundreds GB of ram is not a big deal. Storage space is extremely cheap too. Unless you run your Bacula on wrist watch or 30Y hardware you do not need to worry about any RDBMS for Bacula catalog. > I seriously do not care about backup performance. It is a home >> installation and a full backup only contains around 1 TB of data from >> 4 systems (some of them virtual). >> >> Does this now mean I have to get MySQL (what is with MariaDB?) or >> PGSQL installed just for keeping Bacula working in 10 or whatever >> version SQLite support will be completly removed? >> >> How sad :( >> > How Great! We removed this legacy in the end! Let's celebrate! :) You see it as a loss, OK. I see it as a great step forward. best regards -- Radosław Korzeniewski rados...@korzeniewski.net ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] bacula and SQLite
I've been wondering the same thing, especially since there seems to be no official way to migrate an SQLite database to PostgreSQL or MariaDB. I'm actually not opposed to doing using one of those (I feel like a "real" RDBMS might offer better performance and stability in my case -- my installation has grown to about 60 clients) but everyone who's done it seems to have rolled their own script to do the conversion. Descriptions of the process in the mailing list archives are pretty vague, and I'm not confident I understand the Bacula schema well enough to pull it off safely. I'm hoping if they decide to pull the plug on SQLite completely they'll provide some kind of migration path for those of us who made the (apparently bad) decision to choose it early on, when it was supported. On Fri, Mar 29, 2019 at 11:51 AM Oliver Lehmann wrote: > Hi, > > I just migrated my Bacula installation from 7 to 9. > During executing the DB schema update Tool, I noticed, that SQLite got > deprecated. > > I wonder why? :( > > I didn't wanted to install, maintain a full RDBMS which eats up > ressources and might contain security issues just to get my backups > going. I seriously do not care about backup performance. It is a home > installation and a full backup only contains around 1 TB of data from > 4 systems (some of them virtual). > > Does this now mean I have to get MySQL (what is with MariaDB?) or > PGSQL installed just for keeping Bacula working in 10 or whatever > version SQLite support will be completly removed? > > How sad :( > > Best regards, > Oliver > > > > ___ > Bacula-users mailing list > Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > -- David Brodbeck System Administrator, Department of Mathematics University of California, Santa Barbara ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
[Bacula-users] bacula and SQLite
Hi, I just migrated my Bacula installation from 7 to 9. During executing the DB schema update Tool, I noticed, that SQLite got deprecated. I wonder why? :( I didn't wanted to install, maintain a full RDBMS which eats up ressources and might contain security issues just to get my backups going. I seriously do not care about backup performance. It is a home installation and a full backup only contains around 1 TB of data from 4 systems (some of them virtual). Does this now mean I have to get MySQL (what is with MariaDB?) or PGSQL installed just for keeping Bacula working in 10 or whatever version SQLite support will be completly removed? How sad :( Best regards, Oliver ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users