Задачи администрирования
Локализация
13
Авторские права
© Postgres Professional, 2016–2022.
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Назначение локализации
Локали и категории
Работа с датами, числами, денежными единицами
Настройка сервера и клиента
Настройка сообщений сервера и клиентских утилит
Правила сортировки
3
Назначение
Поддержка кодировок
выбор кодировки символов для различных языков
перекодировка символов между клиентом и сервером
Функционал, зависящий от локализации
сортировка и сравнение символов
функции upper, lower, initcap
поиск по шаблону
функции to_char с датами, числами, денежными единицами
язык сообщений сервера и утилит
Возможности локализации в PostgreSQL позволяют хранить текст
в различных кодировках. Для русского языка поддерживаются все
основные кодировки символов, включая UTF8, WIN1251, KOI8R,
ISO_8859_5.
Клиентское приложение может работать в кодировке, отличной от
кодировки сервера. В таком случае настраивается преобразование
символов между клиентом и сервером.
Кроме поддержки различных кодировок локализация влияет на работу
следующего функционала сервера:
●
сортировка символов, например в предложениях ORDER BY или
в операциях сравнения (>, <);
●
преобразование букв в верхний и нижний регистр в функциях upper,
lower, initcap;
●
поиск по шаблону в регулярных выражениях, операторах LIKE,
SIMILAR TO, включая поиск без учета регистра символов;
●
форматирование дат, чисел и денежных единиц в функциях to_char;
●
выбор языка сообщений сервера и утилит.
4
Локали и категории
Локали
определяют язык, территорию и кодировку символов,
например ru_RU.UTF8
установлены в операционной системе
Категории локали
lc_ctype классификация символов
lc_collate правила сортировки символов
lc_messages язык сообщений
lc_monetary формат денежных единиц
lc_numeric формат чисел
lc_time формат даты и времени
PostgreSQL использует возможности локализации, предоставляемые
операционной системой. Поэтому в ОС следует предварительно
настроить локали, которые потребуются для работы СУБД.
Обычно локали в ОС задаются в формате
«язык_территория.кодировка». Например, ru_RU.UTF8 определяет
локаль с русским языком (ru), на котором говорят в России (RU),
и кодировкой UTF8. В Windows используются развернутые имена:
Russian_Russia.1251.
Язык и территория определяют такие национальные особенности, как
порядок символов, формат даты, разделитель десятичных разрядов
и т. п.
Иногда бывает необходимо комбинировать поведение некоторых
функций из разных локалей. Например, вместе с правилами сортировки
русского языка использовать английские сообщения сервера.
PostgreSQL поддерживает раздельную установку категорий локали
через одноименные параметры конфигурации.
6
Настройка сервера
PostgreSQL
LC_COLLATE = en_US.UTF8
LC_CTYPE = en_US.UTF8
template0
initdb --encoding='UTF8' --locale='ru_RU.UTF8'
CREATE DATABASE db
TEMPLATE = template0
ENCODING = 'KOI8R'
LOCALE = 'ru_RU.koi8r'
db
template1
параметры локализации
нельзя изменить после
создания базы
Установка локали выполняется при инициализации кластера баз
данных. Утилита initdb использует локаль из окружения ОС, либо
локаль можно указать явно через соответствующие параметры.
При создании новой базы данных из шаблона template0 можно указать
параметры локализации, отличные от тех, что использовались при
инициализации кластера баз данных.
К основным параметрам локализации базы данных относятся:
кодировка символов (encoding), а также категории локали lc_ctype
и lc_collate, которые удобно задавать одним параметром (ключом)
locale, потому что их значения обычно совпадают.
На эти категории локали накладываются ограничения:
●
кодировки символов у locale (lc_ctype и lc_collate) должны совпадать
с кодировкой базы данных.
●
после создания базы данных locale (lc_ctype и lc_collate) изменить
нельзя.
Второе ограничение объясняется тем, что изменение правил
сортировки и классификации символов может нарушить работу
существующих индексов. Кроме того, может измениться поведение
операций сравнения текстовых строк в ограничениях CHECK и
табличных триггерах, что сделает уже сохраненные данные
некорректными.
Именно поэтому, если нужно создать базу с отличающимися
параметрами локализации, необходимо использовать шаблон
template0, в котором гарантированно нет никаких данных.
7
Настройка клиента
PostgreSQLpsql
терминал
chcp /
Terminal→Set Encoding
LC_ALL = ru_RU.UTF8
PGCLIENTENCODING = UTF8
backend
server_encoding
client_encoding
преобразование
символов (CREATE
CONVERSION)
Для настройки локализации клиентского приложения нужно сделать
следующее.
●
Проверить, что настройки сервера корректны. Как минимум, что
используется правильная кодировка БД (неизменяемый параметр
server_encoding покажет кодировку, заданную при создании БД).
●
Проверить, что в клиентской ОС установлены нужные локали, и
настроить категории локали (переменные среды LC_*) в сеансе ОС.
●
Настроить кодировку, с которой работает приложение, и проверить
настройки устройства вывода.
Например, psql в Windows обычно использует кодировку Win-1251,
поэтому в терминале (cmd.exe) необходимо выполнить команду chcp
1251 и установить шрифт true type (например, Lucida Console).
●
После подключения к БД проверить параметр client_encoding. Этот
параметр отвечает за перекодировку символов между клиентом и
сервером. При необходимости установить в значение кодировки
приложения. Значение client_encoding можно задать и в переменной
среды PGCLIENTENCODING.
Обслуживающий процесс автоматически перекодирует символы из
кодировки БД (server_encoding) в кодировку клиента (client_encoding).
Для большинства кодировок в PostgreSQL преднастроены необходимые
процедуры преобразования символов: они находятся в таблице
системного каталога pg_conversion. Возможно создание
дополнительных пользовательских процедур (CREATE CONVERSION).
9
Сообщения сервера
psql
configure --enable-nls ...
PostgreSQL
backend
lc_messages
журнал сообщений
ОШИБКА:
ошибка синтаксиса
ОШИБКА:
ошибка синтаксиса
Сообщения сервера (и утилит) PostgreSQL переведены на несколько
языков. В том числе и на русский.
Для того чтобы сообщения сервера выводились на русском языке,
нужно, чтобы сервер PostgreSQL был собран с поддержкой
национальных языков (configure --enable-nls).
Параметр конфигурации lc_messages управляет языком сообщений
сервера.
Сообщения сервера не только отправляются клиенту, но и
записываются в журнал сервера. При выборе языка, отличного от
английского, следует убедиться, что инструменты работы с журналом
сервера понимают этот язык. Например pgBadger может работать
только с английскими сообщениями.
Подробнее о переводе сообщений сервера для переводчиков
и разработчиков: https://postgrespro.ru/docs/postgresql/13/nls
10
Сообщения утилит
psql
configure --enable-nls ...
export LC_MESSAGES=ru
PostgreSQL
backend
=> SELECT version();
version
-------------------------
PostgreSQL 10.6
(1 строка)
Утилиты PostgreSQL (psql, pg_dump и пр.) также поддерживают
национальные языки.
Для того, чтобы сообщения утилит выводились на языке, отличном от
английского, нужно, чтобы клиент PostgreSQL был собран с поддержкой
NLS. Язык вывода устанавливается на клиенте переменной среды
LC_MESSAGES (параметр сервера lc_messages влияет только на
сообщения самого сервера, но не клиента).
Большинство ОС (включая Windows) используют следующий порядок
просмотра переменных среды для языка сообщений: LANGUAGE,
LC_ALL, LC_MESSAGES, LANG.
12
Правила сортировки
Объекты базы данных
разные правила сортировки в одной БД
начальное наполнение при создании БД
указываются для столбцов таблиц и в выражениях
Провайдеры
libc
ICU
Специальные правила сортировки
«C» и «POSIX»
Чтобы в одной базе данных можно было по-разному сортировать и
сравнивать текстовые данные, в PostgreSQL существуют специальные
объекты — правила сортировки (collation). Они позволяют использовать
порядок сортировки, отличный от установленного по умолчанию для
базы данных.
При создании правила сортировки указываются внешняя библиотека,
реализующая сортировку (провайдер), и классификация символов.
Начиная с PostgreSQL 10 можно выбирать между библиотеками ICU
и libc; до этого всегда использовалась libc.
Начальный список объектов — правил сортировки — формируется при
инициализации кластера. Для всех имеющихся в ОС локалей в базу
данных загружаются правила сортировки. В дальнейшем при
добавлении локалей в ОС можно дозагрузить правила.
Правила сортировки можно использовать при создании текстовых
столбцов таблиц, при определении доменов и просто в выражениях, где
сортируются или сравниваются текстовые строки.
Специальные правила сортировки «C» и «POSIX» работают одинаково
и создаются для всех кодировок сервера. Для этих правил буквами
будут считаться только латинские символы от A до Z, все остальные
знаки будут сортироваться в соответствии со своими кодами в данной
кодировке.
Посмотреть имеющиеся правила сортировки можно в таблице
системного каталога pg_collation.
13
Провайдер libc
Порядок символов
зависит от реализации в операционной системе
Изменения в библиотеке
отслеживается номер версии
Использование
по умолчанию для базы данных или кластера
явное указание при определении столбцов и в выражениях
Правила сортировки провайдера libc в базе данных работают точно так
же, как и в операционной системе. Однако библиотека libc может быть
реализована по-разному в разных операционных системах. Как
следствие, сортировка для одних и тех же локалей может отличаться
в зависимости от ОС сервера базы данных. Если приложение должно
поддерживать работу (включая логическую репликацию) СУБД на
разных ОС, следует убедиться, что сортировка везде работает
корректно.
Более серьезной потенциальной проблемой является установка новой
версии библиотеки libc в ОС. Такое может произойти, например, при
переходе на новый сервер с новой версией ОС. Изменение в новой
версии libc используемых в базе данных правил сортировки может
привести к некорректной работе индексов и другим проблемам.
14
Провайдер ICU
Порядок символов
зависит от версии библиотеки, но не от операционной системы
Использование
явное указание при определении столбцов и в выражениях
нельзя использовать по умолчанию для базы данных или кластера
Дополнительные возможности
управление порядком сортировки групп символов
Для использования провайдера ICU в определении правил сортировки
необходимо, чтобы PostgreSQL был собран с поддержкой этой
библиотеки. Библиотека ICU и реализованные в ней правила
сортировки работают одинаково на всех операционных системах.
Библиотека ICU допускает видоизменение правил сортировки без
смены языка и территории. Можно указать разный порядок сортировки
для отдельных групп символов. Например, символы какой группы
должны идти раньше: кириллица, латиница или цифры; буквы
в верхнем регистре или в нижнем.
При инициализации кластера баз данных или при создании новой БД
можно указать только локали библиотеки libc. Использовать в таком
качестве библиотеку ICU можно, начиная с версии PostgreSQL 15.
16
Детерминизм
Детерминированные правила сортировки
равенство строк при побайтовом совпадении
Недетерминированные правила сортировки
строки, состоящие из разных байтов, могут быть равными
дополнительная гибкость
производительность ниже
не поддерживают LIKE и другие операции
Правило сортировки в зависимости от типа используемого сравнения
может быть детерминированным или недетерминированным.
Детерминированные сравнения считают равными строки, состоящие
из одних и тех же байтов. При недетерминированном сравнении даже
не совпадающие побайтово строки могут считаться равными.
Например, в Unicode буква «ё» может быть представлена одним
символом Cyrillic Small Letter Io (U+0451), а может быть составлена из
буквы «е» (U+0435) и диакритического знака «диерезис» (U+0308).
Все стандартные правила — детерминированные. Пользовательские
правила по умолчанию тоже будут детерминированными.
Недетерминированные правила дают дополнительную гибкость: можно,
например, задать ограничение уникальности без учета регистра или
диакритических символов. Но производительность детерминированных
правил обычно выше. К тому же недетерминированные правила не
поддерживают некоторые операции, такие как поиск по шаблону.
18
Отслеживание изменений
Изменение библиотеки-провайдера приводит к проблемам
неправильная сортировка значений в индексах
сравнения строк в ограничениях целостности
сравнения строк в запросах
и т. д.
Номер версии библиотеки хранится в системном каталоге
При изменении установленной версии — предупреждение
libc не гарантирует стабильность и платформонезависимость версии
Если при изменении библиотеки-провайдера сравнение начинает
работать иначе, это может повлиять на используемые в базе данных
правила сортировки и вызвать проблемы:
- неправильную работу запросов из-за нарушения сортировки значений
в индексах;
- некорректные данные из-за изменившегося поведения при сравнении
строк в ограничениях целостности CHECK и триггерах;
- некорректные результаты из-за изменившегося поведения сравнения
строк в запросах, процедурном коде и политиках защиты строк.
Поэтому при использовании правил сортировки PostgreSQL будет
предупреждать о возможных проблемах, связанных с изменением
библиотек.
При создании правила текущий номер версии библиотеки сохраняется
в системном каталоге. При каждом использовании правила
сохраненный номер версии сверяется с текущим номером, и при
обнаружении расхождения запросы будут выдавать предупреждения
о несоответствии версий.
Если такая ситуация случилась, следует пересоздать индексы,
использующие измененные правила сортировки, а также проверить все
места, где используются правила сортировки.
Нужно помнить, что, в отличие от ICU, провайдер libc не гарантирует
одинаковую работу сортировки даже при совпадении версий
библиотеки.
20
Итоги
Перед инициализацией кластера нужные для СУБД локали
должны быть установлены в ОС
Клиент и сервер могут работать в различных кодировках
с автоматическим преобразованием символов
Сообщения сервера и утилит переведены на множество
языков, включая русский
Правила сортировки используют внешние библиотеки,
изменения в которых могут привести к повреждению
данных и некорректным результатам
21
Практика
1. Перенос данных между базами в разных кодировках.
Создайте базу данных с кодировкой KOI8R.
Создайте таблицу и добавьте в нее строки, содержащие
символы кириллицы.
Сделайте копию базы данных утилитой pg_dump.
Восстановите таблицу из копии в базу с кодировкой UTF8.
2. Получите номер сегодняшнего дня недели.
Меняется ли номер дня недели в зависимости от настроек
локализации?
1. Для создания БД в кодировке KOI8R:
- убедитесь, что в ОС установлена нужная локаль;
- в команде CREATE DATABASE используйте шаблон template0
параметры ENCODING и LOCALE.
2. Для получения номера дня недели используйте функцию to_char.
Допустимые форматные маски даты:
