Postgresql ошибка кодировки

В статье пойдёт речь о том, как добиться корректного вывода кириллицы в «консоли» Windows (cmd.exe).

Описание проблемы

В дистрибутив PostgreSQL, помимо всего прочего, для работы с СУБД входит:

• приложение с графическим интерфейсом pgAdmin;
• консольная утилита psql.

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

Ну и зачем тогда работать с psql, кому нужно долбить клавиатурой в консольке, когда можно всё сделать красиво и быстро в pgAdmin? Ну, не всегда pgAdmin доступен, особенно если речь идёт об удалённой машине. Кроме того, выполнение SQL-запросов в текстовом режиме консоли — это +10 к хакирству.

Решение проблемы

Версии ПО:

• MS Windows 7 SP1 x64;
• PostgreSQL 8.4.12 x32.

На сервере имеется БД, созданная в кодировке UTF8.

Суть

Суть проблемы в том, что cmd.exe работает (и так будет до скончания времён) в кодировке CP866, а сама Windows — в WIN1251, о чём psql предупреждает при начале работы:

WARNING: Console code page (866) differs from Windows code page (1251)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.

Значит, надо как-то добиться, чтобы кодировка была одна.

В разных источниках встречаются разные рецепты, включая правку реестра и подмену файлов в системных папках Windows. Ничего этого делать не нужно, достаточно всего трёх шагов:

1. сменить шрифт у cmd.exe;
2. сменить текущую кодовую страницу cmd.exe;
3. сменить кодировку на стороне клиента в psql.

Конкретные действия

Супер быстро и просто

Запускаете cmd.exe, оттуда psql:

psql -d ВАШАБАЗА -U ВАШЛОГИН

Далее:

psql ! chcp 1251

Быстро и просто

Запускаете cmd.exe, оттуда psql:

psql -d ВАШАБАЗА -U ВАШЛОГИН

Вводите пароль (если установлен) и выполняете команду:

set client_encoding='WIN866';

И всё. Теперь результаты запроса, содержащие кириллицу, будут отображаться нормально. Но есть небольшой косяк:

Потому предлагаем ещё способ, который этого недостатка лишён.

Посложнее и подольше

Запустить cmd.exe, нажать мышью в правом левом верхнем углу окна, там Свойства — Шрифт — выбрать Lucida Console. Нажать ОК.

Выполнить команду:

chcp 1251

В ответ выведет:

Текущая кодовая страница: 1251

Запустить psql;

psql -d ВАШАБАЗА -U ВАШЛОГИН

Кстати, обратите внимание — теперь предупреждения о несовпадении кодировок нет.

Выполнить:

set client_encoding='win1251';

Он выведет:

SET

Всё, теперь кириллица будет нормально отображаться.

Проверяем:

I’m working in a centralized monitoring system on Windows 2008 R2, I have installed a PostgreSQL 9.3 to use psql from the command line.

When I try to access to some remote Postgres (an 8.4 in my principal case) I have an error with the encoding:

command:

psql.exe -h 192.168.114.12 -p 5432 -d db_seros_transaccion -U postgres -f script.sql

error:

psql: FATAL:  la conversión entre WIN1252 y LATIN1 no está soportada

I try adding the sentence

SET client_encoding = 'UTF8';

in my script but the problem persist (and with other encodings too, like LATIN1 & WIN1252).

After googling it I found people that update some rows in the server to make the connection, and this is a problem to me.

Can anyone help me to make a connection using psql without an update? Is it possible?

В процессе настройки сервера для сайта, пришлось столкнуться с некоторыми проблемами. В частности с проблемой кодировки базы данных PostgreSQL. Дело в том, что при установке PostgreSQL, шаблоны баз данных создавались с кодировкой LATIN1, а сайт работает на Django, с использованием кодировки UTF8. В результате, при попытке вставки данных выпадала следующая ошибка:

ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1.


  Поискав информацию, удалось найти несколько решений, среди которых было решение, которое позволяет пересоздать шаблон базы данных с кодировкой UTF8. Но пройдёмся внимательно по симптомам задачи.
 


  Проверка кодировки

 

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

  Заходим в режим работы с PoctgreSQL:
 
sudo -u postgres psql


  psql — это утилита для работы с базами данных, а postgres — это супер пользователь PostgreSQL.
 

  И выполняем следующие команды:
 
postgres=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 LATIN1
(1 row)

postgres=# l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US | 
 template0 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)



  В выводе будет показана кодировка LATIN1, как для сервера, так и для баз данных.
 

  Исправление кодировки для template1

 

  А теперь исправим кодировку template1, который используется для создания баз данных.
 
postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
postgres=# DROP DATABASE Template1;
postgres=# CREATE DATABASE template1 WITH owner=postgres ENCODING = 'UTF-8' lc_collate = 'en_US.utf8' lc_ctype = 'en_US.utf8' template template0;
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';


  В данном случае сначала мы указываем, что template1 не является шаблоном для баз данных. Удаляем данный шаблон. Потом создаём новый шаблон с кодировкой UTF8 и устанавливаем данную базу данных в качестве шаблона для новых баз данных. Дальше новые базы данных будут создаваться с кодировкой UTF8.
 

  И проверим, в какой кодировке у нас находиться шаблон template1, сам сервер, кстати, будет по-прежнему в кодировке LATIN1
 
postgres=# l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | LATIN1   | en_US      | en_US      | 
 template0 | postgres | LATIN1   | en_US      | en_US      | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(3 rows)

Я так понимаю, что где-то неправильная кодировка, но не пойму где именно.

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

База, которую нужно перенести, содержит кодировку UTF8 и локаль russian_Russia.1251
client_encoding — WIN1251
server_encoding — UTF8

Дамп создаю так:

pg_dump -U postgres -W -E UTF8 -d dbname > dbname.sql

Эксперимента ради, создала другую базу с локалью ru_RU.UTF8.
В дампе те же самые кракозябры, вот такого вида:
h%*h%�%d%h%%h%�%d%h%

Пробовала сделать SET CLIENT_ENCODING TO UTF8 , дамп всё тот же.

При этом, я пытаюсь перенести базу с Windows на Linux и в Linux с помощью file проверила кодировку получившегося дампа — UTF-16. Хотя я при его создании явно указываю уже UTF8.

Дополню:
Системная локаль у меня в линуксе:

$ locale
LANG=en_US.UTF-8
LANGUAGE=en_US
LC_CTYPE="en_US.UTF-8"
LC_NUMERIC=ru_UA.UTF-8
LC_TIME=ru_UA.UTF-8
LC_COLLATE="en_US.UTF-8"
LC_MONETARY=ru_UA.UTF-8
LC_MESSAGES="en_US.UTF-8"
LC_PAPER=ru_UA.UTF-8
LC_NAME=ru_UA.UTF-8
LC_ADDRESS=ru_UA.UTF-8
LC_TELEPHONE=ru_UA.UTF-8
LC_MEASUREMENT=ru_UA.UTF-8
LC_IDENTIFICATION=ru_UA.UTF-8
LC_ALL=

Список баз с кодировками и локалями там же:

Какие манипуляции делала с уже готовым дампом:

Восстанавливаю данные из дампа в предварительно созданную базу
$ psql -U valen running_events < events.sql
ERROR: invalid byte sequence for encoding «UTF8»: 0xff

При этом если открыть файл дампа в редакторе кода, вижу:
SET client_encoding = ‘UTF8’;

Проверяю кодировку дампа:
$ file events.sql
events.sql: Little-endian UTF-16 Unicode text, with CRLF line terminators
Конвертирую:
$ iconv -f UTF-16 -t UTF-8 events.sql -o events2.sql
файл создался

Вторая попытка восстановить данные уже из конвертированного дампа:
$ psql -U valen running_events < events2.sql
данные записались

Проверяю вывод таблиц из базы:
running_events=# dt
List of relations
Schema | Name | Type | Owner
———-+———-+——-+———-
public | events | table | postgres
(1 row)

Действительно всё записалось. Но:
running_events=# SELECT * FROM events WHERE id = 1;

Снова кириллица некорректно отображается. Несмотря на то, что у базы running_events русская локаль.

Ещё один момент:
В Windows (т. е. там же, где делаю дамп) происходит та же история. Из дампа данные в базу не записываются.

Ещё дополнение:
Решила проделать всю операцию от начала и до конца в Ubuntu. То есть, я создала базу с кодировкой UTF8 и локалью ru_RU.UTF8, вставила в неё одну запись, содержащую в одном из полей русский текст, потом сделала её дамп. Открыла в редакторе кода — вообще никаких некорректных символов, кириллица тоже отображается.
Восстановила данные из дампа в свежесозданную базу — всё записалось с первого раза. Проверила в psql — на запрос SELECT вижу ту единственную запись, кириллица отображается верно.

И всё же пока не понятно, почему в Windows так упорно не хочет создаваться нормальный дамп…

Файлы дампов на гитхабе:
https://github.com/composercoder/running-events/tr…

В этой директории есть три .sql файла.

• events_dump.sql — дамп основной базы из винды, которую я пытаюсь перенести
• events_dump_converted_to_utf8.sq — тот же, конвертированный в utf8
• events_linux_dump.sql — дамп такой же базы, созданной в убунту, с такой же таблицей, но всего одной записью