Задачи администрирования
Локализация
16
Авторские права
© Postgres Professional, 2016–2025
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Игорь Гнатюк
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
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_COLLATE — правила сортировки символов
LC_CTYPE — классификация символов
LC_MESSAGES lc_messages язык сообщений
LC_MONETARY lc_monetary формат денежных единиц
LC_NUMERIC lc_numeric формат чисел
LC_TIME lc_time формат даты и времени
PostgreSQL использует возможности локализации, предоставляемые
операционной системой. Поэтому в ОС следует предварительно
настроить локали, которые потребуются для работы СУБД.
Обычно локали в ОС задаются в формате «язык_регион.кодировка».
Например, ru_RU.UTF8 определяет локаль с русским языком (ru), на
котором говорят в России (RU), и кодировкой UTF8. В Windows
используются развернутые имена: Russian_Russia.1251.
Язык и территория определяют такие национальные особенности, как
порядок символов, формат даты, разделитель десятичных разрядов
и т. п.
Иногда бывает нужно комбинировать поведение разных локалей.
Например, вместе с форматами даты и времени, принятыми в России,
использовать английские сообщения сервера. PostgreSQL
поддерживает раздельную установку категорий локали через
одноименные параметры конфигурации.
Для категорий локали LC_COLLATE и LC_CTYPE соответствующие
неизменяемые параметры сервера упразднены начиная с версии
PostgreSQL 15; теперь за информацией о локали базы данных следует
обращаться к таблице системного каталога pg_database.
5
Провайдеры локалей
Стандартная библиотека libc
соответствие POSIX
Внешняя библиотека ICU
переносимое решение
широкие дополнительные возможности
PostgreSQL реализует поддержку различных провайдеров локалей.
Провайдеры — это библиотеки, предоставляющие данные локалей
и реализующие сортировку. Стандартный провайдер libc использует
системную библиотеку C и предоставляет ее локали. Эти локали
используются большинством утилит операционной системы.
Также поддерживается провайдер ICU, использующий одноименную
внешнюю библиотеку. Имена локалей в ICU строятся по правилам
языковых меток IETF BCP 47. Такие метки имеют вид «язык-регион»
или просто «язык» (например ru-RU). При использовании локалей ICU
PostgreSQL проверяет имена и приводит их к каноническому виду в
соответствии с правилами. Параметр icu_validation_level позволяет
настроить уровень сообщений при проверках локалей.
Информация о выбранном провайдере и локали базы сохраняется
в столбцах pg_datlocprovider и daticulocale таблицы pg_database.
По ряду причин настройки libc нужны во всех базах данных (даже
использующих провайдер ICU), поэтому в столбцах datcollate и datctype
всегда будет информация о локали libc.
Команды и утилиты для установки параметров локали позволяют явно
выбрать и провайдера. По умолчанию используется провайдер libc.
6
Настройка сервера
PostgreSQL
LC_COLLATE = en_US.UTF8
LC_CTYPE = en_US.UTF8
template0
initdb --locale-provider=icu --icu-locale='ru-RU'
CREATE DATABASE db
LOCALE_PROVIDER = 'libc'
TEMPLATE = template0
ENCODING = 'KOI8R'
LOCALE = 'ru_RU.koi8r'
db
template1
параметры локализации
нельзя изменить после
создания базы
свойства кодировки
должны соответствовать
локали
При инициализации кластера провайдер и локаль шаблонных баз
задаются с помощью ключей утилиты initdb. По умолчанию
используется провайдер libc и локаль из окружения ОС.
При создании базы данных она наследует настройки локализации
шаблона. Если требуется создать базу с другими параметрами
локализации, нужно использовать шаблон template0, в котором
гарантированно нет никаких данных, зависящих от локали. При этом
можно сменить провайдера, кодировку символов, а также категории
локали LC_CTYPE и LC_COLLATE. Значения для категорий обычно
совпадают, поэтому их удобно задавать одним параметром (ключом):
для libc — LOCALE, а для ICU — ICU_LOCALE.
При этом кодировка базы данных должна быть совместима
с параметрами локали LC_CTYPE и LC_COLLATE этой базы данных.
Локали C и POSIX совместимы с любым набором символов, а для
остальных локалей провайдера libc есть только один набор символов,
который будет работать правильно. (Исключение: в среде Windows
кодировка UTF-8 может использоваться с любой локалью.) Локали
провайдера ICU можно использовать с большинством кодировок.
8
Настройка клиента
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),
однако на практике это вряд ли потребуется.
10
Сообщения сервера
psql
configure --enable-nls ...
PostgreSQL
backend
lc_messages
журнал сообщений
ОШИБКА:
ошибка синтаксиса
ОШИБКА:
ошибка синтаксиса
Сообщения сервера (и утилит) PostgreSQL переведены на несколько
языков, в том числе и на русский.
Для того чтобы сообщения сервера выводились на русском языке,
нужно, чтобы сервер PostgreSQL был собран с поддержкой
национальных языков (configure --enable-nls). Большинство пакетов
собирается с таким параметром.
Параметр конфигурации lc_messages управляет языком сообщений
сервера.
Сообщения сервера не только отправляются клиенту, но и
записываются в журнал сервера. При выборе языка, отличного от
английского, следует убедиться, что инструменты работы с журналом
сервера понимают этот язык. Например pgBadger может работать
только с английскими сообщениями.
Подробнее о переводе сообщений сервера для переводчиков
и разработчиков: https://postgrespro.ru/docs/postgresql/16/nls
11
Сообщения утилит
psql
configure --enable-nls ...
export LC_MESSAGES=ru
PostgreSQL
backend
=> SELECT version();
version
-------------------------
PostgreSQL 16.4
(1 строка)
Утилиты PostgreSQL (psql, pg_dump и пр.) также поддерживают
национальные языки.
Для того, чтобы сообщения утилит выводились на языке, отличном от
английского, нужно, чтобы клиент PostgreSQL был собран с поддержкой
NLS (обычно это так). Язык вывода устанавливается на клиенте
переменной среды LC_MESSAGES (параметр сервера lc_messages
влияет только на сообщения самого сервера, но не клиента).
Большинство ОС (включая Windows) используют следующий порядок
просмотра переменных среды для языка сообщений: LANGUAGE,
LC_ALL, LC_MESSAGES, LANG.
13
Правила сортировки
Объекты базы данных
разные правила сортировки в одной БД
начальное наполнение при создании БД
указываются для столбцов таблиц и в выражениях
Стандартные правила сортировки
default, C, POSIX
unicode, ucs_basic
Чтобы в одной базе данных можно было по-разному сортировать и
сравнивать текстовые данные, в PostgreSQL существуют специальные
объекты — правила сортировки (collation). Они позволяют использовать
порядок сортировки, отличный от установленного по умолчанию для
базы данных.
Правила сортировки можно использовать при создании текстовых
столбцов таблиц, в определении доменов и просто в выражениях,
где сортируются или сравниваются текстовые строки. При создании
правила сортировки указывается провайдер и классификация
символов.
Начальный список правил сортировки формируется при инициализации
кластера: для всех имеющихся в ОС локалей загружаются их правила
сортировки. В дальнейшем при добавлении локалей в ОС можно
дозагрузить правила.
Стандартные правила сортировки C и POSIX работают одинаково
и создаются для всех кодировок сервера. Для этих правил буквами
считаются латинские символы от A до Z, а остальные сортируются
в соответствии со своими кодами в данной кодировке. Правило default
использует значения LC_COLLATE, LC_CTYPE и провайдера, заданных
при создании базы данных. Также доступны правила сортировки
unicode и ucs_basic, определенные в стандарте SQL.
Все имеющиеся правила сортировки можно увидеть в таблице
системного каталога pg_collation.
14
Провайдер libc
Порядок символов
зависит от реализации в операционной системе
Изменения в библиотеке
отслеживается номер версии
Использование
по умолчанию для базы данных или кластера
явное указание при определении столбцов и в выражениях
Правила сортировки провайдера libc в базе данных работают точно так
же, как и в операционной системе. Однако библиотека libc может быть
реализована по-разному в разных операционных системах. Как
следствие, сортировка для одних и тех же локалей может отличаться
в зависимости от ОС сервера базы данных. Если приложение должно
поддерживать работу (включая логическую репликацию) СУБД на
разных ОС, следует убедиться, что сортировка везде работает
корректно.
Более серьезной потенциальной проблемой является установка новой
версии библиотеки libc в ОС. Такое может произойти, например, при
переходе на новый сервер с новой версией ОС. Изменение в новой
версии libc используемых в базе данных правил сортировки может
привести к некорректной работе индексов и другим проблемам.
15
Провайдер ICU
Порядок символов
зависит от версии библиотеки, но не от операционной системы
Использование
явное указание при определении столбцов и в выражениях
Дополнительные возможности
управление порядком сортировки групп символов
дополнительные (особые) правила сортировки
Для использования провайдера ICU в определении правил сортировки
необходимо, чтобы PostgreSQL был собран с поддержкой этой
библиотеки. Библиотека ICU и реализованные в ней правила
сортировки работают одинаково на всех операционных системах.
Библиотека ICU допускает видоизменение правил сортировки без
смены языка и территории. Можно указать разный порядок сортировки
для отдельных групп символов. Например, символы какой группы
должны идти раньше: кириллица, латиница или цифры; буквы
в верхнем регистре или в нижнем. Для настройки этих правил можно
включить в языковую метку соответствующие параметры.
Дополнительные правила сортировки задаются особым синтаксисом,
их примеры приведены в демонстрации.
17
Детерминизм
Детерминированные правила сортировки
равенство строк при побайтовом совпадении
Недетерминированные правила сортировки
строки, состоящие из разных байтов, могут быть равными
дополнительная гибкость
производительность ниже
не поддерживают LIKE и другие операции
Правило сортировки в зависимости от типа используемого сравнения
может быть детерминированным или недетерминированным.
Детерминированные сравнения считают равными строки, состоящие
из одних и тех же байтов. При недетерминированном сравнении даже
не совпадающие побайтово строки могут считаться равными.
Например, в Unicode буква «ё» может быть представлена одним
символом Cyrillic Small Letter Io (U+0451), а может быть составлена
из буквы «е» (U+0435) и диакритического знака «диерезис» (U+0308).
Все стандартные правила — детерминированные. Пользовательские
правила по умолчанию тоже будут детерминированными.
Недетерминированные правила дают дополнительную гибкость: можно,
например, задать ограничение уникальности без учета регистра или
диакритических символов. Но производительность детерминированных
правил обычно выше. К тому же недетерминированные правила не
поддерживают некоторые операции, такие как поиск по шаблону.
19
Отслеживание изменений
Изменение библиотеки-провайдера приводит к проблемам
неправильная сортировка значений в индексах
сравнения строк в ограничениях целостности
сравнения строк в запросах
и т. д.
Номер версии библиотеки хранится в системном каталоге
При изменении установленной версии — предупреждение
libc не гарантирует стабильность и платформонезависимость версии
Если при изменении библиотеки-провайдера сравнение начинает
работать иначе, это может повлиять на используемые в базе данных
правила сортировки и вызвать проблемы:
●
неправильную работу запросов из-за нарушения сортировки
значений в индексах;
●
некорректные данные из-за изменившегося поведения при
сравнении строк в ограничениях целостности CHECK и триггерах;
●
некорректные результаты из-за изменившегося поведения сравнения
строк в запросах, процедурном коде и политиках защиты строк.
Поэтому при использовании правил сортировки PostgreSQL будет
предупреждать о возможных проблемах, связанных с изменением
библиотек.
При создании правила текущий номер версии библиотеки сохраняется
в системном каталоге, в таблицах pg_collation и pg_database. При
каждом использовании правила сохраненный номер версии сверяется
с текущим номером, и при их несоответствии запросы будут выдавать
предупреждения.
Если обнаружено расхождение, следует пересоздать индексы,
использующие измененные правила сортировки, а также проверить все
места, где используются эти правила.
Нужно помнить, что, в отличие от ICU, провайдер libc не гарантирует
одинаковую работу сортировки даже при совпадении версий
библиотеки.
21
Итоги
Перед инициализацией кластера нужные для СУБД локали
должны быть установлены в ОС
Клиент и сервер могут работать в различных кодировках
с автоматическим преобразованием символов
Сообщения сервера и утилит переведены на множество
языков, включая русский
Правила сортировки используют внешние библиотеки,
изменения в которых могут привести к повреждению
данных и некорректным результатам
22
Практика
1. Перенос данных между базами в разных кодировках.
Создайте базу данных с кодировкой KOI8R. Создайте
таблицу и добавьте в нее строки, содержащие символы
кириллицы. Сделайте копию базы данных утилитой
pg_dump. Восстановите таблицу из копии в базу
с кодировкой UTF8.
2. Получите номер сегодняшнего дня недели.
Меняется ли номер дня недели в зависимости от настроек
локализации?
1. Для создания БД в кодировке KOI8R:
●
убедитесь, что в ОС установлена нужная локаль;
●
в команде CREATE DATABASE используйте шаблон template0
и параметры ENCODING и LOCALE.
2. Для получения номера дня недели используйте функцию to_char.
Допустимые форматные маски даты:
