Как изменить кодировку символов базы данных postgres?

во-первых, ответ Даниэля является правильным, безопасным вариантом.

для конкретного случая изменения с SQL_ASCII на что-то другое, вы можете обмануть и просто ткнуть каталог pg_database, чтобы переназначить кодировку базы данных. Это предполагает, что вы уже сохранили все символы, отличные от ASCII, в ожидаемой кодировке (или что вы просто не использовали символы, отличные от ASCII).

затем вы можете сделать:

update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'thedb'

это не изменит сортировки база данных, как только закодированные байты преобразуются в символы (так что теперь length('£123') вернет 4 вместо 5). Если база данных использует параметры сортировки "C", не должно быть никаких изменений в заказе для строк ASCII. Скорее всего, вам придется перестроить любые индексы, содержащие символы, отличные от ASCII.

будьте бдительны. Сброс и перезагрузка обеспечивает способ проверить содержимое базы данных на самом деле в кодировке, которую вы ожидаете, и это не так. и если окажется, что у вас есть некоторые неправильно закодированные данные в базе данных, спасение будет трудно. Поэтому, если вы можете, сбросьте и повторно инициализируйте.

сброс базы данных с определенной кодировкой и попытка восстановить ее в другой базе данных с другой кодировкой может привести к повреждению данных. Кодирование данных должно быть установлено до того, как какие-либо данные будут вставлены в базу данных.

Регистрация этой : при копировании любой другой базы данных параметры кодировки и локали не могут быть изменены из исходной базы данных, поскольку это может привести к повреждению данных.

и этой : некоторые категории локали должны иметь фиксированные значения при создании базы данных. Вы можете использовать разные настройки для разных баз данных, но как только база данных будет создана, вы больше не сможете изменить их для этой базы данных. LC_COLLATE и LC_CTYPE являются эти категории. они влияют на порядок сортировки индексов, поэтому они должны быть исправлены, или индексы на столбцы текста будет поврежден. (но вы можете смягчить это ограничение, используя параметры сортировки, как описано в разделе 22.2.)значения по умолчанию для этих категорий определяются при запуске initdb, и эти значения используются при создании новых баз данных, если не указано иное в команде CREATE DATABASE.

---

Я бы предпочел перестроить все с самого начала правильно с правильной локальной кодировкой на вашей ОС debian, как объяснено здесь:

su root

перенастроить локальные настройки :

dpkg-reconfigure locales

выберите свой язык (например, для французского языка в Швейцарии: fr_CH.В utf8)

удалить и правильно очистить postgresql:

apt-get --purge remove postgresql\*
rm -r /etc/postgresql/
rm -r /etc/postgresql-common/
rm -r /var/lib/postgresql/
userdel -r postgres
groupdel postgres

переустановить postgresql:

aptitude install postgresql-9.1 postgresql-contrib-9.1 postgresql-doc-9.1

теперь любая новая база данных будет автоматически создана с правильной кодировкой, lc_type (классификация символов) и LC_COLLATE (порядок сортировки строк).

ответ Даниэля Кутика правильный, но он может быть еще более безопасным, с

# dump into file
pg_dump myDB > /tmp/myDB.sql

# create an empty db with the right encoding (on older versions the escaped single quotes are needed!)
psql -c 'CREATE DATABASE "tempDB" WITH OWNER = "myself" LC_COLLATE = '\''de_DE.utf8'\'' TEMPLATE template0;'

# import in the new DB
psql -d tempDB -1 -f /tmp/myDB.sql

# rename databases
psql -c 'ALTER DATABASE "myDB" RENAME TO "myDB-wrong-encoding";' 
psql -c 'ALTER DATABASE "tempDB" RENAME TO "myDB";'

# see the result
psql myDB -c "SHOW LC_COLLATE"