Convert PostgreSQL to SQLite

Convert PostgreSQL to SQLite

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'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.