Convert a PostgreSQL database from SQL_ASCII to UTF8 encoding
# convert createdb's template to UTF8 | |
echo "UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';" | psql -U postgres | |
echo "drop database template1;" | psql -U postgres | |
echo "create database template1 with template = template0 encoding = 'UTF8';" | psql -U postgres | |
echo "update pg_database set datacl='{=c/postgres,postgres=CTc/postgres}' where datname='template1';" | psql -U postgres | |
echo "UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';" | psql -U postgres | |
# export and reimport as UTF8 | |
pg_dump -U uniiverse --encoding utf8 mydatabase -f mydatabase_utf8.sql | |
createdb -U postgres -E utf8 mydatabase_utf8 | |
psql -U postgres -f mydatabase_utf8.sql -d mydatabase_utf8 | |
echo "ALTER DATABASE mydatabase RENAME TO mydatabase_ascii" | psql -U postgres | |
echo "ALTER DATABASE mydatabase_utf8 RENAME TO mydatabase" | psql -U postgres |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment