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

Bacula: Device is BLOCKED waiting to create a volume

I’m using Bacula server 5.2.3 for backing up my data, actually backing up more than 20 servers. The last days I am getting this message in bacula server from one of my jobs:

--
12-ene 10:46 bacula-sd JobId 5799: Job Backup-server15.2012-01-12_09.41.51_28 is waiting. Cannot find any appendable volumes.
Please use the "label" command to create a new Volume for:
    Storage:      "FileStorage-server15" (/pools/vol0/server15)
    Pool:         server15-file
    Media type:   File-server15
--

My backups are disk based, each server has it’s own storage and device, and are configured to label volumes automatically. I am getting this error only from one of my jobs, the others are finishing correctly.

I’ve rechecked all configuration for this client, looking for errors but it had the same settings than the others clients and in the past has been running ok. I’ve checked also, that LabelMedia option has set to yes in it’s device config:

#
Device {
  Name = FileStorage-server15
  Media Type = File-server15
  Archive Device = /pools/vol0/server15
  LabelMedia = yes;                   # lets Bacula label unlabeled media
  Random Access = Yes;
  AutomaticMount = yes;               # when device opened, read it
  RemovableMedia = no;
  AlwaysOpen = no;
}
#

When I listed the pool’s volumes for this client I could see one volume in APPEND state, I have configured bacula to use one volume per job.

*list media pool=server15-file

--
+---------+-----------------------+-----------+---------+----------------+----------+--------------+---------+------+-----------+--------------+---------------------+
| MediaId | VolumeName            | VolStatus | Enabled | VolBytes       | VolFiles | VolRetention | Recycle | Slot | InChanger | MediaType    | LastWritten         |
|   7,178 | disk-server157178      | APPEND    |       1 |              0 |        0 |    1,209,600 |       0 |    0 |         0 | File-server15 | 0000-00-00 00:00:00 |
--

I’ve checked that this volume does not exist physically in disk, so I’ve manually purged this volume in bacula catalog. Anyway when a new job is run it has to create a new volume in bacula catalog.

*purge volume=disk-server157178 yes

When I’ve relaunched this job again, a new volume has been added and the job has finished Ok.
I don’t know exactly why I’ve reached this situation, may be because a few days ago I’ve run out of disk space, but I am not sure.


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

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

Notice: Use of undefined constant XML - assumed 'XML' in /home/elkano.org/blog/wp-content/plugins/wp-syntaxhighlighter/wp-syntaxhighlighter.php on line 1048