Christoph’s Blog

Rants, discoveries and my tireless search for perfect Linux tools

Archive for September, 2008

MySQL to PostgreSQL - a Bacula conversion odyssey

with 2 comments

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.

Written by Christoph Haas

September 16th, 2008 at 11:11 pm

Posted in Debian, Open-Source, Rants

mentors.debian.net’s flakiness

without comments

A lot of users of mentors.debian.net have already mailed, irced and jabbered me. (Fortunately nobody rang at my door.) The service was pretty unreliable in the past weeks. If I’m not mistaken it was due to not enough RAM. Unfortunately it’s a VPS server (sponsored for that purpose by vpslink.com) that has 1 GB RAM but doesn’t allow to set up swap for the rare cases where a 100 MB more RAM would save the world. Took me a while to figure out where the infrequent “MySQL server has gone away” came from. That lead to packages either left in the incoming directory or being thrown away randomly during the import into the repository because not all database error conditions have been properly caught.

The better news is that the debexpo project done during this year’s Google Summer of Code is now done. The Pylons-based software was written to suit the need for easy-to-use web-based Debian package repositories like PPAs or debpool. I am currently working on a few subleties of debexpo before it will be used for mentors.debian.net. I intend to announce the beta version of mentors.debian.net in about a week. And I hope for lots of feedback then.

And let’s hope I get the RAM situation improved on the server. The mentors backend was the only software using the MySQL database. With debexpo (which uses SQLAlchemy for database interaction) I’m free to use PostgreSQL or even SQLite.

Update: I should have taken my logs more seriously. The “OperationalError: (2006, ‘MySQL server has gone away’)” was meant seriously. Now it’s also clear why it didn’t happen all of the time. MySQL’s default connection timeout is 8 hours. It’s now set to 3 days so things shouldn’t break unless maintainer’s stop uploading packages for longer than 3 days. Does anyone know if MySQL can be taught to not cancel idle connections? I read rumors about a reconnect=1 argument to Python’s MySQLdb module but I didn’t find it in the docs. This is really a stupid default behavior.

Written by Christoph Haas

September 8th, 2008 at 11:25 am