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
New comments can no longer be posted on this entry.