Skip to main content

Bacula: migrate database from mysql to postgresql

I’ve followed these steps to migrate bacula database from mysql 5.1.49 to postgresql 8.4.9. I am running bacula 5.2.3 compiled from sources on Debian Squeeze.

1- Install postgresql database:


# apt-get install postgresql-8.4 postgresql-server-dev-8.4 postgresql-client-8.4

2- Compile and install bacula with postgresql support:

# cd /usr/src/bacula-5.2.5
# ./configure --with-postgresql --with-openssl --with-python
# make install

3- Create bacula user on postgresql:

# su - postgres
# psql -d template1 -U postgres
template1=# CREATE USER bacula WITH PASSWORD 'myPassword';

4- Create bacula database

# sh /etc/bacula/create_postgresql_database

5- Create database tables

# sh /etc/bacula/make_postgresql_tables

6- Grant privilejes on bacula database to bacula user.

# sh /etc/bacula/grant_postgresql_privileges

7- take dump file from mysql. I’ve used this script to take the mysql dump and change some fields, like zero timestamp that does not fit with postgresql.


#!/bin/perl -w

my $line=undef;
my $catalog="bacula";


system ("mysqldump --single-transaction --compatible=postgresql --compact --no-create-info $catalog > $catalog.sql");
print "$catalog dump completed!\n";
  
open FD_CAT, "<$catalog.sql";
open FD_OUT, ">out-$catalog.sql";
while (<FD_CAT>)
{
 if (m/^(INSERT\s+INTO\s+\")(\w+)(\"\s+.+)$/){          
   $line=$1.lc($2).$3;
   $line=~s/\((\d+)\,\'/\($1\,e\'/g;    
   $line=~ s/\\\'/\'\'/g;
   $line=~ s/\'0000-00-00 00:00:00\'/to_timestamp\(0\)/g;
   print FD_OUT "$line\n";
 }else{
   print "line not found: $_";
 } 

}
close (FD_CAT);
close (FD_OUT);
print "Dump filtered commpleted for $catalog\n";


8- Import dump file into postgresql

# psql -d bacula -f out-bacula.sql

9- Change the catalog definition in bacula-dir.conf from mysql to postgresql

Edit /etc/bacula-dir.conf and change driver and port

dbdriver = "dbi:postgresql"; dbaddress = 127.0.0.1; dbport =5432

10- Restart bacula director.

# /etc/init.d/bacula-dir restart

One thought to “Bacula: migrate database from mysql to postgresql”

  1. This was helpful except you left out one key thing:
    http://www.bacula.org/5.0.x-manuals/en/main/main/Installing_Configuring_Post.html

    The database must be re sequenced. If this is not done, your jobs will fail.

    This is for RHEL based installs. Bacula 7 uses libbaccats.so instead of dbi connections. You have to change this to use the postgresql entry:

    alternatives –set libbaccats.so
    /usr/lib64/libbaccats-postgresql.so

    Refer to this entry:
    http://koji.pidora.ca/koji/fileinfo?rpmID=176777&filename=quickstart_postgresql.txt

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.


Notice: Undefined variable: wp_sh_class_name in /var/www/elkano.org/blog/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1002

Notice: Undefined variable: wp_sh_class_name in /var/www/elkano.org/blog/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1002

Warning: Use of undefined constant XML - assumed 'XML' (this will throw an Error in a future version of PHP) in /var/www/elkano.org/blog/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1048