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.

Thorium

I'd just switch your development environment to PostgreSQL, developing on top of one database (especially one as loose and forgiving as SQLite) but deploying on another (especially one as strict as PostgreSQL) is generally a recipe for aggravation and swearing. – Irrespective

To echo mu's response, DON'T DO THIS..DON'T DO THIS..DON'T DO THIS. Develop and deploy on the same thing. It's bad engineering practice to do otherwise. – Threnode

Developing on SQLite and running on postgres is perfectly acceptable IF you are using an ORM which deals with all the differences in dialect and schema for you. – Tallou

@Zaz You meant serverfault.com/questions/274355/… – Asphodel

The same question on ServerFault. – Orgiastic

phauer.com/2017/dont-use-in-memory-databases-tests-h2 – Percheron

Answer 1

Even though there are many very good helpful answers here, I just want to mark this as answered. We ended up going with the advice of the comments:

I'd just switch your development environment to PostgreSQL, developing on top of one database (especially one as loose and forgiving as SQLite) but deploying on another (especially one as strict as PostgreSQL) is generally a recipe for aggravation and swearing. – @mu is too short

To echo mu's response, DON'T DO THIS..DON'T DO THIS..DON'T DO THIS. Develop and deploy on the same thing. It's bad engineering practice to do otherwise. – @Kuberchaun

So we just installed postgres on our dev machines. It was easy to get going and worked very smoothly.

Thorium

what if i need to clone my server's postgres to sqlite on my phone? i need to bind them somehow – Brickwork

Answer 2

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

Lew

For me, t and f did not work, I had to use 1 and 0 as described in these posts: #5768864 – Homunculus

blobs saved in different format. sqlite uses x'010101', while postgres '\x0101' – Chasseur

I created a gist that was based from this and other sources: gist.github.com/jeffreycastro/918a24588ac1628e2af6b0def5bb7416 – Lind

answer is not complete. check here - medium.com/@andreypu/… more detailed explanation. helped me out – Brickwork

Answer 3

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 ;)

Connote

This does not work for me I have a lot of syntax errors... – Christopher

Answer 4

Taken from https://mcmap.net/q/122027/-how-to-convert-sqlite-sql-dump-file-to-postgresql (upvote there): The sequel gem makes this a very relaxing procedure:

First install Ruby, then install the gem by running gem install sequel.

In case of sqlite, it would be like this: sequel -C postgres://user@localhost/db sqlite://db/production.sqlite3

Credits to @lulalala .

Hedvige

installing Ruby has many different ways and two of them failed for me. What do you suggest on Ubuntu? Neither apt nor snap allow me to install any gems. I got those from: ruby-lang.org/en/documentation/installation/#apt – Acquiescence

@Acquiescence if you use the system ruby, you can usually install gems with sudo gem install ... or gem --user-install install. Otherwise I'd recommend a ruby version manager like rvm, or - if you need a more general version manager for different languages - asdf. – Hedvige

Answer 5

You can use pg2sqlite for converting pg_dump output to sqlite.

#  Making dump
pg_dump -h host -U user -f database.dump database

#  Making sqlite database
pg2sqlite -d database.dump -o sqlite.db

Schemas is not supported by pg2sqlite, and if you dump contains schema then you need to remove it. You can use this script:

# sed 's/<schema name>\.//' -i  database.dump
sed 's/public\.//' -i  database.dump
pg2sqlite -d database.dump -o sqlite.db

Froth

Tried this tool. It ran for a few seconds, reported no errors and produced a sqlite db of zero bytes :/ – Toon

This programme currently fails with exceptions against a dump from Postgresql 15. – Nursery

Was fixed in 1.0.1 – Froth

in ubuntu 22.04 -- $ pg2sqlite -d database.dump -o sqlite.db -- pg2sqlite: command not found – Jupon

Errors when running: % java -jar pg2sqlite-1.0.3.jar -d stb_backup_2.sql -o sqlite.db 14:38:04.648 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ - Create Index - Exception: [SQLITE_ERROR] SQL error or missing database (near "order": syntax error) [SQL] 'CREATE INDEX index_words_on_order ON words (order)' [LINE #1724] CREATE INDEX index_words_on_order ON words USING btree ("order");, 14:38:04.649 [main] ERROR com.github.caiiiycuk.pg2sqlite.Boot$ - Task failed... – Coppola

Answer 6

In case one needs a more automatized solution, here's a head start:

#!/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. prefix 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

Affinal

great script ! what about boolean values ? what do you do with those ? – Woolcott

What's memseq? I'm getting this error: pg_dump: error: too many command-line arguments (first is "memseq") – Weeping

Answer 7

when I faced with same issue I did not find any useful advices on Internet. My source PostgreSQL db had very complicated schema.
You just need to remove from your db-file manually everything besides table creating
More details - here

Brickwork

Answer 8

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

And I've started using the Postgres.app on my Mac (no install needed, drop the app in your Applications directory, although might have to add one line to your PATH envirnment variable as described in the documentation), with Induction.app as a GUI tool to view/query the database.

Dabster

But this question is about going the other way around - from postgres to sqlite, not to postgres, as your comment and reference suggest. – Gaberlunzie