I have two instances both managed by gitops but the data is only written to one instance which is production. When we do gitops changes we test on a separate dev cluster which has an instance of questdb. The dev instance gets no data so I'm writing something to export data from the prod instance and import it to the dev instance.
My first attempt was to extract data to a CSV, so I could import later. But I am hitting a block here
curl -Gk "https://***.com/exec" --data-urlencode "query=COPY (SELECT * FROM build_status LIMIT 1) TO '/var/lib/questdb/copy/test.csv' WITH (header=true);"
{"query":"COPY (SELECT * FROM build_status LIMIT 1) TO '/var/lib/questdb/copy/test.csv' WITH (header=true);","error":"query is not allowed here","position":6}
apiVersion: v1
data:
server.conf: |
metrics.enabled = true
cairo.sql.backup.root = /var/lib/questdb/backup
cairo.sql.copy.root = /var/lib/questdb/copy
metrics.enabled = true
rest.copy.allow = true
shared.worker.count = 5
kind: ConfigMap
Version is 8.2.2
The error above is because the COPY command is to import data from a local file into QuestDB. To export data we would use the export endpoint.
However, it is probably a better idea to just read data from the source instance and insert into the destination. QuestDB ships with a utils folder, and one of the tools does exactly that: It reads from one instance (using the pg protocol) and writes into another (using ILP).
You would need to compile the jar, and then this would be an example of how to use it
java -cp utils.jar io.questdb.cliutil.Table2Ilp -d trades -dilp "https::addr=localhost:9000;username=admin;password=quest;" -s "trades WHERE start_time in '2022-06'" \
-sc "jdbc:postgresql://localhost:8812/qdb?user=account&password=secret&ssl=false" \
-sym "ticker,exchange" -sts start_time