SQL сервер PostgreSQL

PostgreSQL - это бесплатный и вместе с тем достаточно быстрый и мощный SQL сервер. Так как PostgreSQL распостраняется по лицензии General Public License (GPL) версии 2, то вам также предоставляются исходные тексты этой в вышей степени замечательной программы.

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

Пакеты в Debian как они есть

Начнем с того, что пакетов, посвященных PostgreSQL в Debian Linux не один. В то же время, это вполне закономерно и удобно. Действительно, если вам нужна только серверная часть, то зачем ставить все остальное?

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

Итак, перечисляю пакеты:

Пакет postgresql

В этом пакете содержиться все, что необходимо для настройки и запуска PostgreSQL. Если вы не планируете писать на C собственные программы для работы с PostgreSQL и не нуждаетесь в документации, то этот пакет - все что вам нужно. Вам нет необходимости ставить что-то еще.

Пройдемся по составу этого пакета

Каталог /etc/postgresql содержит следующие файлы:

В каталоге /var/postgres/data находится некоторое количество служебных файлов для PostgreSQL, а в каталоге /var/postgres/data/base размещаются базы данных, каждая в своем отдельном каталоге.

После установки этого пакета в /var/postgres/data/base будет автоматически создана база данных template1, а также выполнены практически все действия, которые необходимы для начала работы. Однако есть ряд настроек, которые вам просто наверняка понадобиться изменить. Остановимся на этом поподробнее.

Все эти настройки связаны с двумя файлами: /etc/postgresql/pg_hba.conf и /etc/postgresql/postmaster.init.

Начнем с postmaster.init.

Убедитесь, что вы root и откройте этот файл в любом текстовом редакторе.

Если вам нужно протоколирование и вывод отладочной информации, уберите комментарий со строки с переменной

POSTGRES_LOG /var/log/postgres.log

Установить нужный уровень отладочной информации вам поможет переменная

PGDEBUG

Если нужно, чтобы ваши запросы к базе данных также протоколировались, то уберите комментарий со строки

PGECHO

Стиль даты вам поможет установить переменная

PGDATESTYLE

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

PGALLOWTCPIP

Если вы хотите дать доступ к своему SQL серверу по сети через TCP/IP, то вам необходимо установить ее в "yes" иначе ничего не получиться. Кроме того, не забывайте после этого проверить как предоставлены права доступа с других компьютеров в файле pg_hba.conf.

Теперь поговорим о pg_hba.conf

Этот файл может содержать два вида записей:

1. Запись вида "host".
2. Запись типа "local".

Запись вида "host" выглядит следующим образом:

host <имя базы данных>  <маска адреса>
    <способ авторизации доступа> [аргумент для авторизации]

Здесь IP_адрес и маска_адреса это адрес компьютера в сети с его сетевой маской в виде "A.B.C.D". Используя маску адреса можно задать не один компьютер, а группу компьютеров. Например, при значениях 192.168.10.88 и 255.255.255.0 для IP-адреса и маски адреса соответственно, мы определяем весь диапазон адресов от 192.168.10.1 до 192.168.10.254 (учитывая, что 192.168.10.255 - это широковещательный адрес).

Параметр Способ авторизации доступа может принимать следующие значения:

Запись вида "local" выглядит следующим образом:

local <имя базы данных> <способ авторизации доступа>
     [аргумент для авторизации]

Эта запись определяет авторизацю доступа к базе данных локальных пользователей. Здесь практически все тоже самое, что и для "host", за исключением того, что IP-адрес и маска адреса опущены за ненадобностью.

На этом позвольте обзор пакета postgresql закончить.

Пакет libpgsql

Пакет содержит разделяемые (shared) библиотеки, которые использует клиентская утилита пользователя psql, а также саму эту утилиту.

Если вы планируете работать с PostgreSQL как с сервером с других машин, то утилита psql вам не нужна. Однако ее присутствие может оказаться полезным для выполнения некоторых функций администрирования пользователей и (или) баз данных.

Утилита psql обеспечивает работу с PostgreSQL с комадной строки. Вы можете подключаться к базам данных, выполнять запросы и любые другие команды языка SQL.

Пакет postgresql-doc

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

Документация представленна как в формает Postscript (.ps файлы) так и в формате HTML.

Пакет libpgtcl

Этот пакет вызывает у меня наибольшее умиление, как у человека, которому очень нравится язык Tcl.

Мне очень хотелось бы сказать огромное спасибо тем людям, которые сделали возможным столь легкий и красивый доступ к базам данным в PostgreSQL из этого языка. О том как это делается, я рассчитываю поговорить в отдельной статье позднее (см. обновления начальной странички).

Пока же отмечу наличие в этом пакете программы pgaccess, которая целиком написана на Tcl с использованием подгружаемой библиотеки libpgtcl.

С помощью этой программы, вы можете в X Window, пользуясь полной визуальностью и наглядностью создавать базы данных, таблицы, изменять их и даже вводить данные в полноэкранном режиме.

Замечание: Если вы планируете пользоваться программой pgaccess, то вы должны разрешить доступ к SQL-серверу по TCP/IP (см. postmaster.init выше)

Пакет libpgperl

Пакет содержит библиотеки для языка Perl, которые позволяют работать с PostgreSQL из программ Perl.

Я не являюсь большим любителем этого языка, по этому я ничего не смогу вам рассказать про то как это делается и насколько хорошо.

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

Microsoft Windows и PostgreSQL

Покольку PostgreSQL изначально писался для UNIX систем, то неудивительно, что до сих пор не существует версии для Microsoft Windows.

Впрочем, в документации к версии 6.5 есть упоминание, что для Windows NT портирование серверной части с использованием библиотеки Cygnus было недавно завершено. Сам я, правда, не смотрел и не пробовал, но если есть люди, у которых это работает, то прошу откликнуться и черкнуть пару строк.

Однако как для Windows95/98 так и для Windows NT с давних пор существовали ODBC и JDBC драйвера, при использовании которых можно было вполне сносно работать с сервером PostgreSQL, работающим на каком-либо UNIX'е. Далее речь пойдет об ODBC драйверах.

ODBC драйвера и их установка

Первый и наверняка наиболее серьезный вопрос состоит в наверное уже крылатой фразе: "А где возьмешь?". Что сказать вам по этому поводу? Взять можно во многих местах. В FAQ к PostgreSQL нас отсылают на http://www.openlinksw.com. Ну был я там, но ничего не понял. Где брать, как брать? Если у кого-то получилось, то напишите мне с подробной инструкцией для идиотов, чтобы ваша инструкция заменила то нытье, что я здесь пишу. А пока позвольте мне рассказать как я нашел ODBC драйвера.

Я пошел другим путем. А именно через поисковые сервера. Среди той кучи ссылок, что я получил на запрос "Postgres+ODBC" большая часть просто не работала, а подавляющее большинство остальных выдавало нечто совершенно древнее. Тем не менее я нашел кое-что, а именно файл postdrv.exe, который я положил сюда, дабы не заставлять вас тратить время понапрасну.

Это самораспаковывающийся архив в установкой через InstallShield. То есть все, что вам нужно сделать для установки, это скачать данных файл в какой-нибудь временный каталог, а затем запустить его. Однако, для того, чтобы начать работать, установки недостаточно. Вам еще необходимо произвести настройку ODBC через ODBC Administrator, который вызывается из окна Панель управления (Control Panel) под именем 32bit ODBC.

Перед тем как начать описание процесса настройки хочу сразу же сделать замечение. Эти ODBC драйвера не первой свежести (кто скажет, где найти свежее скажу спасибо). В настройках вы увидите, что поддерживаются протоколы только до версии PostgreSQL 6.4, хотя есть уже PostgreSQL 6.5. Однако, я опробовал эти драйверы и могу с уверенностью сказать, что с версией 6.5 они вполне работают.

Настройка OBDC драйверов

Итак, запускаем 32bit ODBC. В результате получаем диалоговое окно, следующего вида:

У вас в окне по всей видимости будет пусто. У меня, как видите уже установлен ODBC драйвер для Interbase. Нажимаем кнопку Add... и снова получаем диалоговое окно вида:

Выбираем "PostgreSQL" и нажимаем кнопку Готово. В результате имеем еще одно окно вида:

Поля Database, Server, Username и Password в вашем случае будут пустыми и вам необходимо заполнить их самостоятельно. Тут никаких трудностей возникнуть не должно: Database - база данных, с которой вы будете работать, Server - машина, на которой работает PostgreSQL, Username и Password - соответственно имя пользователя и пароль с использованием которых будет осуществляться подключение к базе данных (это значит, что на сервере с PostgreSQL у вас должен быть заведен пользователь именно с таким именем и паролем).

Параметр Port вам необходимо сменить только в том случае, если вы работаете с нестандартным портом (см. файл /etc/postgresql/postmaster.init на предмет изменения номера порта).

Как видно из рисунка, есть еще две кнопки Driver и DataSource.

При нажатии на кнопку Driver получаем окно вида:

В этом окне я рекомендую установить флажки Recognize Unique Indexes и Parse Statement. Если вам нужно протоколирование транзакций, то установите CommitLog(C:postodbc.log. Какие настройки включают остальные флажки - понятия не имею (если кто обьяснит, скажу спасибо).

При нажатии на кнопку DataSource получаем окно вида:

Сразу же рекомендую убрать флажек ReadOnly. А вот Protocol нужно установить в зависимости от версии PostgreSQL, с которой вы работаете. Я также включаю флажек Show Column, так как он не мешает. А вот для чего нужны остальные настройки я снова буду рад услышать от грамотных людей.

Финал

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

Замечания по Delphi

Эти два случая из моего опыта работы с PostgreSQL из Delphi.

Первую фичу я обнаружил на методе AppendRecord. Оказывается, чтобы данный метод отработал успешно, нужно первым элементом списка ставить nil, иначе, при выполнении возникнет ошибка. Т.е. теперь AppendRecord будет выглядеть, например так:

MyQuery.AppendRecord([nil, 1, 'Василий', 'Иванович', 'Чапаев']);

а не так, как делается, например, при работе с Interbase:

MyQuery.AppendRecord([1, 'Василий', 'Иванович', 'Чапаев']);

Вторая фича касается какой-то веселой ситуации, возникшей у меня один раз с сервером. По какой-то причине, тот отказывал в обслуживании клиентам. Похоже, это произошло потому, что в этот момент выполнялся какой-то процесс по обслуживанию базы данных на самом сервере.

Симптомом послужило то, что при обращении к PostgreSQL из программы на Deplhi, сама программа повесилась намертво. При этом курсор принимал на экране характерную форму при выполнении SQL запроса. Однако этот запрос все продолжал выполняться и продолжал... Через 5 минут мне это надоело и я снял задачу принудительно.

Чем было вызвано зависание программы, я даже не знаю. По идее, должен был отработать тайм-аут, по истечении которого должна была появиться ошибка, но этого не случилось. Может виновата кривость BDE, может ODBC драйвера.

Вылечилось просто - на сервере был перезапущен PostgreSQL.

Доступ к PostgreSQL из программ на языке Tcl

Что для этого необходимо?

Только лишь пакет libpgtcl, который содержит необходимую библиотеку libpgtcl.so.

Стандартный Tcl не содержит средств для доступа к PostgreSQL, но поскольку этот язык выполнен так, что может расширять свои возможности через подружаемые библиотеки, то наличие библиотеки libpgtcl.so - это все что нужно.

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

Несмотря на то, что весь набор операторов сводится к pg_connect, pg_exec, pg_select и pg_disconnect - это все что нужно.

• pg_connect - обеспечивает подключеник заданной базе данных
• pg_disconnect - обеспечивает отключение от базы данных
• pg_exec - предназначен для тех операторов SQL, которые не возвращают информацию
• pg_select - предназначен для тех операторов SQL, которые возвращают набор данных

pg_connect

Оператор имеет одну опцию: -conninfo, однако именно в этой опции, указываются все необходимые для подключения параметры, такие как host - машина, где работает сервер PostgreSQL, dbname - имя базы данных, к которой мы хотим подключиться, user - имя пользователя, с правами которого мы хотим осуществить подключение и password - его пароль.

Оператор возвращает значени уникального декскриптора базы, который затем используется всеми другими операторами, в случае успешного подключения. В случае неудачи, срабатывает исключение, которое можно обработать командой Tcl catch.

pg_disconnect

Все назначение оператора pg_disconnect состоит в закрытии сеанса соединения с базой данных. Для этого оператору нужен только один параметр - уникальный дискриптор, полученный в результате выполнения pg_connect.

pg_exec

Этот оператор необходим для выполнения таких операторов языка SQL, как INSERT, UPDATE, DELETE и т.д, словом всех операторов SQL, в результате выполнения которых не возвращается информация.

Для этого необходимы два параметра: уникальный декскриптор, полученный через pg_connect и строка, в которой записан оператор SQL. pg_select

Само название оператора предполагает, что он будет использоваться при выполнении запросов к базе данных с использованием оператора SELECT.

Оператор имеет четыре параметра:

1. Уникальный дескриптор, полученный через pg_connect
2. Строка, в которой расположен текст запроса на языке SQL
3. Имя переменной, которая будет играть роль массива-приемника информации
4. Команда, которая будет выполняться для каждой полученной записи из набора данных

Простой пример

В данном примере, несмотря на простоту присутствуют все вышеописанные операторы:

#!/bin/sh
# 
exec tclsh "$0" "$@"
# подгружаем библиотеку
load libpgtcl.so
# подготавливаем параметры для соединения
set host "server"
set dbname "traffic"
set user "victor"
set password "orel1"
# пытаемся подключиться к базе данных
set result [catch {set dbc [pg_connect -conninfo
         "host=$host dbname=$dbname user=$user password=$password"]}]
# если result не ноль, то выдаем ошибку
if {$result} {
  puts "Не удалось подключиться к базе данных. Проверьте параметры соединения"
  exit
}
# в противном случае, соединение удалось, а в переменной dbc находится
# уникальный дескриптор
# делаем запрос к базе данных на получение всей информации из таблицы clients_tbl
# при этом, названия полей (name, money, rang) выступают в виде индексов
# ассоциативного массива с именем answer
pg_select $dbc "SELECT * FROM clients_tbl" answer {
  puts "Клиент: $answer(name)"
  puts "Имеет денег: $answer(money)"
  puts "Его ранг: $answer(rang)"
}
# удаляем из таблицы всех клиентов, у которых денег меньше чем 1000
# Поскольку информация не возвращается, воспользуемя оператором pg_exec
pg_exec $dbc "DELETE FROM clients_tbl WHERE money<1000"
# Закрываем соединение с базой данных
pg_disconnect $dbc

Ограничения в PostgreSQL

PostgreSQL практически полностью удовлетворяет стандарту SQL92. Однако "практически полностью" не значит "целиком". Работа над PostgreSQL продолжается и не исключено, что последние отличия будут устранены в ближайший год, однако они есть. Список этих отличий (вероятно весьма неполный, дополнения приветствуются) я и постараюсь сейчас выдать:

• Отсутствует реализация внешнего ключа FOREIGN KEY. Пока рекомендуют применять триггеры, что позволяет как бы эмулировать внешний ключ, но увы по трудоемкости несравненно больше.
• Не работает ALTER TABLE COLUMN.
• Не работает INSERT INTO ... SELECT с AS.
• Не работает INSERT ... SELECT ... GROUP BY.
• Не работает UNION с LIMIT.
• Не работает CREATE TABLE ... AS SELECT ... UNION SELECT ...
• Не работает DISTINCT на курсорах (VIEWS).

Кроме того имеется несколько крупных и мелких багов, которые планируется устранить в ближайшем будущем. Более подробно обо все этом можно почитать в файле /usr/doc/postgresql-doc/TODO.gz, если установлена документация.

От автора

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

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

Автор: Виктор Вислобоков, victor_v@permonline.ru
Оригинал статьи находится на http://linux.perm.ru