c.buhtz wrote: > Is there a way (free, open source, Ubuntu) to convert a > PostgreSQL-database with data in it to a sqlite database?
Hello, 1. Download the free open source Java application MyJSQLView. http://myjsqlview.com/download.html 2. Insure the PostgreSQL JDBC driver is installed in the class path, best just put in /java/jre/lib/ext/ directory of the installed OS. https://jdbc.postgresql.org/download.html 3. Open the PostgreSQL database. A. Export Database Scheme: Data | Export | SQL | Database Scheme At this point you can dump database tables individually in SQL or CSV or as a whole. Now depending on the tables size this can time consumming. B. Export Database SQL/CSV Data | Export | SQL | Table Data | Export | CSV | Table 4. Recreate the SQLite required tables from the exported PostgreSQL Database Scheme, from 3.A. Example datatype tables can help from the test directory of the MyJSQLView application. 5. Open the Desired clean SQLite database. A. Import your recreated SQL tables files derived from 4. Data | Import | SQL Dump B. Import you data for each table derived from either 3.B. SQL/CSV. Data | Import | SQL Dump/CSV File As indicated by others if you have special PG data types, arrays, GIS, others then you going to have to resolve the conversion data. I have done this for recreating virtual tables with other lite database, but not SQLite. That is a plugin that is not included with the MyJSQLView application. See below code for Data Type Conversion information. http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/datasource/TypeID.java http://code.google.com/p/myjsqlview/source/browse/trunk/myjsqlview/src/com/dandymadeproductions/myjsqlview/datasource/TypesInfoCache.java A good part of this can be done as indicated by others with PostgreSQL pgdump, but that usually gives a lot of other data related to recreating a PostgreSQL database that is not going to apply to your liter SQLite database. I would recommmend chunking this into bites rather then trying to import all or nothing approach. Dana M. Proctor MyJSQLView Project Manager.
