Batch encoding conversion

Today I found out (the hard way - customer call!) that I had created a whole bunch of databases on a server in UNICODE encoding, when the locale picked only supported LATIN1. There were also a bunch of other dbs that were actually in LATIN1 that had no problems.

To solve this, I wrote a quick-n-dirty-n-hacky script to batch-convert all the databases from UNICODE to LATIN1. Luckily none of them were very large I'll post it here in the hope that it'll help someone - but consider it mainly something to build from. Specifically the errorchecking should perhaps be better than just an echo of the exitcode.

It solved my problem. YMMV.

#!/bin/sh

/usr/local/pgsql/bin/psql template1 postgres -A -t -c "SELECT datname FROM pg_database WHERE datallowconn AND encoding=6 AND NOT datname ~ '.*_s'" | while read D ; do

  echo "Converting $D"
  echo "-- start" > $D.dump
  echo "ALTER DATABASE $D RENAME TO ${D}_s;" >> $D.dump
  echo "CREATE DATABASE $D ENCODING='LATIN1' TEMPLATE=template0;" >> $D.dump
  echo "\\connect $D" >> $D.dump
  /usr/local/pgsql/bin/pg_dump -U postgres $D >> $D.dump
  /usr/local/pgsql/bin/psql -v ON_ERROR_STOP=1 -U postgres template1 -f $D.dump
  echo Exitcode $?
done