Sqlite vs postgresql10/13/2023 ![]() Join information_schema.tables tab on tab.table_schema = col.table_schema $ sequel -C sqlite://data.db Connect to your database and export all the date/time/timestamp columns in your database to a csv file: I converted the timestamps after migrating the postgresql db: Considering sqlite db is the default starter option for strapi projects, I think it will bring great value to the community. The other option is to see if the Strapi team can store datetimes in seconds instead of milliseconds as default. Set work_mem to '16MB', maintenance_work_mem to '512 MB' Into include drop, create tables, create indexes, reset sequences Just run pgloader This will succesfully migrate the DB, but the datetimes will be way off due to the incorrect units: load database Here is my pgloader script so far, if someone wants to take it further. It is a massive pain as I’ve never used Lisp before! The created_at and update_at fields are important to me so I’m trying to do a custom transformation written in Lisp (pgloader is written in Lisp - see their docs) just to do the simple msec to sec unit conversion. If you can convert all the datetimes in the sqlitedb to seconds epoch time then pgloader tool should work (and possibly any other migration tool). ![]() Thus, the whole pgloader migration fails. The pgloader tool assumes you are inputting SECONDS epoch unix time. The problem is that datetimes are stored in milliseconds since epoch in sqlite. I am also trying to use the pgloader tool to do a direct migration from sqlite to postgresql. ![]()
0 Comments
Leave a Reply.AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |