SQLite to Postgress migration

sqlite_to_postgres.md

SQLite to Postgres

Extracting from SQLite

We have two options:

1. Create a dump:

sqlite3 database.sqlite .dump > /path/to/sqlite-dumpfile.sql

2. Or we can export all tables as CSV files

for table in $(sqlite3 database.sqlite ".tables"); do
	echo $table
	sqlite3 -header -csv \
		database.sqlite "select * from $table;" > "$table.csv"
done

Importing to Postgres

Using CSV export files

1. Connect to Postgres using psql

psql <postgres-connection-string>

2. Change to the databse where you want to import

\c <your-database>

3. Import each CSV file

\copy "<table-name>" from "<your/exports/table-name.csv>" delimiter ',' csv header;

Using a dump file

TBD

Using pgloader

This might find issues depending ont he structure of the tables and its data types

1. Create a pgload.conf file with:

   load database  
       from 'database.sqlite'  
       into postgresql:///testdb 
          
   with include drop, create tables, create indexes, reset sequences  
            
   set work_mem to '16MB', maintenance_work_mem to '512 MB';
   

2. Run: pgloader pgload.conf