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

Ответ 1

Чтобы изменить кодировку вашей базы данных:

• Дамп вашей базы данных
• Отбросьте свою базу данных,
• Создать новую базу данных с различным кодированием
• Перезагрузите данные.

Убедитесь, что клиентское кодирование установлено правильно во время всего этого.

Источник: http://archives.postgresql.org/pgsql-novice/2006-03/msg00210.php

Ответ 2

Прежде всего, ответ Даниэля - правильный, безопасный вариант.

Для конкретного случая перехода с 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.

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

Ответ 3

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

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

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

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

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 (порядок сортировки строк).

Ответ 4

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

Итак, по-настоящему безопасный способ:

• Создайте новую базу данных с различной кодировкой и именем
• Дамп вашей базы данных
• Восстановить дамп к новой базе данных
• Проверьте, что ваше приложение работает правильно с новым DB
• Переименуйте старый DB в нечто значимое
• Переименовать новую БД
• Повторное тестирование приложения
• Отбросить старую базу данных

В случае возникновения чрезвычайной ситуации просто переименуйте DBs назад

Ответ 5

# 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"