PostgreSQL *

Я решил не затягивать выход обновления справочника и выпустил 2 версию «Работа с Postgresql: настройка, масштабирование».

Как и раньше, в книге иследуются вопросы по настройке производительности Postgresql, репликации и кластеризации.
Добавил пару слов о расширениях PostgreSQL, методики решения проблем, сниплеты(пока в процессе). Произвел коррекцию ошибок в манах настроек. Пока что только сконвертирован pdf. Все правки и замечания прошу на этой странице github.com/le0pard/postgresql_book/issues или в комментариях.

Страница книги: postgresql.leopard.in.ua
Исходники: github.com/le0pard/postgresql_book

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

В одном из докладов недавно проходившего CNews Forum я увидел «СУБД для предприятий» в списке ПО, которое навсегда останется проприетарным. Я не могу согласиться с таким тезисом, поэтому написал небольшую статью.

Привет всему хабросообществу.

Время не стоит на месте. После публикации моего справочника по Postgresql очень многое успело поменяться, а точнее добавиться в эту отличную СУБД. После выхода PostgreSQL 9 версии я понял, что потребуется добавить информацию о нововведениях для этой версии. Тем более, что 9 версия знаменуется выходом репликации из коробки.

Доброго времени суток. Учитывая, что с момента релиза PostgreSQL 9 прошло уже некоторое количество времени — я решил пощупать одну из его новых функций — нативную репликацию. Как известно, новый механизм основан на пересылке XLOG`a от мастера к слейву. Одним из жирных плюсов можно назвать нормальную обработку ALTER`ов. Иными словами — администратор 9й версии может обойтись без Slony.

Написал небольшой мануал по настройке и масштабированию PostgreSQL. Сподвигло меня на это то, что материала по этой теме мало, он устарел или его просто нет.

Скачать можно здесь:
http://github.com/le0pard/postgresql_book/raw/master/postgresql.pdf
Исходники:
http://github.com/le0pard/postgresql_book/

Страница:
http://postgresql.leopard.in.ua/

Начнем с определения того, что такое автономные транзакции в принципе:
Автономные транзакции позволяют создавать новые подтранзакции (subtransaction), которые могут сохранять или отменять изменения вне зависимости от родительской транзакции. Подробнее тут.

Реализованы они в СУБД Oracle, и являются очень гибким и удобным средством. Самым популярным (но далеко не единственным) использованием автономных транзакций является логирование. Рассмотрим несложный пример. Представьте себе следующую ситуацию — в вашей БД реализована длинная и сложная хранимая процедура, например ежемесячный биллинг. Важные события вы наверняка хотели бы видеть в таблице логов. Вы, конечно, можете просто писать в нее безо всяких ухищрений. Но есть один серьезный недостаток — если в процедуре происходит необработанная ошибка — транзакция откатывается вместе со всеми записями в таблицу логов. Тут на помощь и приходят автономные транзакции. Они выполняются независимо от родительской транзакции, и вы можете увидеть записи в таблице логов еще до того, как закончилось выполнение логгируемой процедуры биллинга.

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

Все уже наверняка обратили внимание, что в PostgreSQL 8.4 появился новый режим работы базы данных: Warm standby. При нём во время работы базы данных Write-Ahead логи (WAL) транслируются на подчинённую базу данных, на которой в реальном времени применяются, как если бы это происходило на основной системе. Поэтому, если основная база данных по какой-то причине (молния/торнадо/третья мировая война/другие стихийные бедствия) выйдет из строя, можно будет мгновенно переключиться на подчинённую базу данных (данные в которой будут достаточно актуальны по сравнению с основной базой) и использовать её дальше.
Но, к сожалению, «тёплый стэндбай» подразумевает, что на подчинённой системе непрерывно происходит процесс восстановления базы данных; из чего следует, что пока основная база жива, подчинённой базой пользоваться нельзя.
Если вы читаете хотя бы блог depesz (не говоря уже про коммит-логи), то вы уже знаете, к чему я веду; если же нет, то… 19-го декабря прошлого года в разрабатываемую версию PostgreSQL 8.5 была добавлена функциональность Hot standby. Теперь, при настройке репликации WAL, подчинённая база данных тоже может использоваться для запросов SELECT (и только SELECT, по понятным причинам). Если раньше второй сервер со второй базой данных простаивал в ожидании форс-мажора, и админу приходилось краснеть перед менеджерами при вопросах об эффективности использования оборудования — то теперь этот сервер, при правильном построении логики приложений, поможет разгрузить основную базу данных.
Подробности о функционировании Hot standby можно прочитать в соответствующей статье из документации разрабатываемой версии PostgreSQL.

Как-то по долгу службы появилась необходимость добавить к поиску на сайте всем известную фичу, сервис «Возможно вы имели в виду…» или «Поиск с опечатками». Стали думать как реализовывать. Сторонние сервисы и api использовать не хотелось, ибо время до чужого сервера и назад, да и в целом не очень хорошо. Как раз кстати пришелся модуль pg_trgm, который ищет близкие к запросу слову на основе триграммного индекса.

На домашнем ноуте стоит Виста. И я бы не сказала, чтобы она меня не устраивала, она мне нравится. Сейчас бы возможно и поставила Семерку, но Виста лицензионная и сносить ее не хочу. Но очень часто сталкиваюсь с проблемой установки всяких программок до сих пор.
Вот и сейчас столкнулась с ошибкой при установке последней версии PostgreSQL — «database cluster initialization failed».
Полазила по англоязычным форумам, потому что в русскоязычных ничего хорошего не нашла. Видимо, не так много извращенцев, которые хотят поставить Postgre на Висту))).
Вообщем, установила так — в процессе установки создается пользователь postgres. И как только он создался (установка еще идет), надо успеть до окончания установки дать этому пользователю полный доступ к папке, куда ставится postgree. ))) Поставить смогла только таким образом.

Возможно есть и другие способы, но я об этом незнаю.

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

Часть 1

Продолжаем разговор о секционировании. В прошлый раз мы рассмотрели простую задачу о таблице, разбитой на секции для каждого дня — так называемый range partitioning. Разбив таблицу на секции мы столкнулись с новой проблемой — поддержка секционированной таблицы. Мы должны вовремя создавать новую секцию, архивировать устаревшие, изменять соответствующим образом триггер, управляющий вставкой новых данных.

Часть 2

Добрый вечер/день/утро уважаемые хабралюди! Продолжаем развивать и дополнять блог о моей любимой open source rdbms Postgresql. Чудесным образом так получилось, что тема сегодняшнего топика еще ни разу здесь не подымалась. Надо сказать, что секционирование в postgresql очень хорошо описано в документации, но разве ж это меня остановит?).

Вслед за Ораклом со своим ‘connet by prior ‘ все остальные СУБД вводят свои реализации иерархических запросов (ИЗ). Хотелось бы рассказать широкой аудитории как это сделано в PostgreSQL.

Postgresql, без сомнения, великолепная СУБД. Она обладает обширнейшими возможностями, отличной документации, и при всем при этом является бесплатной. Однако, всегда найдется что-то, чего пользователю не будет хватать. И в postgresql это легко исправляется, ведь он позволяет создавать функции на языках на любой вкус, будь то Plpgsql, Perl или даже Java.

Приведу пример. Мне всегда не хватало функции, получающей DDL выбранной таблицы. В oracle, например, вы можете воспользоваться для этого средствами пакета dbms_metadata. А вот в postgresql аналога почему-то нет. То есть можно конечно использовать pgdump, но это уже немного не то, мне хотелось бы иметь функцию бд. И так далее, думаю у каждого найдется несколько таких небольших «хотелок».

В любой моей базе я создаю в схеме «public» определенный набор вот таких облегчающих мне жизнь функций. В этом топике я хочу поделиться ими. Приглашаю всех также поделиться в комментариях своими наработками.

В этом топике я хочу рассказать о задаче, очень часто встречающейся в веб-проектах — определение местоположения по ip-адресу. Начну с того, что для того, чтобы определить местоположение пользователя — нужна некая geoip база. Приведу здесь два популярных бесплатных варианта:
IpGeoBase — очень хорошая бесплатная база, но, к сожалению, только по российским ip-адресам.
MaxMind — огромная база по ip-адресам всех стран. Предоставляют бесплатную lite-версию базы. Точность базы по российским ip-адресам не настолько хороша, как у IpGeoBase. Также предоставляют некое API для работы со своей базой, которое позволяет производить выборки очень быстро.

Допустим вы скачали эти базы и залили их в таблицы вашей БД Postgresql (сама закачка — несколько out of scope, если у кого-то возникнет желание — я могу в будущем рассказать о том, что такое команда COPY и с чем ее едят). В общем случае вы получите таблицу такой структуры:

startip	endip	location_id
2130706433	2130706433	1

Здесь:
startip — это начало блока ip-адресов в формате long
endip — конец блока ip-адресов в формате long
location_id — идентификатор локации (город, регион, страна и т.д., maxmind даже координаты содержит).

Каким бы супер-пупер спецом вы не были — в процессе разработки эпизодически попадаются интересные и удобные вещи о которых, казалось бы, давно бы пора знать, но все никак не складывалось. Этакий ништячок, найдя который хочется воскликнуть: «Эврика!». Вот таким ништячком для меня стал элемент синтаксиса INSERT в PostgreSQL, которым я и хочу поделиться с вами.

Ситуация: После добавления записи в базу нужно получить id этой самой свеже-вставленной записи.
Решение на PostgreSQL: INSERT… RETURNING id где id — это PRIMARY_KEY в таблице.

Просто и элегантно, не правда ли?

На работе потребовалось разобраться с провести множество экспериментов с dblink.
Что бы не наступать по нескольку раз на одни и те же грабли, параллельно сделал перевод документации разбавив его своими комментариями.

Кому интересно, велкам: doc.prototypes.ru/database/postgresql/contrib/dblink

Родная документация тут: www.postgresql.org/docs/8.4/static/dblink.html

В MySQL можно делать INSERT… ON DUPLICATE KEY UDPATE. В постгресе пока что такого нет (насколько мне известно, MERGE не вошел в версию 8.4).

Я натыкался в сети на рекомендацию использовать правила для того чтобы добиться аналогичного поведения. Мне показалось, что использовать правила в данном случае даже лучше, нежели MERGE, я сделал правило… и наткнулся на подводный камень. Я реализовывал счетчик: есть уникальный ключ и, если такой ключ в таблице уже существует при вставке новой записи — следует вместо этого увеличить значение счетчика.

Простая ситуация, простое правило. Но: при первой вставке это правило, как оказалось, тоже отрабатывает! В результате, при первой вставке вставляется не 1, а 2!

Пока писал, Fred84 подкинул ссылку, где об этой ситуации подробнее рассказано: archives.postgresql.org/pgsql-bugs/2007-03/msg00080.php

Эти инструкции предполагают, что домашняя дерриктория pgsql расположена в /usr/local/pgsql, и данные находится в /usr/local/pgsql/data.

Если ваша конфигурация другая, замените соответствующие пути на свои. Внутренний формат хранения данных типично изменяется в каждом главном выпуске PostgreSQL. Поэтому, если Вы обновляете существующую установку, младше версии “8.3.x”, Вы должны сделать резервную копию, а затем восстановить данные. Если Вы обновляете PostgreSQL “8.3.x”, новая версия может использовать Ваши текущие файлы с данными, таким образом Вы сможете пропустить этапы резервного копирования и восстановления.

Задача

Как удобно делать выборки из деревьев типа Nested Sets, и как не удобно им управлять. Как удобноуправлять деревьями типа id->parent_id, но как не удобно и накладно использовать рекурсии при выборках. Понятно, что прииспользовании модулей для управления деревьями часть проблемы снимается, но при этом процесс работы с базой данных несовсем прозрачен т.е. для изменения данных мы используем одни методы, для изменения расположения узла в дереве — другие,плюс еще транзакции не помешали бы. Эту нестыковку можно решить двумя способами:

• Использовать для работы с таблицей хранимые процедуры, в которой объединить оба метода обновления (вставки, удаления);
• Использовать триггеры, для исключения вообще каких-либо нестандартных методов работы;

Первый способ неудобен тем, что при изменении структуры таблицы, нам потребуется еще изменять процедуру, а так же бытьмаксимально внимательным, при работе с таблицей, что бы все изменения данных проходили через наши процедуры, а не прямымизапросами. Второй способ несколько утяжеляет тяблицу введением дополнительных булевых полей, а так же приходится делатьнекоторые «финты ушами», хотя позволяет добиться максимальной прозрачности работы.Первый способ — в топку, тем более где-то интернетах уже есть подобное решение.База данных — PostgreSQL, как актуальная мне на данный момент, дополнения для MySQL напишу позже.