Is there a way to merge 2 teslamate databases?

[muffstic]

I originally setup Teslamate on Windows 10 and have since bought a Raspberrypi. I tried to restore from a backup before I went on vacation but it didn't work. While I was gone, the power went out and the windows 10 database stopped working June 30th at 3:26 and the Raspberry pi started recording on June 30th at 6:10. If I only use the windows 10 database I will lose data for a week, if I use the Raspberrypi database I will lose the data from the first 3 months I owned the car. Any help is appreciated if it's possible.

[darkxfuneral]

I originally setup Teslamate on a Synology NAS and a RPi (as Backup). Because of a firewall misconfiguration on my NAS, it didn't recieved any data for one day but the RPi has it.
That was exactly my question if it is possible to merge two database. my NAS has the most of data's and RPi serve as a backup and it was setup 3 months later.
So please if there is a way to merge 2 teslamate databases, let us know.

[SteveDonie]

I can't give you exact steps, but this would probably be done by merging the two databases. If there was a way to export the data from one database and then import into the other database, that seems like it could work. Unfortunately, it appears that export is an unimplemented feature: #451

[bre0zd]

I'm in a similar situation and where I would want to merge my two old TeslaMate databases (which I took a backup ofc) in to one single database. I made the mistake that I created a new TeslaMate instance for every new Tesla we got but now lacking the ability to get a comprehensive view on historic data of the other models. I've got access via psql / pgAdmin to the DB instance and tried to do a restore but obviously running in to issues as the keys are already existing. And DB expert out there? :)

[manuelkamp]

If you are not familiar in writing your own sql lines, the short answer is no. If you are, it is possible, but i doubt anyone would write scripts for your specific migration.

[ajeckmans]

I have a similar issue in that I've failed to migrate data properly and now have about 500MB in one instance (a little over a year of data) and 350MB in another.
Unfortunately its not as easy as dumping the data using pg_dump and re-importing into the new instance. Both instances will have started numbering of identifiers for stuff like locations from zero and so you have some duplicate data with different ids, different data with the same etc. Any import operation will have to do duplicate detection, rewriting ids and possible data migration.
I've been sitting on my database for a few months now waiting for the moment I have time to give it a go. @muffstic please hold on to your old database as I will get around to writing that "script" @manuelkamp mentioned.

[tylermerrick]

I am in pretty much the same scenario, I have a backup file that I would like imported to a new instance that already has data.

[ajeckmans]

I totally forgot about this! I still have an old database I want to emrge as well. I'm going to put this as a post it on my monitor :-)

[spacex-3]

Hey bro, do you finish the script? thanks a lot!

[tony06077]

出一个teslamte 备份还原教程原理就两步
1创建备份文件并导出
2放入备份文件并还原如何获取备份文件和把备份文件放进去就成了关键，其他只要输入死命令就行。这边给几个命令 可能用得到
CD 进入指定路径 并在指定路径下运行
CP 复制
需要xftp xshell 两软件
第一步获取备份文件 一般来说备份文件如果在安装时自己没有指定在哪个位置，都在HOME目录下的主机名里
例如/home/ubuntu 当确认了你安装在哪个目录下 逐行 敲入命令
sudo -icd /home/ubuntu　这个时候就会看到******************:/home/ubuntu#表示已经成功进入了

docker-compose exec -T database pg_dump -U teslamate teslamate > /backuplocation/teslamate.bck
此时在 /home/ubuntu路径下已经有一个 teslamate.bck 的文件
打开xftp 打开到/home/ubuntu目录下把文件下载下来 如果遇到下载不下来的就用cp 命令复制到其他文件再下载下来我这是复制到tmp文件再通过xftp下载下来 （如果能直接打开并下载下来的这步不需要）

cp teslamate.bck /tmp 这个命令代表着我把teslamate.bck文件复制到tmp文件下打开xftp 到tmp路径 把teslamate.bck下载出来到此第一步已经完成。

第二步打开要还原的主机 把这个文件放到你安装teslamate的位置
sudo -i
打开xftp 把teslamate.bck放入/home/ubuntu路径下 如果失败就和上面一样先放入临时文件再复制进去把teslamate.bck 放到tmp文件下cd /tmp *****************:/home/ubuntu#表示已经成功进入了
cp teslamate.bck/home/ubuntu　 （默认你的也在这个位置，不是这个位置自己改）这个的意思就是把文件从tmp复制到/home/ubuntu下到此备份文件已经放入要还原的地方了
接下来的就敲命令就可以了
docker-compose stop teslamate （这条单独敲，显示stop就代表成功）
接下来复制下面整段敲入

Drop existing data and reinitializedocker-compose exec -T database psql -U teslamate << .

drop schema public cascade;
create schema public;create extension cube;
create extension earthdistance;
CREATE OR REPLACE FUNCTION public.ll_to_earth(float8, float8)
RETURNS public.earth
LANGUAGE SQL
IMMUTABLE STRICT
PARALLEL SAFE
AS 'SELECT public.cube(public.cube(public.cube(public.earth()*cos(radians($1))*cos(radians($2))),public.earth()*cos(radians($1))*sin(radians($2))),public.earth()*sin(radians($1)))::public.earth';
.

Restoredocker-compose exec -T database psql -U teslamate -d teslamate < teslamate.bck

Restart the teslamate container

docker-compose start teslamate
(由于论坛排班的布局建议上github把这个命令从那边复制再敲入）
看到conpose显示绿色 代表成功 接下来就打开你的地址
数据已经全部恢复了

[ajeckmans]

It's not about just reimporting backed up data. There's two copies of the database with overlapping id's for data that need to be merged. Duplicates left out and relations restored properly.

Or even worse similar data (for instance same location) with different ids, that need to be de duplicated.

[tony06077]

You are right. I have no idea to combine them.

[bfoz]

Has anyone made any progress on this? Is there any documentation on the DB schema?

[ajeckmans]

I spent a few hours on it and have some preliminary work done to "fix" this issue, but the project has been on hold for a few months now.

As far as I know there is no DB schema other than the database itself and what is in https://github.com/adriankumpf/teslamate/tree/master/priv/repo/migrations

[khorton]

I am very interested in a way to merge two databases. I have two TeslaMate servers running, but each server has been offline at different periods, so neither server has a complete set of data. But, I'd have the whole picture if I could merge the two databases, somehow.

[justwjx]

same requirements

[kylefu8]

Same requirement. Hope someone can work it out. Thanks in advance.