SQL: pgloader sqlite db > postgres

Fucking nighmare! I wanted to migrate a 30gb sqlite database into postgres. Apart from the Tablespace nonsense (required for storing data on external drive), the pgloader file that eventually worked is:

LOAD DATABASE
    FROM sqlite:///home/<user>/chembl_36.db
    INTO postgresql:///chembl_36

WITH
    create tables,
    create indexes,
    reset sequences,
    batch size = 25MB,
    prefetch rows = 10000

SET
    work_mem to '128MB',
    maintenance_work_mem to '1GB';

Must be run directly from the server though. pgloader is a BITCH when it comes to ssh connections.

Run with external variable (pgloader doesn’t like quotes, or much else for that matter)

PGUSER=<pg_user> PGPASSWORD='<pword>' pgloader migrate.load

This still gave so many import errors that I went with a python script instead, imported without indices and manually re-indexed.