Migrating the Database

For some reason I thought it would be a good idea to migrate the database from MySQL to PostgreSQL. Installing PostgreSQL on Debian (in my case Debian 8 Jessie) is just one command line. I also made a simple backup script which is run by a chron job, I have a similar script for MySQL:

#!/bin/sh

# sudo crontab -e
# Backup all PostreSQL databases every day 3 am
#* 3 * * * /mnt/sda1/bak/pgsqlbak.sh

TIMESTAMP=$(date +"%F")
BACKUP_DIR="/mnt/sda1/bak/pgsql/$TIMESTAMP"
USERNAME="my_name"
export PGPASSWORD="*********"
find /mnt/sda1/bak/pgsql/ -maxdepth 1 -type d -mtime +7 -exec rm -Rf {} \;

mkdir -p $BACKUP_DIR

databases=`psql -h 127.0.0.1 -U $USERNAME -q -t -c "SELECT datname FROM pg_database" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'}`

for i in $databases; do
  /usr/bin/vacuumdb -z -h 127.0.0.1 -U $USERNAME $i >/dev/null 2>&1
  /usr/bin/pg_dump -U $USERNAME -i -F c -b $i -h 127.0.0.1 -f $BACKUP_DIR/$i.backup
  /usr/bin/pg_dump -U $USERNAME -i -F p -b $i -h 127.0.0.1 -f $BACKUP_DIR/$i.sql
done

Migrating the database structure required some manual work, but fortunately the database is still very small. So I just used mysqldump to get a SQL file, changed some types and syntax and imported it into PostgreSQL. Because I use a caching data server, which is the only program that accesses the database, and this data server is database agnostic (so i can just switch back to MySQL with changing the configuration file), I can not use any advanced database features anyway. This made the migration of the database much easier. I can't even use auto incremented values for primary keys or other database generated values, instead it uses UUIDs as primary keys.

The PostgreSQL backend was never tested before, so it surprised me a bit that the data server worked without any changes with PostgreSQL.

Porting the Data server

I am developing this with MSVC 2015 on Window 7 64 Bit, x86_64 architecture (I also tried VC2017 but the linker crashes all the time). The target is Debian 8 on ARMv7 (32 Bit) architecture. So, not just that the target is a different tools chain, but also the OS and architecture is different.

To generate the make files (and VS solution, project files) I use premake5, because I'm not smart enough for CMake.

Compiling

I didn't compile anything on Linux in ages, so I thought this could be fun.

Getting the source to compile with GCC and Clang was not hard, every library I use is also available for Linux, or also compiles on Linux (Lua, SQlite, Asio). What I needed to do was:

• Just ignore many unknown pragma warnings.
• Get rid of MS' secure CRT functions (e.g. sprintf_s() -> sprintf()).
• Get rid of the nifty #pragma comment(lib, ...) and add the lib files to the project and make files instead ?.
• Throw out the ODBC database driver. I thought about also targeting MSSQL, so it seemed to be a good idea to have support for ODBC, but it's not used.
• Turn off Linktime Optimization (full program optimization). Took me a while until i realized my compiler does not support it.

Linking

So making the program just to compile was very easy, but making it also link was a pain, especially the PostgreSQL client library has many dependencies (e.g. libldap2-dev, libssl-dev, libgsasl7-dev). So I ended up having something like this in my premake5.lua:

    if (_TARGET_OS == "windows") then
      links { "abscommon", "abcrypto", "sqlite3", "libpq", "libmysql" }
    elseif (_TARGET_OS == "linux") then
      links { "pthrerad", "abscommon", "abcrypto", "sqlite3", "dl", "pq", "ssl", "crypto", "mysqlclient", "z", "gssapi_krb5" }
    end

Finally it complied and linked, but there are still some problems as you can see on the screenshot.

It runs now on the same machine as the database server. The data server is very lightweight (without load ??

Update

The linking issues have been solved with upgrading VS2017 to version 15.8.6. Now everything (18 projects) compile, link and run fine with VS2017.

Also the obvious bugs on Linux have been solved, but the Linux version of the data server is still not reliable.