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

Add comment

New comments can no longer be posted on this entry.

Conferences

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

Upcoming

PGConf.EU 2025
Oct 21-24, 2025
Riga, Latvia

Past

PGConf.dev 2025
May 13-16, 2025
Montreal, Canada
Nordic PGDay 2025
Mar 18, 2025
Copenhagen, Denmark
SCaLE 2024
Mar 6-9, 2025
Pasadena, USA
FOSDEM PGDay 2025
Jan 30-Feb 2, 2025
Brussels, Belgium
PGConf.EU 2024
Oct 22-25, 2024
Athens, Greece
More past conferences