Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save prodamin/334e761dd2e879d9f803886a5cd44a82 to your computer and use it in GitHub Desktop.
Save prodamin/334e761dd2e879d9f803886a5cd44a82 to your computer and use it in GitHub Desktop.
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
@inspector71
Copy link

inspector71 commented Mar 19, 2023

Line 9 implies to me that an existing SQL_ASCII encoded database can be exported and transcoded (converted) on-the-fly into --encoding utf8. Is that the intention?

Received a few permissions errors, Then ...

pg_dump: Error message from server: ERROR: invalid byte sequence for encoding "UTF8": 0xf6 0x6c 0x7a 0x6c

This suggests, to me at least, that on-the-fly transcoding / converting of existing SQL_ASCII encoded database content is not supported by pg_dump. Am I missing something because it would be very useful if this was possible.

@0x09AF
Copy link

0x09AF commented May 18, 2024

Thank you so much!!!
I had a working Postgres database for paperless-NGX that broke during an upgrade. Authors recommended recreating the DB in UTF-8. Well, I didn't have to

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment