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