How I migrated H2 database to PostgreSQL

Here my write down of how I managed to migrate the Traccar data from the H2 database to a PostgreSQL database.

create a Postgresql data for traccar and adjust traccar configuration accordingly:
https://www.traccar.org/postgresql/

Start traccar and it will create new empty tables in the Postgresql database. Then stop traccar again, so that it does not interfere with the copy process.

To actually copy the data use the 3rd party tool SQuirreL SQL
http://www.squirrelsql.org/

To be able to connect to the H2 and Postgresql databases with Squirrel SQL you need the drivers. They are not included in the Squirrel SQL installer. You can download them here:
H2 JDBC driver: http://www.h2database.com/automated/h2-latest.jar
PostgreSQL JDBC Driver: https://jdbc.postgresql.org/download.html

This post describes how to do the copy process:
https://stackoverflow.com/questions/11359813/how-to-convert-h2database-database-file-to-mysql-database-sql-file/25405283
it is about migration to Mysql, but copying to PostgreSQL is similar.

As source I used the "H2 Embedded" Driver.
Copy your H2 database files from Traccar server /opt/traccar/data/ to a folder where your Squirrel SQL client runs, for example "C:\Traccar".
I used this URL: jdbc:h2:C:\Traccar\database
Do not use the full filename "C:\Traccar\database.mv.db"! You must omit the file extension ".mv.db"!

For the "PostgreSQL" driver in Squirrel SQL the connection must look like this
URL: jdbc:postgresql://[your_server]:5432/[your_traccar_database]
Of course the Postgresql database on the traccar server must be accessible from the Squirrel SQL client.

Be aware that the copy process is very slow! For me copying a 100MB H2 database took 6 hours.

After copying the data the process is not quite done.
You would be able to query the old data in Traccar but adding new tracker data will fail. You would see error messages like this in the log:

Failed to store position - ERROR: duplicate key value violates unique constraint "tc_positions_pkey"
  Detail: Key (id)=(1) already exists. - PSQLException (... < QueryBuilder:480 < DataManager:458 < DefaultDataHandler:40 < ...)

The copy did not set the sequence counters for the primary keys in the tables. So traccar begins with Key (id)=(1) again, but since Key (id)=(1) already exists, it fails.
We need to fix the sequence counters as described in this article
https://wiki.postgresql.org/wiki/Fixing_Sequences
chapter "Updating sequence values from table"

For traccar version 4.10, which I am using, in 14 tables the sequence counters need to be fixed. These are the SQL queries, created by the Postgres code, which will fix the counters:

 SELECT SETVAL('public.tc_attributes_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_attributes;
 SELECT SETVAL('public.tc_calendars_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_calendars;
 SELECT SETVAL('public.tc_commands_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_commands;
 SELECT SETVAL('public.tc_devices_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_devices;
 SELECT SETVAL('public.tc_drivers_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_drivers;
 SELECT SETVAL('public.tc_events_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_events;
 SELECT SETVAL('public.tc_geofences_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_geofences;
 SELECT SETVAL('public.tc_groups_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_groups;
 SELECT SETVAL('public.tc_maintenances_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_maintenances;
 SELECT SETVAL('public.tc_notifications_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_notifications;
 SELECT SETVAL('public.tc_positions_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_positions;
 SELECT SETVAL('public.tc_servers_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_servers;
 SELECT SETVAL('public.tc_statistics_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_statistics;
 SELECT SETVAL('public.tc_users_id_seq', COALESCE(MAX(id), 1) ) FROM public.tc_users;

After this the data migration is complete and you can start your traccar service again.

I hope this helps others to migrate their data.

Thanks for sharing this information.

By the way, I would recommend using MySQL with Traccar instead of Postgres. We had 5x better performance with it.

Hi! I try repeat it, but I can't do it on Potgress and MySQL too. For example:

1. We have different structure id databases:
   
2. When I try copy some tables, I have error:
   

Screens for postgress, but in MySQL the structure is even more different and I have same errors.
Can you say what tables and in what section I must copy?
Or may be I have another way for migrate to another database?
Thanks in advance!

Thanks, @fts, One thing I found to add is that the tables like to be copied in order. For me copying "tc_devices" then "tc_groups" and then the rest.