How to convert a postgres database to sqlite

Question

We're working on a website, and when we develop locally (one of us from Windows), we use sqlite3, but on the server (linux) we use postgres. We'd like to be able to import the production database into our development process, so I'm wondering if there is a way to convert from a postgres database dump to something sqlite3 can understand (just feeding it the postgres's dumped SQL gave many, many errors). Or would it be easier just to install postgres on windows? Thanks.

Answer 1

I found this blog entry which guides you to do these steps:

1. Create a dump of the PostgreSQL database.

   ssh -C [email protected] pg_dump --data-only --inserts YOUR_DB_NAME > dump.sql 

2. Remove/modify the dump.

   1. Remove the lines starting with SET
   2. Remove the lines starting with SELECT pg_catalog.setval
   3. Replace true for ‘t’
   4. Replace false for ‘f’

3. Add BEGIN; as first line and END; as last line

4. Recreate an empty development database. bundle exec rake db:migrate

5. Import the dump.

   sqlite3 db/development.sqlite3 sqlite> delete from schema_migrations; sqlite> .read dump.sql 

Of course connecting via ssh and creating a new db using rake are optional

Answer 2

STEP1: make a dump of your database structure and data

pg_dump --create --inserts -f myPgDump.sql \    -d myDatabaseName -U myUserName -W myPassword 

STEP2: delete everything except CREATE TABLES and INSERT statements out of myPgDump.sql (using text editor)

STEP3: initialize your SQLite database passing structure and data of your Postgres dump

sqlite3 myNewSQLiteDB.db -init myPgDump.sql 

STEP4: use your database ;)