-
-
Save prodamin/334e761dd2e879d9f803886a5cd44a82 to your computer and use it in GitHub Desktop.
Convert a PostgreSQL database from SQL_ASCII to UTF8 encoding
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
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
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 bypg_dump
. Am I missing something because it would be very useful if this was possible.