Skip to content

Instantly share code, notes, and snippets.

@bittner

bittner/sqlite2pg.sh

Forked from eclubb/sqlite2pg.sh
Last active January 23, 2024 08:35
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bittner/7368128 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bittner/7368128 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. bittner revised this gist Nov 15, 2013. 1 changed file with 41 additions and 10 deletions.
    51 changes: 41 additions & 10 deletions sqlite2pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -1,31 +1,62 @@
    #!/bin/sh
    #!/bin/bash

    # This script will migrate schema and data from a SQLite3 database to PostgreSQL.
    # Schema translation based on http://stackoverflow.com/a/4581921/1303625.
    # Some column types are not handled (e.g blobs).
    #
    # See also:
    # - http://stackoverflow.com/questions/4581727/convert-sqlite-sql-dump-file-to-postgresql
    # - https://gist.github.com/bittner/7368128

    # cross-OS compatibility (greadlink, gsed, gzcat are GNU implementations for OSX)
    readlink=readlink; sed=sed; zcat=zcat
    [[ `uname` == 'Darwin' ]] && {
    readlink=greadlink; sed=gsed; zcat=gzcat
    which $readlink $sed $zcat > /dev/null || {
    echo 'ERROR: GNU utils required for Mac. You may use homebrew to install them: brew install coreutils gnu-sed'
    exit 1
    }
    }

    [[ "$3" == "" || "$4" != "" ]] && {
    echo "Sqlite3 to PostgreSQL database migration: Dump all data from an existing Sqlite database, and create a new PostgreSQL DB from it."
    echo "Usage: ${0##*/} <sqlite_src_db_file> <pg_dest_db_name> <pg_dest_user>"
    exit 1
    }

    SQLITE_DB_PATH=$1
    PG_DB_NAME=$2
    PG_USER_NAME=$3

    SQLITE_DUMP_FILE="sqlite_data.sql"
    SQLITE_DUMP_FILE="/tmp/sqlite_dump_data.sql"

    sqlite3 $SQLITE_DB_PATH .dump > $SQLITE_DUMP_FILE

    # PRAGMAs are specific to SQLite3.
    sed -i '/PRAGMA/d' $SQLITE_DUMP_FILE
    $sed -i '/PRAGMA/d' $SQLITE_DUMP_FILE
    # Remove unsigned as Postgres doesn't know it.
    $sed -i 's/ unsigned[ ]*/ /g' $SQLITE_DUMP_FILE
    # Convert sequences.
    sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig' $SQLITE_DUMP_FILE
    $sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig ; s/"id" integer NOT NULL PRIMARY KEY/"id" serial NOT NULL PRIMARY KEY/g' $SQLITE_DUMP_FILE
    # Convert column types.
    sed -i 's/datetime/timestamp/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE

    createdb -U $PG_USER_NAME $PG_DB_NAME
    psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE
    $sed -i 's/datetime/timestamp with time zone/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE
    # Convert 0/1 values for boolean types to '0'/'1'.
    for bool in 0 0 1 1; do
    # global flag seems to be broken(?) for -i on OSX GNU sed, so we loop twice
    $sed -i "s/,${bool},/,'${bool}',/g" $SQLITE_DUMP_FILE
    $sed -i "s/,${bool})/,'${bool}')/g" $SQLITE_DUMP_FILE
    $sed -i "s/(${bool},/('${bool}',/g" $SQLITE_DUMP_FILE
    done

    createdb -U $PG_USER_NAME $PG_DB_NAME || exit 2
    psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE || exit 2
    # TODO: grep out error and success messages and redirect them to ${SQLITE_DUMP_FILE}-{error,success}.log
    # 2>&1 | sed '/ERROR: current transaction is aborted, commands ignored until end of transaction block/d' | sed '/^CREATE TABLE$/d' | sed '/^INSERT 0 1$/d' | sed '/^CREATE INDEX$/d'

    # Update Postgres sequences.
    psql $PG_DB_NAME $PG_USER_NAME -c "\ds" | grep sequence | cut -d'|' -f2 | tr -d '[:blank:]' |
    while read sequence_name; do
    table_name=${sequence_name%_id_seq}

    psql $PG_DB_NAME $PG_USER_NAME -c "select setval('$sequence_name', (select max(id) from $table_name))"
    done
    psql $PG_DB_NAME $PG_USER_NAME -c "select setval('$sequence_name', (select max(id) from $table_name))" || exit 2
    done
  2. @eclubb eclubb revised this gist Mar 30, 2012. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions sqlite2pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -19,8 +19,7 @@ sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY
    # Convert column types.
    sed -i 's/datetime/timestamp/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE

    dropdb -U $PG_USER_NAME $PG_DB_NAME &&
    createdb -U $PG_USER_NAME $PG_DB_NAME &&
    createdb -U $PG_USER_NAME $PG_DB_NAME
    psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE

    # Update Postgres sequences.
  3. @eclubb eclubb created this gist Mar 30, 2012.
    32 changes: 32 additions & 0 deletions sqlite2pg.sh
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,32 @@
    #!/bin/sh

    # This script will migrate schema and data from a SQLite3 database to PostgreSQL.
    # Schema translation based on http://stackoverflow.com/a/4581921/1303625.
    # Some column types are not handled (e.g blobs).

    SQLITE_DB_PATH=$1
    PG_DB_NAME=$2
    PG_USER_NAME=$3

    SQLITE_DUMP_FILE="sqlite_data.sql"

    sqlite3 $SQLITE_DB_PATH .dump > $SQLITE_DUMP_FILE

    # PRAGMAs are specific to SQLite3.
    sed -i '/PRAGMA/d' $SQLITE_DUMP_FILE
    # Convert sequences.
    sed -i '/sqlite_sequence/d ; s/integer PRIMARY KEY AUTOINCREMENT/serial PRIMARY KEY/ig' $SQLITE_DUMP_FILE
    # Convert column types.
    sed -i 's/datetime/timestamp/g ; s/integer[(][^)]*[)]/integer/g ; s/text[(]\([^)]*\)[)]/varchar(\1)/g' $SQLITE_DUMP_FILE

    dropdb -U $PG_USER_NAME $PG_DB_NAME &&
    createdb -U $PG_USER_NAME $PG_DB_NAME &&
    psql $PG_DB_NAME $PG_USER_NAME < $SQLITE_DUMP_FILE

    # Update Postgres sequences.
    psql $PG_DB_NAME $PG_USER_NAME -c "\ds" | grep sequence | cut -d'|' -f2 | tr -d '[:blank:]' |
    while read sequence_name; do
    table_name=${sequence_name%_id_seq}

    psql $PG_DB_NAME $PG_USER_NAME -c "select setval('$sequence_name', (select max(id) from $table_name))"
    done