gem-hunter
Language: Scala
pg2sqlite is easy to use solution to create sqlite database from postgresql dump
Easy to use solution to create sqlite database from postgresql dump.
pg_dump script formatIn release section you can download pre-built version of pg2sqlite.jar
Install jre (java) on your PC
Create dump from postgresql database
pg_dump -h host -U user -f database.dump database
java -jar pg2sqlite-1.0.3.jar -d database.dump -o sqlite.db
pg2sqlite -d <file> -o <file> [-f <true|false>]
<file> - file that contains dump of postgresql database (made by pg_dump, accepts .gz)<file> - file name of newly created sqlite3 database<true|false> - default: false, force database re-creation if database file alredy exists<integer|text|real> - default: integer, change sqlite3 date class (read below)SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:
By default pg2sqlite uses INTEGER to store dates, but you can change this with -t argument (-t text or -t real), use it like this:
java -jar pg2sqlite-1.0.3.jar -d database.dump -o sqlite.db -t text
pg2sqlite does not support database schemas. If your dump file includes schema definition It will print errors like this:
Create Table - Exception:
unknown database <schema>
[SQL] 'CREATE TABLE <schema>.table (...;'
You can easily fix dump file with sed:
# sed 's/<schema name>\.//' -i database.dump sed 's/public\.//' -i database.dump pg2sqlite -d output.dump -o sqlite.db
Where public is a schema name.
git clone https://github.com/caiiiycuk/postgresql-to-sqlite.git
cd postgresql-to-sqlite
sbt one-jar
cp target/scala-2.11/postgresql-to-sqlite_2.11-0.0.1-SNAPSHOT-one-jar.jar pg2sqlite.jarClone the repository and run
docker build -t postgresql-to-sqlite:latest .
inside the postgresql-to-sqlite folder.
Use
docker run -v /home/john/dbdata:/dbdata -e psource='/dbdata/pqdump.sql' -e starget='/dbdata/output.sqlite' -it postgresql-to-sqlite:latest
where
psource is the pqdump filename and folder & starget the sqlite filename and folderp.s. the schema removal has to be done outside the container
If you appreciate this project, please consider voting for it on Stack Overflow: