Until then, we just used the regular Sqlite Database (
landing.db
) in order to store data of users, license, …
Now that we would like to scale, it appears that we have to migrate data to Postgresql (because even if we just for now want to increase the number of docs workers, they need to connect to Postgresql as well, right?).
So, I would like to know if you knew a way to migrate data from Sqlite to Postgresql (maybe Gristlabs had to run this operation in the past?).
I attempted to use pgloader, but either using the debian package or the most recent version through docker, it fails with this message:
2023-09-06T08:24:33.160001Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "datetime('now')"
QUERY: CREATE TABLE aliases
url_id text,
org_id bigint,
doc_id text,
created_at timestamptz default 'datetime(''now'')'
2023-09-06T08:24:33.160001Z FATAL Failed to create the schema, see above.
2023-09-06T08:24:33.164001Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 98 0.032s
Create Schemas 0 0 0.000s
Create SQL Types 0 0 0.004s
Create tables 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
I can try to hack around pgloader, but before going further (and maybe reinvent the wheel), I would like to know if you have any method to share?
Thanks in advance!
Hi @Florent_F, sorry no, we haven’t done this kind of transition at Grist Labs. It’d be great if you took notes on the steps you’ve needed to go through, e.g. for external storage.
The error you are seeing looks like a problem with default values. There’s a bit of a mismatch between SQLite and PostgreSQL in a few places. The one you are seeing there reminds me of this code:
