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

Conferences

I speak at and organize conferences around Open Source in general and PostgreSQL in particular.

Upcoming

Stockholm PUG 2016/3
Jun 16, 2016
Stockholm, Sweden
PGDay UK 2016
Jul 05, 2016
London, UK
PG Day'16 Russia
Jul 6-8, 2016
St Petersburg, Russia
Stockholm PUG 2016/4
Aug 31, 2016
Stockholm, Sweden
Postgres Open
Sep 13-16, 2016
Dallas, USA
PGConf.EU 2016
Nov 1-Jan 4, 2016
Tallinn, Estonia

Past

PGCon
May 17-21, 2016
Ottawa, Canada
Pycon Sweden
May 9-10, 2016
Stockholm, Sweden
Stockholm PUG 2016/2
Apr 26, 2016
Stockholm, Sweden
PGConf.US
Apr 18-20, 2016
New York, USA
pgDay Paris
Mar 31, 2016
Paris, France
More past conferences