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

FOSDEM + PGDay 2017
Feb 2-4, 2017
Brussels, Belgium
SCALE+PGDays
Mar 2-5, 2017
Pasadena, California, USA
Confoo Montreal 2017
Mar 8-10, 2017
Montreal, Canada

Past

PGConf.Asia 2016
Dec 2-3, 2016
Tokyo, Japan
Berlin PUG
Nov 17, 2016
Berlin, Germany
PGConf.EU 2016
Nov 1-4, 2016
Tallinn, Estonia
Stockholm PUG 2016/5
Oct 25, 2016
Stockholm, Sweden
Postgres Vision 2016
Oct 11-13, 2016
San Francisco, USA
More past conferences