Use Postgres dump files to populate CartoDB instance

load_dmp_2_cartodb.sh

#!/bin/bash

#------------------------------------------------------------
# We assume the dump files have been generated using pg_dump:
#
# pg_dump -a --column-inserts -x -O -t table_name database_name -f /tmp/dmp_file_name
#
#------------------------------------------------------------

# Provide details of your CartoDB account:
account="your_account"
apikey="XXXXXXXXXXXXXXXXXXX"

# If your are hosting CartoDB yourself, edit hostname, portnumber etc...
url="https://$account.cartodb.com/api/v2/sql/?api_key=$apikey"

query="q="

# We assume dump files are in the /tmp directory

for file in `find /tmp -type f -name '*.dmp'`;
do

# delete first 23 lines from the file (because of -x -O parameters in pg_dump)

    sed -i '1,23d' $file

# delete last 6 lines from the file (because of -x -O parameters in pg_dump)
    sed -i -n -e :a -e '1,6!{P;N;D;};N;ba' $file

# file contains only lines of INSERT statements by now

    COUNTER=1
    while read line; do
        encoded_value=$(python -c "import urllib; print urllib.quote('''$line''')")
        curl -o -X POST $url -d $query$encoded_value >/dev/null 2>&1
        echo $COUNTER
        let COUNTER=COUNTER+1
    done < $file
done;
exit 0