Labkey lists remote backup

EDIT – This is a solution for inexperienced PostgreSQL users. A much better solution is by using pg_dump to get a full backup of the “labkey” database. However, the pg_dump solution requires on to do port forwarding in VirtualBox on the PostgreSQL port (5433 or 5432) and to modify a few configuration files to allow remote access to the database. The benefit is however important!

Recently, I began using LabKey [] to manage shared lists of reagents and strains, previously found in Excel/LibreOffice spreadsheets. The motivation for this switch was mostly the fact that it is relatively easy to do damage a big table in Excel in ways that are not always easy to track or to find. LabKey may not have the best interface out of the box but gets the work done. I found that the easiest way to install it was on a GNU/Linux Ubuntu virtual server, currently on a Windows machine (some notes on VirtualBox can be found in one of my previous posts).

Now, it is clearly easier to manage a spreadsheet in a spreadsheet software; however sharing spreadsheets between several users is not obvious. LabKey lists solve this problem to a large extent, they are easy to build from .xls files and relatively easy to use. The interface lacks, for the moment, any kind of batch editing, but I hope this is a feature that could be implemented in future releases. Batch import is easy with xls or tsv/csv files.

The reason for this post is that I could not find easily ways to backup the data found on the LabKey web server. Doing SQL dumps and the likes of it is in itself a whole affair and I know from one of my previous adventures with WordPress and MySql, that SQL backups require work to restore. As a workaround, I found that LabKey answers very well to http requests, via wget, for example (in green the variables that one has to change for each case):

/opt/local/bin/wget --server-response --http-user=a_user_mail 

Here, the complete path for wget on my system is shown. It is useful to have it this way if the command is run by cron, for example.

This query can now be included in a short sh script run by cron to do weekly backups of that given list. Here is the template for the bash script doing it:

# Storage folder where to move backup files
suffix="labkey" #or whatever you like
dest_folder_name=`date +"%Y%m%d"`$suffix
mkdir $dest_folder
cd $dest_folder
zip -rj $zipname $dest_folder
#j option removes paths from the zip archive
#remove temporary files
rm -r $dest_folder #remove the temporary folder with the downloaded files

That’s it. This solution is far from being perfect and only backups the lists that one specifies but it is easy to use and the obtained json files are very easy to parse and transform to whatever you like.


LibreOffice Calc copy/paste dates may be 4 years wrong

I recently came upon a very strange bug, or should I call it “behaviour” when doing copy-paste between LibreOffice spreadsheets. What was extremely annoying was that a date like 2013-04-09 would transform to 2009-03-09. I suspected something was wrong but had no idea what until I saw a long conversation about a similar behaviour in a bug report [].

Take a look at one of the settings in LibreOffice Calc:


There it is: the source of the misbehaving copy/paste is the starting date used to calculate the actual date. Dates are numbers that start at a given base. If a spreadsheet was built on an older Excel on a Mac, it will retain the start of dates as 1904. Apparently, this date start was not automatically changed when importing these files in LibreOffice. If the spreadsheets starts counting in january 1904, any date copied and pasted in another spreadsheet that starts counting in december 1899 will be shifted back in time by about 4 years (in fact 1462 days).

Mistery solved. Be careful – if you change the above settings in LibreOffice Calc, the dates of an open spreadsheet that had an incorrect base to start with will change immediately. To recover the old dates, just add 1462 (or a different number if your spreadsheet happened to use, for example, 01/01/1900 to start counting).