Re: [Bacula-users] SQLite - no longer supported

On 26/02/2011 11:50 AM, Dan Langille wrote:
> On 2/25/2011 10:54 AM, C M Reinehr wrote:
>> On Thu 24 February 2011 07:27:48 pm Dan Langille wrote:
>>> On 2/24/2011 10:08 AM, C M Reinehr wrote:
>>>> On Wed 23 February 2011 10:30:11 pm Dan Langille wrote:
>>>>> A recent bug report (http://bugs.bacula.org/view.php?id=1688) which
>>>>> indicated that we are no longer supporting SQLite.
>>>>>
>>>>> I feel it's safe to stop regression testing against it.
>>>> I'm sorry to hear that. I've been using SQLite for the entire time that
>>>> I've been using Bacula -- ten years or so -- with absolutely no
>>>> difficulty. Admitedly, my needs are limited -- three servers&   eight pc's
>>>> -- but it's been simple to administer&   troubleshoot and reliable.
>>> What version of Bacula are you using now?  What version of SQLite?
>> I stick with Debian Stable and still am running Lenny. The Bacula version is
>> 2.4.4 and the SQLite version is 3.5.9. In the near future I'll be upgrading
>> to Squeeze and it looks as if that will be Bacula v5.0.2&  SQLite v3.7.3.
>> (So, in any case, I guess I'm good for another two years or so, until the
>> next Debian Stable is released! ;-)
> I've been running daily regression tests for MySQL, PostgreSQL, and 
> SQLite.  The latter crashes far more than the other two.  Look at 
> http://regress.bacula.org/ and look for langille.  You'll see.

I recently switched from SQLite3 to PostgreSQL on a Debian system.
I was already using the Bacula 5.0.2 packages, and moved from SQLite3 3.7.5 to 
PostgreSQL 9.0.3.
Attached is a perl script I wrote to do the migration with.
The scripts in the Bacula source examples area did not work for me.
I have only tested it on my system, so it may not work for you.
I haven't had a system that has gone through lots of previous versions of 
Bacula that could have old unused tables in the database
etc...
Use at your own risk.

The steps to do the conversion are:

- Make sure you have plenty of disk space somewhere.
  You'll need enough for your SQLite3 dump; plus approximately that size again 
for data file produced by the conversion; plus enough
space for the PostgreSQL database.
  My SQLite3 dump was 7GB which produced an 11GB PostgreSQL database.

- Keep a safe copy of your configuration files in the /etc/bacula/ directory.

- As the bacula user, dump the SQLite3 database by running: 
/etc/bacula/scripts/make_catalog_backup.pl MyCatalog
  You can become the bacula user by running: su -s /bin/bash - bacula

- Move the /var/lib/bacula/bacula.sql dump file produced from the step above to 
where you have plenty of disk space.

- Remove (or purge) your bacula-common-sqlite3, bacula-director-sqlite3 and 
bacula-sd-sqlite3 packages via apt-get, or aptitude, or
whatever tool you use to manage your Debian packages with.

- Install the postgresql, bacula-common-pgsql, bacula-director-pgsql, 
bacula-sd-pgsql packages.
  This creates the bacula database for you with all the correct tables etc.

- Shut down the newly installed bacula director and storage daemons.
  eg. service bacula-director stop ; service bacula-sd stop

- In the directory where you moved the bacula.sql file to, as the bacula user, 
run my attached script passing the bacula.sql file as
a parameter.
  eg. ./bacula_sqlite3_to_pgsql.pl bacula.sql

- The conversion process will begin and it can take a long time.

- If successful you shouldn't see any errors at all.
  If not, you'll have to address the errors by fixing the script and run it 
again.
  Each time it runs it regenerates the data files, truncates the appropriate 
PostgreSQL tables and loads the data into them again.
  When the data is loaded into the tables, the serial type columns have their 
sequence numbers updated so that new inserted data
will have the correct serial numbers to cause no overlap.

- You'll need to merge your /etc/bacula/ configuration files back into place.
  In my case, the only change to my original configuration files I needed to 
make was to Catalog {} section setting the correct
database parameters.

- Start up the bacula director and storage daemon.

- Test.

I hope this is helpful to someone out there.

Obviously you should probably test this on a virtual machine or something 
before you do it for real.
If all goes bad, you could reinstall your bacula-*-sqlite3 packages again and 
import the bacula.sql dump into them to back out.
Although I didn't try this as my conversion worked okay.

Regards,

----------
Jim Barber
DDI Health

bacula_sqlite3_to_pgsql.pl
Description: Text document

------------------------------------------------------------------------------
Colocation vs. Managed Hosting
A question and answer guide to determining the best fit
for your organization - today and in the future.
http://p.sf.net/sfu/internap-sfd2d

_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users