So I’ve been looking to migrate my bacula installation from MySQL to PostgreSQL. Personally I like PostgreSQL better and the claims on the bacula-users list were that it’s faster. So I did a bunch of reading, and then testing the database conversion and finally have made it through the process. Here is the results of how to do it. The system that I did the migration on is an OpenSUSE 11.2 system.<\/p>\n
I first upgraded my install from bacula 3.0.3 to 5.0.0, still using MySQL. This was a pretty straightforward process. A little different because the packing of the RPMS changed between 3.0.3 and 5.x.<\/p>\n
Backup my existing config files and the database.<\/p>\n
tar -czf \/root\/bacula-backup.tar.gz \/etc\/bacula\r\nmysqldump -u bacula -ppassword bacula > bacula-3.0-mysql.sql<\/pre>\nRemove the old RPMs<\/p>\n
zypper remove bacula bacula-bat bacula-updatedb bacula-server<\/pre>\nInstall the new RPMs<\/p>\n
zypper install bacula-console bacula-console-bat bacula-director-mysql bacula-storage-mysql bacula-client bacula-director-mysql<\/pre>\nUpdate the database<\/p>\n
\/usr\/lib\/bacula\/update_mysql_tables -u bacula -ppassword bacula<\/pre>\nThe next step was the hard one, converting the database. I used a post from the bacula-users list<\/a> to come up with the appropriate mysqldump line. I then repeatedly created the postgreSQL database and tried to import the dump until it imported without serious errors. In the end this is the pipeline that created a good dump:<\/p>\n
mysqldump -t -n -c --compatible=postgresql --skip-quote-names --skip-opt --disable-keys --lock-tables -u bacula -ppassword bacula \\\r\n | grep -v \"INSERT INTO Status\" \\\r\n | sed -e 's\/0000-00-00 00:00:00\/1970-01-01 00:00:00\/g' \\\r\n | sed -e 's\/\\\\0\/\/' > fixed-bacula-backup.sql<\/pre>\nThe mysqldump line is pretty much what was in the mailing list post, except that I did all of the tables at once. The grep is to get rid of inserts into the status table. I was having issues with duplicate keys and such and the bacula-users list assured me that this table is created by the make_postgresql_tables script. The first sed line is to fix some bad dates. MySQL allows a date of all zeros, PostgreSQL doesn’t, so I just bumped the 0 dates to the beginning of the unix epoch. The second sed line removes the extra null characters that showed up on all of the inserts into the log table. I’m not sure what caused these, but PostgreSQL doesn’t like to import them and this made it much happier.<\/p>\n
I then setup a .pgpass file in root’s home directory so that I could secure the postgreSQL database with a password and not put it in my bacula config files. You can learn about the pgpass file in the postgreSQL documentation<\/a>.<\/p>\n
Next it’s just a matter of creating the PostgreSQL tables as the postgres user (or some other user with postgresql superuser privileges)<\/p>\n
.\/create_postgresql_database\r\n.\/make_postgresql_tables\r\n.\/grant_postgresql_privileges<\/pre>\nAnd then loading in the data. This load took a little over an hour and a half on my system, so be prepared to wait a bit.<\/p>\n
psql -Ubacula bacula < fixed-bacula-backup.sql<\/pre>\nNow one needs to reset the sequences that postgreSQL uses to autocreate ids. I started with the instructions in the bacula manual<\/a>, but needed to add a couple of missing sequences.<\/p>\n
SELECT SETVAL('basefiles_baseid_seq', (SELECT MAX(baseid) FROM basefiles));\r\nSELECT SETVAL('client_clientid_seq', (SELECT MAX(clientid) FROM client));\r\nSELECT SETVAL('file_fileid_seq', (SELECT MAX(fileid) FROM file));\r\nSELECT SETVAL('filename_filenameid_seq', (SELECT MAX(filenameid) FROM filename));\r\nSELECT SETVAL('fileset_filesetid_seq', (SELECT MAX(filesetid) FROM fileset));\r\nSELECT SETVAL('job_jobid_seq', (SELECT MAX(jobid) FROM job));\r\nSELECT SETVAL('jobmedia_jobmediaid_seq', (SELECT MAX(jobmediaid) FROM jobmedia));\r\nSELECT SETVAL('media_mediaid_seq', (SELECT MAX(mediaid) FROM media));\r\nSELECT SETVAL('path_pathid_seq', (SELECT MAX(pathid) FROM path));\r\nSELECT SETVAL('basefiles_baseid_seq', (SELECT MAX(baseid) FROM basefiles));\r\nSELECT SETVAL('client_clientid_seq', (SELECT MAX(clientid) FROM client));\r\nSELECT SETVAL('file_fileid_seq', (SELECT MAX(fileid) FROM file));\r\nSELECT SETVAL('filename_filenameid_seq', (SELECT MAX(filenameid) FROM filename));\r\nSELECT SETVAL('fileset_filesetid_seq', (SELECT MAX(filesetid) FROM fileset));\r\nSELECT SETVAL('job_jobid_seq', (SELECT MAX(jobid) FROM job));\r\nSELECT SETVAL('jobmedia_jobmediaid_seq', (SELECT MAX(jobmediaid) FROM jobmedia));\r\nSELECT SETVAL('media_mediaid_seq', (SELECT MAX(mediaid) FROM media));\r\nSELECT SETVAL('path_pathid_seq', (SELECT MAX(pathid) FROM path));\r\nSELECT SETVAL('pool_poolid_seq', (SELECT MAX(poolid) FROM pool));<\/pre>\nUpdates I needed to add:<\/p>\n
SELECT SETVAL('device_deviceid_seq', (SELECT MAX(deviceid) FROM device));\r\nSELECT SETVAL('location_locationid_seq', (SELECT MAX(locationid) FROM location));\r\nSELECT SETVAL('locationlog_loclogid_seq', (SELECT MAX(loclogid) FROM locationlog));\r\nSELECT SETVAL('log_logid_seq', (SELECT MAX(logid) FROM log));\r\nSELECT SETVAL('mediatype_mediatypeid_seq', (SELECT MAX(mediatypeid) FROM mediatype));\r\nSELECT SETVAL('storage_storageid_seq', (SELECT MAX(storageid) FROM storage));<\/pre>\nAfter that I needed to modify the Catalog section in my bacula-dir.conf file to use localhost for the “DB Address”, remove the mysql socket reference and remove the password reference.<\/p>\n
I also needed to modify the backup catalog command to be this (all on one line):<\/p>\n
RunBeforeJob = \"\/usr\/lib\/bacula\/make_catalog_backup bacula bacula \\\"\\\" localhost\"<\/pre>\n","protected":false},"excerpt":{"rendered":"So I’ve been looking to migrate my bacula installation from MySQL to PostgreSQL. Personally I like PostgreSQL better and the claims on the bacula-users list were that it’s faster. So I did a bunch of reading, and then testing the database conversion and finally have made it through the process. Here is the results of … Continue reading Migrating bacula from MySQL to PostgreSQL<\/span>