MySQL to PostgreSQL – a Bacula conversion odyssey

Why is it that always the seemingly most simple things turn out to be the most annoying? This time I “just” wanted to get rid of one of my last MySQL databases and move it over to PostgreSQL. The Bacula catalog that saves which file I backed up when and to which storage medium. I tried with MySQL’s “mysqldump” and it’s PostgreSQL compatibility option – but apparently MySQL developers know nothing about PostgreSQL. Then I tried with “sqlfairy” – and found myself booting my system hard after it ate 2 GB of swap and died while converting 500 MB of data.

So finally I asked in #bacula and was told to try CSV (comma-seperated values) as an intermediate format. Yuck… that satan-spawned format that reminds me of my dark past? Okay. First dump the catalog from MySQL:

for table in BaseFiles CDImages Client Counters Device File Filename FileSet Job JobMedia Location LocationLog Log Media MediaType Path Pool Status Storage UnsavedFiles Version ; do mysqldump -u root -pmypassword -T. bacula $table; done

(Okay, okay, this is not comma- but tab-seperated. But that’s even better for running the COPY-FROM command later.)

This creates an ‘.sql’ (the schema) and a ‘.txt’ (the rows/records) file for each table in the current directory. Just don’t try to apply the schema to PostgreSQL. Instead better create a new schema. Bacula ships with a script for that purpose.

Unless you have the PostgreSQL database for Bacula ready you should run something like…

/usr/share/bacula-director/make_postgresql_tables -h localhost -U bacula

…and…

/usr/share/bacula-director/grant_postgresql_privileges -h localhost -U bacula

Now on to read the tab-delimited data into PostgreSQL. The importing via the COPY command must be done with administrator privileges! And it’s important to explicitly state which columns correspond to which table columns (see the respective ‘.sql’ files) or otherwise you’ll get chaos. Of course this only has to be done for .txt files larger than 0 bytes. Oh, and the filename has to be absolute. Example:

• psql -h localhost bacula admin -c “COPY basefiles (baseid,basejobid,jobid,fileid,fileindex) FROM ‘/var/tmp/BaseFiles.txt'”
• psql -h localhost bacula admin -c “COPY cdimages (mediaid,lastburn) FROM ‘/var/tmp/CDImages.txt'”
• psql -h localhost bacula admin -c “COPY client (ClientId,Name,Uname,AutoPrune,FileRetention,JobRetention) FROM ‘/var/tmp/Client.txt'”
• psql -h localhost bacula admin -c “COPY counters (Counter,MinValue,MaxValue,CurrentValue,WrapCounter) FROM ‘/var/tmp/Counters.txt'”
• psql -h localhost bacula admin -c “COPY device (DeviceId, Name, MediaTypeId, StorageId, DevMounts, DevReadBytes, DevWriteBytes, DevReadBytesSinceCleaning ,DevWriteBytesSinceCleaning, DevReadTime,DevWriteTime, DevReadTimeSinceCleaning, DevWriteTimeSinceCleaning, CleaningDate, CleaningPeriod) FROM ‘/var/tmp/Device.txt'”
• psql -h localhost bacula admin -c “COPY file (FileId, FileIndex, JobId, PathId, FilenameId, MarkId, LStat, MD5) FROM ‘/var/tmp/File.txt'”
  psql -h localhost bacula admin -c “COPY fileset (FileSetId, FileSet, MD5, CreateTime) FROM ‘/var/tmp/FileSet.txt'”
• psql -h localhost bacula admin -c “COPY filename (FilenameId, Name) FROM ‘/var/tmp/Filename.txt'”
• psql -h localhost bacula admin -c “COPY jobmedia (JobMediaId, JobId, MediaId, FirstIndex, LastIndex, StartFile, EndFile, StartBlock, EndBlock, VolIndex, Copy) FROM ‘/var/tmp/JobMedia.txt'”
• psql -h localhost bacula admin -c “COPY location (LocationId, Location, Cost, Enabled) FROM ‘/var/tmp/Location.txt'”
• psql -h localhost bacula admin -c “COPY locationlog (LocLogId, Date, Comment, MediaId, LocationId, NewVolStatus, NewEnabled) FROM ‘/var/tmp/LocationLog.txt'”
• psql -h localhost bacula admin -c “COPY log (LogId,JobId,Time,LogText) FROM ‘/var/tmp/Log.txt'”
• psql -h localhost bacula admin -c “COPY media (MediaId, VolumeName, Slot, PoolId, MediaType, FirstWritten, LastWritten, LabelDate, VolJobs, VolFiles, VolBlocks, VolMounts, VolBytes, VolErrors, VolWrites, VolCapacityBytes, VolStatus, Recycle, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, InChanger, MediaAddressing, VolReadTime, VolWriteTime, EndFile, EndBlock, LabelType, StorageId, VolParts, MediaTypeId, DeviceId, LocationId, RecycleCount, InitialWrite, ScratchPoolId, RecyclePoolId, Enabled, Comment) FROM ‘/var/tmp/Media.txt'”
• psql -h localhost bacula admin -c “COPY mediatype (MediaTypeId, MediaType, ReadOnly) FROM ‘/var/tmp/MediaType.txt'”
• psql -h localhost bacula admin -c “COPY path (PathId, Path) FROM ‘/var/tmp/Path.txt'”
• psql -h localhost bacula admin -c “COPY pool (PoolId, Name, NumVols, MaxVols, UseOnce, UseCatalog, AcceptAnyVolume, VolRetention, VolUseDuration, MaxVolJobs, MaxVolFiles, MaxVolBytes, AutoPrune, Recycle, PoolType, LabelFormat, Enabled, ScratchPoolId, RecyclePoolId, LabelType, NextPoolId, MigrationHighBytes, MigrationLowBytes, MigrationTime) FROM ‘/var/tmp/Pool.txt'”
• psql -h localhost bacula admin -c “COPY storage (StorageId, Name, AutoChanger) FROM ‘/var/tmp/Storage.txt'”
• psql -h localhost bacula admin -c “COPY unsavedfiles (UnsavedId, JobId, PathId, FilenameId) FROM ‘/var/tmp/UnsavedFiles.txt'”

Unfortunately my “Job.txt” and “Media.txt” contained datestamp entries like “0000-00-00 00:00:00” which are not valid for PostgreSQL. So I went into Vim and replaced it: s/0000-00-00 00:00:00/1970-01-01 00:00:00/g. Clear the table (DELETE FROM job) and import again.

And finally it’s important to get the sequence numbers right as described in the Bacula manual.

Morale: spend two extra-minutes to start with PostgreSQL right away instead of bothering about conversions later. And never assume converting from one database to another would work – just because both have “SQL” in their names.