How to convert postgres database to sqlite

Question

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

Answer 1

There are several converter tools:

http://sqlite.com/cvstrac/wiki?p=ConverterTools

Would it be easier to just install postgres on windows?

Perhaps this is very simple.

Answer 2

I found this blog post to help you take the following steps:

• Dump the PostgreSQL database.

  ssh -C [e-mail] pg_dump --data-only --inserts YOUR_DB_NAME > dump.sql 

• Delete / modify the dump.

  • Delete lines starting with SET
  • Remove lines starting with SELECT pg_catalog.setval
  • Replace true for ' t
  • Replace false for ' f

• Add BEGIN; as the first line and END; as the last line

• Create a new development database. bundle exec rake db:migrate

• Import 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 3

STEP1: Dump Database Structure and Data

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

STEP2: delete everything except the CREATE TABLES and INSERT commands from myPgDump.sql (using a text editor)

STEP3: Initialize the SQLite Database Data Transfer Structure and Your Postgres Dump Data

 sqlite3 myNewSQLiteDB.db -init -myPgDump.sql 

STEP4: use your database;)

Answer 4

If you need a more automated solution, start at a glance:

 #!/bin/bash $table_name=TABLENAMEHERE PGPASSWORD="PASSWORD" /usr/bin/pg_dump --file "results_dump.sql" --host "yourhost.com" --username "username" --no-password --verbose --format=p --create --clean --disable-dollar-quoting --inserts --column-inserts --table "public.${table_name}" "memseq" # Some clean ups perl -0777 -i.original -pe "s/.+?(INSERT)/\1/is" results_dump.sql perl -0777 -i.original -pe "s/--.+//is" results_dump.sql # Remove public. preffix from table name sed -i "s/public.${table_name}/${table_name}/g" results_dump.sql # fix binary blobs sed -i "s/'\\\\x/x'/g" results_dump.sql # use transactions to make it faster echo 'BEGIN;' | cat - results_dump.sql > temp && mv temp results_dump.sql echo 'END;' >> results_dump.sql # clean the current table sqlite3 results.sqlite "DELETE FROM ${table_name};" # finally apply changes sqlite3 results.sqlite3 < results_dump.sql && \ rm results_dump.sql && \ rm results_dump.sql.original 

Answer 5

It was VERY easy to do using gemstone taps as described here: http://railscasts.com/episodes/342-migrating-to-postgresql

And I started using Postgres.app on my Mac (without having to install, drop the application in the application directory, although you may need to add one line to the PATH environment variable as described in the documentation) using Induction.app as a GUI tool for view / query the database.