Hi there,
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:
Grepping the code for use of sqlUtils, I’d guess that the differences in sqlUtils.now and sqlUtils.datetime may be the main schema differences. I haven’t used pgloader, and don’t know if it gives you a chance to tweak the schema. If not, you could make a copy of the sqlite file, and modify its schema in place such that it can load ok.
Another option would be to let Grist initialize the schema of the postgres database, and then copy/replace just the data into the tables.
1 Like
Thanks @paul-grist for your reply!
Sure, it would be my pleasure!
Seems indeed safer this way. Pgloader seem to support a data only option. This should reduce considerably the risk of errors.
I’ll keep you informed of my progress.
You may need to delete any data the tables get seeded with, e.g. default user accounts, to avoid duplicates.
I think I successfully migrated data from Sqlite to Postgresql, at least locally (have to check whether it goes well for our production instance) 
.
If that would interest you, I can think of making this available as a script tool in Grist.
Important: If anyone read this message, please run this at your own risk, this hasn’t been thoroughly tested (it has just been experimented so far). Be sure to make backups beforehand and to know how to restore your instance if anything went wrong.
As a prerequesite, a postgres database should already exist with the targetted structure. This can be done by either :
- by mouting linking the Grist instance to postgres instead of sqlite and run pgloader as described below.
- otherwise one can also mount a local grist instance linked to Postgresql, run the migration from a copy of the sqlite home database (aka
landing.db), and if successful dump the database and load it in a production instance.
To run the migration, first create a file named db.load with this content (adapt what is in capital case):
load database
from sqlite:///PATH/TO/LANDING.DB
into postgresql://DB_USER:DB_PASSWORD@DB_HOST/DB_NAME
with truncate, data only;
And then simply run this:
pgloader db.load
You may see warnings, but in my case, no errors are reported and the data should be now migrated.
1 Like