-
-
Save fiftin/18221054c7777e1f1207 to your computer and use it in GitHub Desktop.
1. Dump the data only sql to file | |
$ pg_dump --data-only --inserts YOUR_DB_NAME > dump.sql | |
2. scp to local | |
3. Remove the SET statements at the top | |
such as: | |
SET statement_timeout = 0; | |
SET client_encoding = 'SQL_ASCII'; | |
4. Remove the setval sequence queries | |
such as: | |
SELECT pg_catalog.setval('MY_OBJECT_id_seq', 10, true); | |
5. Replace true => ‘t’ and false => ‘f’ | |
-- These: | |
INSERT INTO table_name VALUES (1, true, false); | |
-- Should be replace to: | |
INSERT INTO table_name VALUES (1, 't', 'f'); | |
6. Add BEGIN; and END; to wrap the whole thing as a trasaction | |
7. Import | |
$ rm db/development.sqlite3 | |
$ rake db:migrate | |
$ sqlite3 db/development.sqlite3 | |
> delete from schema_migrations; | |
> .read dump.sql | |
> .exit |
I turned this into a simple, but effective bash script:
https://github.com/hirefrank/pg-to-sqlite
I'm on Postgresql 16 and the default dump file has non compatible format of INSERT INTO with the one sqlite uses. I found out if you dump DB with --attribute-inserts
it will work
pg_dump --attribute-inserts --data-only -d my_psql_db > /tmp.dump.sql
reference pg_dump docs
This was super helpful. Thank you!
I'm migrating a Rails database, and thought it worth adding that the INSERTS
for both the ar_internal_metadata
table and schema_migrations
table should be removed as these are created when running rails db:migrate
.
They will raise UNIQUE constraint failed
errors otherwise.
Thanks @equivalent and @aaronmoodie I added your suggestions to my script -- you can see them here:
hirefrank/pg-to-sqlite#1
Also @aaronmoodie 👋 it's been a long time since Etsy!
Oh! hey @hirefrank! 👋 I didn't realise that was you! Thanks for updating the script and for adding my suggestions.
Just to wrap this up I had to change some postgresql JSONB fields to JSON, and some Integer/String Array fields to JSON fields.
I also had to use different date calculation queries using sqlite's JULIANDAY and the TRIM functions were different in my search/filter queries and indexes. But that was pretty much it!