Created
October 5, 2015 07:04
-
-
Save fiftin/18221054c7777e1f1207 to your computer and use it in GitHub Desktop.
Convert PostgreSQL to SQLite
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
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.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This was super helpful. Thank you!
I'm migrating a Rails database, and thought it worth adding that the
INSERTS
for both thear_internal_metadata
table andschema_migrations
table should be removed as these are created when runningrails db:migrate
.They will raise
UNIQUE constraint failed
errors otherwise.