Реинициализация кластера баз данных PostgreSQL

    Предмет описываемой проблемы



    При работе с базой данных в PostgreSQL необходимо не забывать, в какой локали (locale) был инициализирован кластер БД — так в постгре называется директория (обычно /var/lib/pgsql/data), в которой хранятся данные всех баз этой установки PostgreSQL.



    Проблема



    Сегодня я столкнулся с такой проблемой. В запросе выборки при использовании функции lower() приведение кириллического текста к нижнему регистру не происходило, при этом английские значения охотно «уменьшались».

    Первая попытка решить проблему



    Гугло-поиск по полвине Интернета дал информацию о том, что неплохо было бы, если бы искомая база данных была в кодировке UTF-8 (в моем случае, по недосмотру, она была в дефолтной SQL_ASCII).

    Ок. Сказано — сделано! Относительно быстро нашлась инструкция о том, как пересоздать базу данных в новой кодировке без потери данных.

    [bash]
    # su - postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydatabase
    ~ pg_dump mydatabase -Ft -v -U postgres -f /tmp/mydatabase.tar
    ~ dropdb mydatabase --username postgres
    ~ createdb --encoding UNICODE mydatabase --username postgres
    ~ pg_restore /tmp/mydatabase.tar | psql --dbname mydatabase --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydatabase
    


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

    Решение проблемы становится интересным



    Вместе с продолженным гугло-чтением пришло понимание того, что кластер баз данных этого PostgreSQL сервера был инициализирован в локали «C», а для функий lower() и upper() это значит очень многое!

    Пришлось выяснить, как пере-инициализировать кластер баз данных, не погубив при этом уже существующие базы и данные в них. При этом, сервер этот является production — на него по крону (crontab) раз в час сливаются кое-какие дампы данных. Благо то, что он не столько продакшн, что не нашлось бы свободного «окна» для пере-инициализации.

    «Окно» в 60 минут и полчаса на подготовку



    До конца рабочего дня оставалось 1,5 часа и одно свободное «окошко» продолжительностью в 60 минут.

    Начинать я решил с разминки на локальном ноуте. Здесь стоит упомянуть о разнице в операционных системах: ноут — Ubuntu 8.10, сервер — CentOS 5. Подготовив три окна терминала и ещё окно текстового редактора, приступил к подготовительным работам.

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

    Дамп баз состоялся без сильных нареканий, только пару раз pg_drop ругнулся на подключенных пользователей к тем же базам (решилось закрытием pgAdmin'а).

    Затем была найдена (в случае с Убунтой) спрятанная в достаточно необычном месте (/usr/lib/postgresql/8.3/bin) команда (initdb) и выполнена в нужными параметрами.

    [bash]
    # su - postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydatabase
    ~ pg_dump mydatabase -Ft -v -U postgres -f /tmp/mydatabase.tar
    ~ dropdb mydatabase --username postgres
    ~ initdb --locale=ru_RU.utf8 data/
    
    


    Damn! Error...

    Мда, оказалось, что нужно вручную удалить содержимое директории кластера баз данных.

    Предупреждение! Не делайте сразу rm -rf data/*. Это я понял после того, как сделал это на ноуте, а после восстановления, у меня сбились права доступа пользователей к серверу (которые хранятся в pg_hba.conf).


    Необходимо сделать копию файла pg_hba.conf куда-нибудь на время перемен.

    [bash]
    ~ cp data/pg_hba.conf /home/cr0t/pg_hba.2009.03.24_1654.conf
    


    После удаления содержимого директории и остановки демона PostgreSQL без ошибки прошла ре-инициализация кластера.

    [bash]
    ~ exit
    # /etc/init.d/postgresql stop
    # su - postgres
    ~ rm -rf data/*
    ~ initdb --locale=ru_RU.utf8 data/
    


    Оставалось только запустить заново сервер и восстановить из дампов старые базы в уже новый кластер, инициализированный в «правильной» локали.

    [bash]
    ~ exit
    # /etc/init.d/postgresql start
    # su - postgres
    ~ createdb --encoding UNICODE mydatabase --username postgres
    ~ pg_restore /tmp/mydatabase.tar | psql --dbname mydatabase --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydatabase
    
    


    Успех. Итоги



    После этих успешных действий функция lower() стала правильно «прижимать» кириллические символы. Все рады. Но я даже не подумал о пользовательских ролях PostgreSQL (так в 8.х версии стали называться пользователи). Их не стало. Хорошо, что мне требовалось создать их всего парочку. Но у кого их много, будьте бдительны, не повторите моей ошибки!

    P.S. Шаги для ре-инициализации, если есть несколько баз данных



    В моём случае необходимо было задампить и впоследствии восстановить 3 базы.
    Для решения этой задачи необходимо добавить всего лишь дополнительные повторы некоторых действий по снятию дампа и последующему восстановлению (при большом желании, даже автоматизирующий скрипт можно написать ;)).

    [bash]
    # su - postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb1
    ~ pg_dump mydb1 -Ft -v -U postgres -f /tmp/mydb1.tar
    ~ dropdb mydb1 --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb2
    ~ pg_dump mydb2 -Ft -v -U postgres -f /tmp/mydb2.tar
    ~ dropdb mydb2 --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb3
    ~ pg_dump mydb3 -Ft -v -U postgres -f /tmp/mydb3.tar
    ~ dropdb mydb3 --username postgres
    ~ cp data/pg_hba.conf ./
    ~ exit
    # /etc/init.d/postgresql stop
    # su - postgres
    ~ rm -rf data/*
    ~ initdb --locale=ru_RU.utf8 data/
    ~ cp pg_hba.conf data/
    ~ exit
    # /etc/init.d/postgresql start
    # su - postgres
    ~ createdb --encoding UNICODE mydb1 --username postgres
    ~ pg_restore /tmp/mydb1.tar | psql --dbname mydb1 --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb1
    ~ createdb --encoding UNICODE mydb2 --username postgres
    ~ pg_restore /tmp/mydb2.tar | psql --dbname mydb2 --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb2
    ~ createdb --encoding UNICODE mydb3 --username postgres
    ~ pg_restore /tmp/mydb3.tar | psql --dbname mydb3 --username postgres
    ~ vacuumdb --full --analyze --username postgres --dbname mydb3
    


    Кросс-пост с моего блога Summer code

    Пост похож на уже опубликованный недавно Патчим UTF-8 Collation под FreeBSD, но мне кажется, что там описано решение проблемы специфичное для FreeBSD, я же привожу для Ubuntu'ы. Когда я решал свою проблему этой информацией даже не пользовался — только гугло-чтение.

    Спасибо за карму!, перенёс в блог PostgreSQL.
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 11

      0
      su postrges
      и не пришлось бы каждый раз писать --username postgres
        +1
        Почему бы не использовать pg_dumpall и избежать кучи команд и проблем с пользователями?
          0
          Согласен.
          Достаточно сделать так:
          1. pg_dumpall
          2. Массовая замена кодировок в командах create database в дампе (только client encoding нужно оставить как есть)
          3. Создание нового кластера с правильной кодировкой
          4. Восстановление из дампа (тут и роли, и права, и все базы сразу)
            0
            Спасибо за наводку на pg_dumpall! Я даже об этой команде не вспомнил. Возьму на заметку, потому что, как я и написал в посте, с ролями после восстановления возникли проблемы.
          +1
          Не понятно зачем делать vacuum --full, на больших базах это может длиться бесконечно долго. Особого смысла в этом перед pg_dump'ом нет. После — тоже не понятно зачем, хотя думаю после оно отработает относительно быстро
          • НЛО прилетело и опубликовало эту надпись здесь
            0
            что-то я не пойму о каком кластере идет речь? кластер это набор из нескольких серверов БД объедененных и работающих как одно целое, а тут вроде только один сервер БД…
              0
              кластер БД — так в постгре называется директория (обычно /var/lib/pgsql/data), в которой хранятся данные всех баз этой установки PostgreSQL
              В самом начале поста.
                0
                блин, как-то я вообще первый абзац проскочил. сорри
              +1
              Технически — очень грамотно, организационно — полный бардак. На продакшене, за час до конца рабочего дня, без полноценного теста («ой, роли полетели! А, ладно: их немного...»)
              Талантливый рас#$дяй.
                0
                update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'the_db';

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое