influx: merging large database with select into fails. Alternatives?-CodePudding

influxdb 1.8.10

I have 2 databases which was originally one, but due to hardware limitations,I had to move to a new system and just started a new database there.

now i've upgraded to a new system and wants to merge these two again. I've restored the backups of both into a seperate docker instance in two db. energypre2021 and energycombined(which has the data beyond 2021)

if I use influx -database=energycombined -execute 'SELECT * INTO energypre2021..:MEASUREMENT FROM /.*/ GROUP BY *' in the docker container, i just get kicked out of the docker instance without any messages and no merged db.

the log just says this

ts=2022-09-08T22:30:10.118858Z lvl=info msg="Open store (end)" log_id=0cooRaaG000 service=store trace_id=0cooRa~0000 op_name=tsdb_open op_event=end op_elapsed=4042.491ms

any tips on how to effectively merge both db's? I am willing to merge one table at a time if needed.

influxdb 1.8.10 64GBRam 1TB SSD should be enough power for this stuff me thinks.

CodePudding user response：

You could probably export the database from the two instances separately and then import them one by one.

Step 1: export the database from the two instances

influx_inspect export -compress -datadir /var/lib/influxdb/data  -waldir /var/lib/influxdb/wal -out /root/1.gz

influx_inspect export -compress -datadir /var/lib/influxdb/data  -waldir /var/lib/influxdb/wal -out /root/2.gz

Step 2: and then you can import these two files one by one:

influx -import -compressed=true -path=/root/1.gz
influx -import -compressed=true -path=/root/2.gz

See more details here on the export and there on the import.

CodePudding user response：

I actually did an export -portable of both instances
influxd backup -portable -database energy /mnt/backup/pre2020

influxd backup -portable -database energy /mnt/backup/newestdata

then I restore the instance with the least data (newestdata) in one empty influx instance. . influxd restore -portable -database energy /mnt/backup/newestdata and export a copy of this influx_inspect export -datadir /var/lib/influxdb/data/ -waldir /var/lib/influxdb/wal/ -out /mnt/backup/newestdata.gz

then i drop that instance and restore the export with the most data influxd restore -portable -database energy /mnt/backup/pre2020

and then import the export influx -import -compressed=true -path=/mnt/backup/newestdata.gz then I import both instances-backups one by one in one empty influx instance.