Hi! Ive been using traccar for a few months while testing it out. i didn't bother to configure it, just started using it. Now I wanted to update it, and scale it, but also wanted to keep my data. so I tried to look for answers in the forums, this topic was a great starting point (thanks fts). but It was a bit out dated, slow, and more importantly in a different OS. So I ended up building my own tooling.

couple notes:

• this worked for me, but I did a lot of testing before actually doing the migration with the production data.
• some of the commands might require some previous configuration thats outside the scope of this guide.
• MAKE SURE YOU BACK EVERYTHING UP!!! things might go terribly horribly wrong, be safe!
• good luck!

H2 to postgres migration

Linux based migration from H2 to postgresql

Backup the DB

1. Stop traccar service

sudo systemctl stop traccar

1. Backup your database
   * copy the /opt/traccar/data/* files to a safe place.
2. backup the settings file traccar.xml to a safe place.
3. backup any other configuration files to a safe place.
4. run the following command to export your database to a file

sudo java -cp h2*.jar org.h2.tools.Script -url "jdbc:h2:/opt/traccar/data/database" -user sa -script backup.sql

1. Copy the backup file to a safe place.

"Convert" the backup file from H2 to Postgresql format.

1. create a new file called h2-sql.sh and copy the following content to it.

#!/bin/bash
args=("$@")

# separate principal inserts into their own files
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_USERS" VALUES.*?\);\s*?\n)/sg' $1 > 1_TC_USERS.txt
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_DRIVERS" VALUES.*?\);\s*?\n)/sg' $1 > 2_TC_DRIVERS.txt
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_GROUPS" VALUES.*?\);\s*?\n)/sg' $1 > 3_TC_GROUPS.txt
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_DEVICES" VALUES.*?\);\s*?\n)/sg' $1 > 4_TC_DEVICES.txt
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_GEOFENCES" VALUES.*?\);\s*?\n)/sg' $1 > 5_TC_GEOFENCES.txt
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."TC_NOTIFICATIONS" VALUES.*?\);\s*?\n)/sg' $1 > 6_TC_NOTIFICATIONS.txt

# other inserts
## extract all inserts
perl -0777 -ne 'print /(INSERT INTO "PUBLIC"."\w+" VALUES.*?\);\s*?\n)/sg' $1 > 7_OTHERS.txt
## remove inserts from tables that have their own file
perl -i -p0e 's/(INSERT INTO "PUBLIC"."(?:TC_DEVICES|TC_USERS|TC_DRIVERS|TC_GEOFENCES|TC_DEVICES|TC_NOTIFICATIONS|TC_GROUPS|DATABASECHANGELOGLOCK|TC_KEYSTORE)" VALUES.*?\);\s*?\n)//sg' 7_OTHERS.txt

# create a file to delete TC_SERVERS
echo 'DELETE FROM public.tc_servers;' > 00_DELETE.txt

# create a file to reset sequences
## extract all CREATE CACHED TABLE
perl -0777 -ne 'print /CREATE CACHED TABLE "PUBLIC"."\w+"\(\n\s*"ID" INTEGER GENERATED.*?\);\s*?\n/sg' $1 > 0_RESET.txt
## replace CREATE CACHED TABLE with SELECT SETVAL
perl -i -p0e 's/CREATE CACHED TABLE "PUBLIC"\."([A-Z_]*)"\(\n *"ID"(.|[\n])*?\);/SELECT SETVAL('\''public.$1_id_seq'\'',COALESCE(MAX(id),1)) FROM public.$1;/igs'  0_RESET.txt

# concatenate all files
cat *.txt > RESTORE.sql

# replace INSERT INTO "PUBLIC"." with INSERT INTO public.
perl -i -p0e 's/INSERT INTO "PUBLIC"."(.*)"/INSERT INTO public.$1/g' RESTORE.sql

# delete temporary files
rm 00_DELETE.txt 0_RESET.txt 1_TC_USERS.txt 2_TC_DRIVERS.txt 3_TC_GROUPS.txt 4_TC_DEVICES.txt 5_TC_GEOFENCES.txt 6_TC_NOTIFICATIONS.txt 7_OTHERS.txt

1. Copy the backup.sql file to the same directory as the h2-sql.sh script.
2. Run the h2-sql.sh script to convert the H2 format to Postgresql format. This file will:
   * read your backup file from H2
   * breakup the inserts
   * create a script to reset the sequences
   * create a script to delete the TC_SERVERS table
   * create the insert scripts for each table
   * concatenate all the files in the right order into a single file called RESTORE.sql

sudo bash h2-sql.sh backup.sql 

configure traccar to use postgresql

1. Uninstall and reinstall traccar. Instructions here
2. Edit the traccar.xml file and change the database settings to use postgresql. For example:

<entry key='database.driver'>org.postgresql.Driver</entry>
<entry key='database.url'>jdbc:postgresql://localhost:5432/postgres</entry>
<entry key='database.user'>postgres</entry>
<entry key='database.password'>THIS IS YOUR PASSWORD</entry>

1. Start traccar service this will create the tables in the database.

sudo systemctl start traccar

1. Restore the new database in postgresql with your back up data:

psql -h localhost -p 5432 -U postgres -d postgres -f RESTORE.sql

1. Stop traccar service

sudo systemctl stop traccar

1. Restore the traccar.xml file from the backup.
2. Start traccar service

sudo systemctl start traccar

Done ✨

Thanks for sharing! And thanks for a very nice markdown formatting.

No problem, Thank you for all the work you put into this Anton!
Just realized that some how I managed to mess up the title and can't edit it anymore XD (maybe you can change HO to How)

Fixed.

LEGEND!

Newbie here, but I was finally able to run the backup database command successfully. Here are the commands I used to generate the backup.sql file:

export JAVA_HOME="/opt/traccar/jre"
export PATH=$JAVA_HOME/bin:$PATH
cd /opt/traccar/lib
java -cp h2-2.2.224.jar org.h2.tools.Script -url "jdbc:h2:/opt/traccar/data/database" -user sa -script /tmp/backup.sql