Migrating Database - ManicTime Documentation

This document will help you to migrate the data to a different database server, for example from SQLite to PostgreSQL or other combinations.

Before migration

Install the database server you will be using, for example PostgreSQL or MS SQL.

1. Stop the server:

Windows
Go to where ManicTime Server is installed (on Windows by default in Program files (x86)/ManicTime Server), open command prompt and type:

ManicTimeServer stop

Linux
stop ManicTimeServer process

2. Create empty databases

Create empty ManicTimeCore and ManicTimeReports databases on the chosen database server

3. Download migration tool

Download migration tool
Download for Windows Download for Linux (x64) Download for Linux (ARM)

Extract files to some folder (i.e. Downloads folder).

4. Run migration tool

Run the tool with something similar to this:

./mtdb migrate -dbpr sqlite -cs "Data Source=C:\ProgramData\ManicTime\Server\Data\ManicTimeCore.db" -tdbpr postgresql -tcs "Host=localhost; Port=5432; Database=ManicTimeCore; SSL Mode=Prefer; User Id=postgres; Password=password;"

./mtdb migrate -dbpr sqlite -cs "Data Source=C:\ProgramData\ManicTime\Server\Data\ManicTimeCore.db" -tdbpr mssql -tcs "Server=localhost; Database=ManicTimeCore; User Id=manic; Password=password1#;"

./mtdb migrate -dbpr mssql -cs "Server=localhost; Database=ManicTimeCore; User Id=manic; Password=password1#;" -tdbpr postgresql -tcs "Host=localhost; Port=5432; Database=ManicTimeCore; SSL Mode=Prefer; User Id=postgres; Password=password;"

This should almost be correct, you just need to fix the connection string so that it points to your database and that it uses a user who has access to the database.

The tool will only migrate ManicTimeCore database. ManicTimeReports doesn't need to be migrated. All the data necessary to recreate ManicTimeReports is in ManicTimeCore database, so once ManicTime Server starts, it will fill ManicTimeReports database.

5. Fix connection string in ManicTimeServerSettings.json

Once the tool completes the conversion you need to fix the connection string, so that the server will know where to find the databases. File is usually in:
Windows

c:\ProgramData\ManicTime\Server\ManicTimeServerSettings.json

Linux

[ManicTimeServer folder]/Data/ManicTimeServerSettings.json

ManicTimeServerSettings.json file should have something like this in it:

PostgreSQL
MS SQL

{
    "database": {
        "provider": "PostgreSql",
        "connectionString": "Host=localhost; Port=5432; Database=ManicTimeCore; SSL Mode=Prefer; User Id=postgres; Password=password;"
    },
    "reporting": {
        "database": {
            "provider": "PostgreSql",
            "connectionString": "Host=localhost; Port=5432; Database=ManicTimeReports; SSL Mode=Prefer; User Id=postgres; Password=password;"
        }
    }
}

{
    "database": {
        "provider": "MsSql",
        "connectionString": "Server=manictime\\MANICTIME;Database=ManicTimeCore;User Id=manictime;Password=XXX;"
    },
    "reporting": {
        "database": {
            "provider": "MsSql",
            "connectionString": "Server=manictime\\MANICTIME;Database=ManicTimeReports;User Id=manictime;Password=XXX;"
        }
    } 
}

6. Start the server

Windows
Go to where ManicTime Server is installed (on Windows by default in Program files (x86)/ManicTime Server), open command prompt and type:

ManicTimeServer start

Linux
run ManicTimeServer in folder where you installed ManicTime