Skip to content

Instantly share code, notes, and snippets.

@emacgillavry
Created November 12, 2012 13:31
Show Gist options
  • Save emacgillavry/4059409 to your computer and use it in GitHub Desktop.
Save emacgillavry/4059409 to your computer and use it in GitHub Desktop.
Use Postgres dump files to populate CartoDB instance
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment