Amsand: PostgreSQL - первые шаги

Оригинальный адрес статьи: http:// amsand.narod.ru / articles / psql.html

PostgreSQL - первые шаги

Опубликована в журнале "Системный администратор", Июль 2004

Сейчас довольно трудно представить себе более или менее серьезный программный проект, который не использовал бы базу данных для хранения информации. В данной статье я хочу рассмотреть одну из лучших (на мой взгляд) систем управления базами данных, распространяемых бесплатно - PostgreSQL. Существуют версии этой СУБД как для различных Unix-систем, так и для Windows. Учитывая, что разрабатывается она прежде всего для работы в среде Unix, то рассмотрим ее установку, первичную настройку и основные принципы использования на примере ОС FreeBSD.

Несколько общих фраз:

Все примеры в данной статье приведены для FreeBSD 5.2 и PostgreSQL 7.4.2, однако в принципиальном отношении все изложенное остается верным и для других актуальных сегодня операционных систем и версий. Статья носит обзорный характер и предназначена, прежде всего, администраторам, желающим познакомиться с этой СУБД и оценить ее возможности для решения тех или иных задач. Предполагается, что читатель знаком с такими вещами как реляционная база данных, СУБД, а также с базовыми понятиями и операторами языка SQL.

PostgreSQL - это объектно-реляционная система управления базами данных, работающая как клиент-серверная система. Основываясь на базовых понятиях реляционных БД, PostgreSQL поддерживает и ряд "объектных" операций, например, наследование. PostgreSQL соответствует базовой спецификации SQL99 и поддерживает большое число возможностей, описанных стандартом SQL92.

Может возникнуть вопрос - почему именно PostgreSQL? Лучше всего ответить на него, сравнив этот продукт с не менее популярным и, пожалуй, более известным - MySQL. Сразу оговорюсь, что я не собираюсь доказывать, что один из них лучше другого - это несколько разные продукты, области применения которых во многом пересекаются, но отнюдь не совпадают, и сравнением я хочу определить нишу PostgreSQL, те категории задач, для которых эта СУБД более эффективна.

Прежде всего, следует отметить различия в организации данных. Если в MySQL каждая таблица заносится в собственный файл (для большинства типов БД), то PostgreSQL организует единую файловую структуру, в которой отдельные файлы не соответствуют непосредственно таблицам или другим объектам базы данных. То есть в MySQL Вы можете создать резервную копию лишь части БД, сохранив соответствующие файлы. Для PostgreSQL такой номер пройдет только для всей структуры, включающей в себя все обслуживаемые сервером БД базы. Как следствие - MySQL полагается при организации БД на файловую систему ОС, в то время как PostgreSQL зависит от нее меньше, но требует дополнительного обслуживания - периодической дефрагментации базы данных командой VACUUM.

Далее, эти две СУБД используют различную реализацию нескольких подключений. PostgreSQL имеет более широкие возможности, как для обработки данных, так и для администрирования, но это, в свою очередь, несколько повышает сложность работы с этой СУБД. Наиболее характерные различия сведены в таблицу, после которой приведены некоторые комментарии-выводы.

Характеристика PostgreSQL MySQL

Необходимость профилактики Да (VACUUM) Нет

Поддержка транзакций Да Для некоторых типов БД

Представления пользователя (views) Да Нет

Реализация нескольких подключений Процессы Потоки

Набор пользовательских привилегий Стандартный Расширенный

Количество типов БД Один Несколько

Организация данных Единая файловая структура Каждая таблица в своем файле

Блокировка таблиц операциями записи Нет Да

Пользовательские функции Да Сложно

Пользовательские типы данных Да Нет

Наследование таблиц Да Нет

Характеристика	PostgreSQL	MySQL
Необходимость профилактики	Да (VACUUM)	Нет
Поддержка транзакций	Да	Для некоторых типов БД
Представления пользователя (views)	Да	Нет
Реализация нескольких подключений	Процессы	Потоки
Набор пользовательских привилегий	Стандартный	Расширенный
Количество типов БД	Один	Несколько
Организация данных	Единая файловая структура	Каждая таблица в своем файле
Блокировка таблиц операциями записи	Нет	Да
Пользовательские функции	Да	Сложно
Пользовательские типы данных	Да	Нет
Наследование таблиц	Да	Нет

Существуют также различия в реализации некоторых SQL-функций (PostgreSQL лучше соответствует стандартам), в наборе типов данных и т.д. Как видно из таблицы, разработчики MySQL делают акцент на наилучшую скорость чтения (выборки) данных, чем и объясняется популярность этой СУБД в среде веб-разработчиков, где выборка - основная операция. Достигается это отсутствием транзакций (они реализованы только для некоторых типов таблиц, например InnoDB, BerkleyDB) и многопоточной работой, однако это же и является причиной несколько меньшей надежности данной СУБД. В плане прав доступа MySQL несколько превосходит PostgreSQL, позволяя задавать права доступа не только на уровне таблицы, но и на уровне столбца, однако в PostgreSQL это с лихвой компенсируется возможностью создавать пользовательские представления ("view").

Наиболее острый вопрос, вызывающий нескончаемые споры - сравнение скоростных характеристик этих двух пакетов. Результаты тестов, доступные в Интернете, весьма противоречивы, что говорит об отсутствии явного лидерства той или иной СУБД. Кроме того, результаты будут сильно зависеть от конкретной реализации теста, преобладания тех или иных операций и нагрузки на систему. В одних случаях первенство будет за MySQL, в других - за PostgreSQL.

Также PostgreSQL часто сравнивают с коммерческой СУБД Oracle. Oracle несколько превосходит PostgreSQL в таких вопросах как использование индексов, репликация и восстановление данных, да и вообще инструменты администрирования Oracle более развиты (но вместе с тем и более сложны). С другой стороны, PostgreSQL предоставляет возможность использовать в качестве процедурного языка помимо PL/pgSQL (очень схожего с PL/SQL, используемым в Oralce), также PL/Perl, PL/Python, PL/Tcl, что позволяет разработчику выбрать более привычный инструмент.

Таким образом, можно сказать, что место PostgreSQL - базы данных, требующие высокой степени надежности хранения информации, предъявляющие повышенные требования к проверке всех изменений, имеющие необходимость в автоматической корректировке большого числа данных при изменении информации в одной из таблиц, а также задачи, где требуется возможность разработки нетривиальных решений, использование нестандартных операторов и т.д. Что, впрочем, не мешает использовать эту СУБД и для web-приложений, например, форума или галереи изображений.

Типовая установка:

Для установки PostgreSQL во FreeBSD лучше всего воспользоваться системой портов (/usr/ports/databases/postgresql7 или другая актуальная версия). Однако в данном случае установка по умолчанию, скорее всего, вас не устроит, поскольку в ней отсутствует поддержка расширенных кодировок, а, следовательно, операции сортировки данных, хранимых в кириллической кодировке (например, KOI8-r), будут работать неправильно.

Прежде всего, нужно собрать PostgreSQL с поддержкой расширенных кодировок. Для этого добавьте в файл Makefile, размещенный в директории порта, следующие строки после первого упоминания переменной CONFIGURE_ARGS:

CONFIGURE_ARGS+= --enable-locale \
				--enable-multibyte=KOI8 \
				--enable-recode

Параметр --enable-locale необходим для поддержки специфических для страны настроек (так называемой локали), --enable-recode включает поддержку перекодировки (данный параметр для описываемого варианта, в котором везде используется только KOI8-r, необязателен, но зачастую может быть весьма полезен). Параметр --enable-multibyte собственно и включает поддержку кодировки KOI8-R. Если Вы предпочитаете работать с другой кодовой таблицей, укажите ее в качестве аргумента. Параметр, заданный без аргумента, включит поддержку всех мультибайтовых кодировок.

Таким же образом Вы можете добавить другие нужные Вам опции или изменить поведение СУБД по умолчанию. Например, изменить номер порта, который PostgreSQL будет прослушивать в ожидании запросов, можно с помощью параметра --with-pgport=NUM (по умолчанию - 5432). А ключ --with-perl позволит в дальнейшем использовать процедурный язык PL/Perl для разработки хранимых процедур в дополнение к PL/pgSQL. Для получения информации по всем ключам запустите configure --help.

После того, как в Makefile внесены все нужные параметры конфигурации, установка выполняется как обычно:

	# make && make install

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

	# make -DWITH_TESTS

И затем, убедившись в успешности тестирования, выполнить установку:

	# make install

Если Ваша система не поддерживает коллекцию портов или Вы предпочитаете ставить все вручную, скачав предварительно архив с исходниками, то после распаковки Вам нужно будет выполнить конфигурацию с приведенными выше параметрами:

./configure 	--enable-locale \
		--enable-multibyte=KOI8 \
		--enable-recode ...

Ну и любителям прекомпилированных RPM-пакетов нужно будет найти пакет, собранный с требуемыми опциями.

Теперь для инициализации базы данных нужно выполнить следующую команду:

	# su -l pgsql -c "initdb -E KOI8"

Обратите внимание, что инициализацию БД нужно выполнять с правами того пользователя, от имени которого процесс будет в дальнейшем запускаться, и это должен быть не root. Если Вы ставили PostgreSQL из коллекции портов, то для этих целей автоматически будет создан пользователь pgsql, чье имя мы и указываем в команде su как аргумент ключа -l. Если же Вы собирали систему самостоятельно, то этого пользователя Вам придется создать вручную и позаботиться о правах доступа к нужным исполнимым файлам, библиотекам и каталогам данных.

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

В процессе инициализации в папке /usr/local/pgsql будет создан каталог data со структурами двух БД - template0 и template1. Изменить путь к БД можно с помощью ключа -D команды initdb:

	# su -l pgsql -c "initdb -E KOI8 -D /var/databases/postgres/data"

По умолчанию для FreeBSD установка исполнимых файлов происходит в папку /usr/local/bin. Если Вы изменили это и новый путь не включен в переменную PATH, то потребуется указать полный путь к файлу initdb.

По окончанию инициализации Вам будет предложено два способа запуска процесса postmaster, который является сервером СУБД:

	$ /usr/local/bin/postmaster -D /usr/local/pgsql/data

или

	$ /usr/local/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start

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

Такой режим работы может быть удобен на стадии отладки, но для реальной работы он явно неудобен. Можно, конечно, запустить его в фоновом режиме, указав в конце команды знак амперсанда (&), но мне больше нравится управлять процессом postmaster с помощью специально для этого разработанной утилиты pg_ctl.

В обоих вариантах ключ -D позволяет задать каталог, в котором хранятся данные. Каталог должен быть инициализирован командой initdb, как описывалось ранее. В принципе, ничто не мешает инициализировать несколько каталогов данных, и запустить одновременно несколько процессов postmaster (при этом также нужно позаботиться о том, чтобы каждый из них прослушивал свой порт). Один процесс может одновременно обслуживать только один каталог данных.

Теперь для проверки, что все работает так, как ожидается, присоединитесь к базе template1 следующей командой:

	$ psql -U pgsql template1

Если Вы получили приветствие Интерактивного Терминала PostgreSQL, значит, БД проиниализировалась и сервер запущен правильно. А если это сообщение еще и на русском языке, то вообще замечательно. Если же нет, то убедитесь, что переменная окружения LANG соответствует выбранной кодировке:

	$ echo $LANG
	ru_RU.KOI8-R

В случае возникновения проблем проанализируйте полученные сообщения об ошибках, поступающие на экран и в журнальные файлы (в зависимости от характера ошибки это могут быть файлы /var/log/pgsql, /var/log/messages и т.п.). Убедитесь, что Вы запускаете PostgreSQL от имени созданного ранее пользователя pgsql (впрочем, имя может быть иным, например postgres). Запустите процесс postmaster в интерактивном режиме, как было описано выше. Если анализ диагностических сообщений не позволяет выявить источник проблем, может помочь возврат на стадию инсталляции и выполнение регрессионных тестов.

Итак, мы подключились к БД, интерактивный клиент общается с нами на нашем родном (или, по крайней мере, на очень знакомом) языке. Еще одна вещь, какую желательно дополнительно проверить - кодировка, с которой БД была инициализирована. Для этого в терминале выполните команду \l, которая выводит список всех БД:

$ psql -U pgsql template1
Добро пожаловать в psql 7.4.2 - Интерактивный Терминал PostgreSQL.

Наберите: \copyright для условий распространения
    \h для подсказки по SQL командам
    \? для подсказки по внутренним slash-командам (\команда)
    \g или ";" для завершения и выполнения запроса
    \q для выхода

template1=# \l
Список баз данных
Имя       | Владелец | Кодировка 
----------+----------+-----------
template0 | pgsql    | KOI8
template1 | pgsql    | KOI8
(записей: 2)

template1=# \q

Поскольку у нас все работает, как и ожидалось, то теперь можно позаботиться об автоматической загрузке сервера PostgreSQL при старте системы. Если Вы ставили СУБД из коллекции портов, то беспокоиться Вам не о чем - в папке /usr/local/etc/rc.d уже есть файл 010.pgsql.sh, который и будет отвечать за запуск и останов сервера СУБД. Если же установка выполнялась вручную, то Вам нужно будет перенести в соответствующий каталог (/etc/rc.d, /usr/local/etc/rc.d) сценарий автозапуска, соответствующий Вашей операционной системе, из подкаталога contrib/start-scripts той папки, куда был распакован дистрибутив.

Что еще важно иметь в виду: пользователь pgsql является администратором СУБД, то есть его полномочия по отношению к базам PostgreSQL практически ничем не ограничены.

Кроме того, настоятельно не рекомендуется использовать базы template0 и template1 для работы. Поэтому первое, что нужно сейчас сделать, это создать новую базу данных и нового пользователя, который будет являться владельцем этой БД.

Для выполнения этих операций, как и всех остальных, будем использовать терминал psql:

$ psql -U pgsql template1
template1=# create user test nocreatedb nocreateuser;
CREATE USER
template1=# create database test owner test;
CREATE DATABASE
template1=# \l
Список баз данных
Имя        | Владелец | Кодировка 
-----------+----------+-----------
template0  | pgsql    | KOI8
template1  | pgsql    | KOI8
test       | test     | KOI8
(записей: 3)

template1=# \connect test test
Вы теперь подсоединены к базе данных "test" как пользователь "test".
test=# \q
$

Теперь у нас есть база данных, с которой в дальнейшем и будем экспериментировать. Обратите внимание, что нового пользователя мы создавали с опциями nocreatedb и nocreateuser, то есть он не будет иметь прав на создание баз и пользователей. В дальнейшем следует подключаться под этим пользователем к вновь созданной базе test:

	$ psql -U test test

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

Хотя это и достаточно очевидно, все же следует указать, что пользователи СУБД и системные пользователи - не одно и то же.

Наверняка Вас удивило, что при подключении к БД не указывается пароль. Действительно, настройка PostgreSQL по умолчанию позволяет пользователям подключаться с хоста localhost без ввода пароля, поскольку этот хост определен как доверенный (trust). Если Вы - единственный пользователь, имеющий терминальный доступ к серверу (например, PostgreSQL установлена на Вашей машине для изучения), то это можно не менять. Но поскольку никогда нельзя быть уверенным, что злоумышленник не получит доступ к командной оболочке, то лучше не пренебрегать дополнительной защитой.

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

Пример простой базы данных (основам SQL посвящается...)

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

$ psql -U test test
Добро пожаловать в psql 7.4.2 - Интерактивный Терминал PostgreSQL.

Наберите: 	\copyright для условий распространения
\h для подсказки по SQL командам
\? для подсказки по внутренним slash-командам (\команда)
\g или ";" для завершения и выполнения запроса
\q для выхода

--создаем таблицу с полями person, post и phone
test=# create table phbook(person varchar, 
test-# post varchar, phone char(20));
CREATE TABLE

--просмотр списка таблиц в базе
test=# \dt
Список связей
Схема   | Имя        | Тип     | Владелец 
--------+------------+---------+----------
public  | phbook     | таблица | test
(1 запись)

--просмотр структуры таблицы
test=# \d phbook
Таблица "public.phbook"
Колонка  | Тип               | Модификаторы 
---------+-------------------+--------------
person   | character varying | 
post     | character varying | 
phone    | character(20)     | 

--добавляем запись
test=# insert into phbook(person, post, phone)
test-# values('Сергей Иванович', 'директор', '(012) 345-67-89');
INSERT 18752 1

--смотрим, что получилось
test=# select * from phbook;
person           | post     | phone 
-----------------+----------+----------------------
Сергей Иванович  | директор | (012) 345-67-89 
(1 запись)

--добавляем еще одну запись. Как видно, после имени таблицы
--не обязательно перечислять заполняемые поля. В этом случае
--поля будут заполняться значениями по порядку
test=# insert into phbook values('Васька', 'друган', '222-22-22');
INSERT 18753 1
test=# select * from phbook;
person           | post     | phone 
-----------------+----------+----------------------
Сергей Иванович  | директор | (012) 345-67-89 
Васька           | друган   | 222-22-22 
(записей: 2)

--команда \h позволяет получить подсказку по любой SQL-команде
test=# \h insert
Команда: INSERT
Описание: создать запись в таблице
Синтаксис:
INSERT INTO таблица [ ( колонка [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { выражение | DEFAULT } [, ...] ) | запрос }

--а так можно просмотреть все команды интерактивной оболочки
--(здесь приведены лишь наиболее часто используемые):
test=# \?
\c[onnect] [ИМЯБД|- [ПОЛЬЗОВАТЕЛЬ]]
             подсоединиться к новой базе данных (текущая: "test")
\h [ИМЯ] подсказка по синтаксису SQL команд; * для всех команд
\q выйти из psql
\timing переключить режим замера запросов (в данный момент: выкл.)

\d [ИМЯ] описать таблицу, индекс, последовательность или вид
\d{t|i|s|v|S} (добавьте "+" для более детальной информации)
    показать таблицы/индексы/последовательности/виды/системные таблицы
\du показать пользователей
\l показать все базы данных (добавьте "+" для более детальной информации)

--просматривать можно не всю таблицу, а только ее часть,
--для чего условие выборки следует указать в предложении where
test=# select person from phbook where phone='222-22-22';
person 
--------
Васька
(1 запись)

--изменим поле записи. Обратите внимание, что если не указать условие
--where, то будут изменены соответствующие поля всех записей
test=# update phbook set post = 'лучший друг' where person = 'Васька';
UPDATE 1
test=# select * from phbook;
person           | post        | phone 
-----------------+-------------+----------------------
Сергей Иванович  | директор    | (012) 345-67-89 
Васька           | лучший друг | 222-22-22 
(записей: 2)

--и удалим одну из записей. Отсутствие предложения where очистит 
--всю таблицу
test=# delete from phbook where post = 'директор';
DELETE 1
test=# select * from phbook;
person  | post        | phone 
--------+-------------+----------------------
Васька  | лучший друг | 222-22-22 
(1 запись)

--наигравшись, удаляем таблицу phbook...
test=# drop table phbook;
DROP TABLE
test=# \dt
Связей не обнаружено.

--и выходим
test=# \q
$

psql.html

Вот так мы познакомились с интерактивным терминалом и убедились, что с PostgreSQL вполне можно общаться на нормальном, человеческом языке SQL. Обратите внимание на синтаксис приглашения. Оно состоит из имени базы данных (в нашем случае - test). Далее следует символ "=", если редактируется новая строка; "-", если продолжается ввод команды, начатый на предыдущей строке; или "(", если продолжается ввод и в предложении имеется незакрытая скобка. Завершает приглашение символ "#", если текущий пользователь является владельцем подключенной БД, или ">" в противном случае. Каждая вводимая SQL-команда должна завершаться точкой с запятой. Для команд терминала это не требуется.

Специфика PostgreSQL...

Теперь более подробно рассмотрим конкретные особенности PostgreSQL. Было бы жестоко пытаться втиснуть весь материал в одну журнальную статью, поэтому этот раздел будет носить скорее обзорный характер. А наиболее интересным особенностям постараюсь посвятить несколько отдельных статей.

Сперва - о типах данных. Помимо стандартных, соответствующих SLQ99 и SQL92 (numeric, char, varchar, bool и т.д.), поддерживается и ряд специфических, например, геометрические типы данных (point, line, box, circle, polygon), сетевые типы (cidr - спецификация сети IP, inet - IP-адрес, macaddr - MAC-адрес) и т.д. Конечно, дополнительные типы данных не являются незаменимыми, но в ряде случаев существенно упрощают логику и структуру базы. Например, геометрические типы могут оказаться весьма полезными при разработке картографических приложений.

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

Будучи объектно-реляционной СУБД, PostgreSQL поддерживает и составные типы данных - массивы. Чтобы определить поле как массив, его тип дополняется парой квадратных скобок (например, "char(10)[]"). Массив (как константа) записывается с помощью фигурных скобок: "'{1,2,3}'", "'{"один", "два", "три", "четыре"}'". Обращение к элементу массива, как и в других языках программирования, выполняется по индексу: arra[2]. Пример:

test=> create table artest(archar char(10)[], 
test-> arint int[], doublear varchar[][]);
CREATE TABLE
test=> \d artest
            Таблица "public.artest"
 Колонка  |         Тип         | Модификаторы 
----------+---------------------+--------------
 archar   | character(10)[]     | 
 arint    | integer[]           | 
 doublear | character varying[] | 

test=> insert into artest(archar, arint, doublear) 
test-> values('{"один", "два"}', '{1,2,3}', '{{"1", "a"}, {"2", "b"}}');
INSERT 18960 1
test=> select * from artest;
           archar            |  arint  |   doublear    
-----------------------------+---------+---------------
 {"один      ","два       "} | {1,2,3} | {{1,a},{2,b}}
(1 запись)

test=> select archar[1] from artest;
   archar   
------------
 один      
(1 запись)

test=> select doublear[1][2] from artest;
 doublear 
----------
 a
(1 запись)

test=> update artest set arint[3]=5;
UPDATE 1
test=> select * from artest;
           archar            |  arint  |   doublear    
-----------------------------+---------+---------------
 {"один      ","два       "} | {1,2,5} | {{1,a},{2,b}}
(1 запись)

test=> insert into artest values('{"Q"}', '{123}', '{{}{}}');
INSERT 18961 1
test=> select * from artest;
           archar            |  arint  |   doublear    
-----------------------------+---------+---------------
 {"один      ","два       "} | {1,2,5} | {{1,a},{2,b}}
 {"Q         "}              | {123}   | {}
(записей: 2)

test=> select * from artest where arint[1] > 2;
     archar     | arint | doublear 
----------------+-------+----------
 {"Q         "} | {123} | {}
(1 запись)

Как видно, первый элемент массива имеет индекс "1". С точки зрения PostgreSQL, массив представляет собой специально форматированную строку, и именно этим обусловлена необходимость заключать запись массива в апострофы независимо от типа его элементов. Для массива строк каждый элемент также должен быть заключен в двойные кавычки. Очевидно, что скорость обработки массивов оставляет желать лучшего, однако в ряде случаев возможность работать с этим типом может оказаться весьма удобной.

Теперь про обещанные ранее последовательности. Последовательность (sequence) представляет собой счетчик, то есть автоматически увеличивающееся целое число. При вызове специальной функции nextval('seq_name'), где seq_name - имя последовательности, значение последовательности увеличивается на шаг, заданный при ее создании (по умолчанию - 1), и возвращается полученное значение. Прочитать это значение еще раз без увеличения последовательности можно с помощью функции currval('seq_name'). Следующий листинг демонстрирует основные принципы работы с последовательностью:

test=> \h create sequence
Команда: CREATE SEQUENCE
Описание: создать генератор последовательностей
Синтаксис:
CREATE [ TEMPORARY | TEMP ] SEQUENCE имя [ INCREMENT [ BY ] шаг ]
[ MINVALUE минимум | NO MINVALUE ] [ MAXVALUE максимум | NO MAXVALUE ]
[ START [ WITH ] начало ] [ CACHE кэш ] [ [ NO ] CYCLE ]

test=> create sequence seqtest;
CREATE SEQUENCE
test=> \ds
Список связей
Схема   | Имя     | Тип                | Владелец 
--------+---------+--------------------+----------
public  | seqtest | последовательность | test
 (1 запись)

test=> select nextval('seqtest');
nextval 
---------
1
(1 запись)

test=> select nextval('seqtest');
nextval 
---------
2
(1 запись)

test=> select currval('seqtest');
currval 
---------
2
(1 запись)

test=> drop sequence seqtest;
DROP SEQUENCE

PostgreSQL имеет довольно развитые возможности работы с индексами. Создаются они командой CREATE INDEX, синтаксис которой можно посмотреть в терминале командой "\h create index". В ряде случаев индексы создаются автоматически (например, для полей с ограничением PRIMARY KEY). Созданные пользователем индексы можно просмотреть командой \di. Поддерживается три типа индексов: B-tree (по умолчанию), R-tree и хэш. В большинстве случаев следует использовать B-деревья, хотя иногда (например, при операциях с геометрическими типами данных) R-tree-индекс может показать лучшие результаты. В любом случае выбор остается за пользователем.

Модифицировать таблицы можно с помощью команды ALTER TABLE. Однако нужно заметить, что она не позволяет удалять столбцы или менять их тип, что связано с особенностями организации базы данных. В большинстве случаев достаточно переименовать ненужный столбец во что-нибудь типа "deleted" и забыть про него. Если же очень хочется удалить его физически, то воспользуйтесь командой CREATE TABLE AS, которая создаст новую таблицу на базе существующей (или даже нескольких, поскольку источником данных может быть любой запрос). Кроме того, всегда можно воспользоваться "универсальным алгоритмом":

CREATE TABLE newtable .. - создаем новую таблицу с нужной структурой;
INSERT INTO newtable SELECT .. FROM oldtable - вставляем в новую таблицу нужные поля записей из старой;
DROP TABLE oldtable - удаляем старую таблицу;
ALTER TABLE newtable RENAME TO oldtable - переименовываем новую.

Очень интересная возможность, имеющаяся в PostgreSQL - наследование таблиц. Если таблица создается как производная (дочерняя) от некоторой базовой (родительской), то она наследует структуру последней, то есть в ней, помимо собственных полей, доступны и поля родительской таблицы, а записи дочерней можно прочитать из родительской. В качестве примера рассмотрим такую задачу: требуется БД для учета имеющихся на предприятии компьютеров (IP-адрес машины, место установки, операционная система), при этом для серверов дополнительно требуется указать работающие на них службы (WWW, FTP, DNS и т.д.). В классической реляционной СУБД это решалось бы созданием отдельной таблицы для хранения информации по службам и привязкой ее к таблице машин по IP либо по дополнительному полю-идентификатору. Объектные возможности PostgreSQL позволяют решить указанную задачу следующим образом:

--создаем родительскую таблицу (как обычно)
test=> create table mashines(ip inet, placement varchar, ostype varchar);
CREATE TABLE

--добавляем в нее запись для рабочей станции (все операции - обычные)
test=> insert into mashines values('192.168.0.1', 'Зал', 'Windows 98');
INSERT 18985 1
test=> select * from mashines;
     ip      |     placement     |   ostype   
-------------+-------------------+------------
 192.168.0.1 | Зал               | Windows 98
(1 запись)

--создаем дочернюю таблицу (предложение inherits с указанием 
--родительских таблиц)
test=> create table servers(services varchar[]) inherits(mashines);
CREATE TABLE

--хотя таблица servers создавалась с одним полем, три других
--она унаследовала от родительской:
test=> insert into servers values('192.168.0.254', 'Серверная', 
test-> 'FreeBSD 5.2', '{"WWW", "FTP"}');
INSERT 18991 1

--в родительской таблице видим записи и из родительской, и из дочерней,
--но без учета полей, заданных только в дочеренй:
test=> select * from mashines;
      ip       |     placement     |   ostype    
---------------+-------------------+-------------
 192.168.0.1   | Зал               | Windows 98
 192.168.0.254 | Серверная         | FreeBSD 5.2
(записей: 2)

--в дочерней - только те, которые заносились непосредственно в нее
test=> select * from servers;
      ip       | placement |   ostype    | services  
---------------+-----------+-------------+-----------
 192.168.0.254 | Серверная | FreeBSD 5.2 | {WWW,FTP}
(1 запись)

--ключевое слово ONLY позволяет выбрать записи из родительской
--таблицы без учета содержимого дочерней
test=> select * from only mashines;
     ip      |     placement     |   ostype   
-------------+-------------------+------------
 192.168.0.1 | Зал               | Windows 98
(1 запись)

Как видите, наследование позволяет создавать вполне логичные и удобные для работы структуры. Следует также заметить, что удалить базовую таблицу можно только со всеми производными (указав ключевое слово CASCADE в команде DROP TABLE).

PostgreSQL позволяет создавать собственные функции, которые сохраняются в базе данных и могут быть использованы при модификации полей записи, в операциях выборки по условию, как триггеры, и т.д. Разработчику, помимо процедурного языка PL/pgSQL (являющегося аналогом PL/SQL в СУБД Oracle), доступны PL/Perl, PL/Python и др. Для этого PostgreSQL должна собираться с соответствующими опциями.

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

Следующая особенность СУБД - возможность создавать представления пользователя (view), которые представляют собой результаты выборки (запроса), доступные для непосредственного обращения к ним в будущем без пересылки повторных запросов. Помимо упрощения логики работы с БД (когда вместо сложных запросов к нескольким таблицам можно отправить весьма простой запрос к представлению пользователя), эта возможность позволяет гибко управлять доступностью данных для различных пользователей. Например, если в БД есть таблица, содержащая имена и пароли клиентов, но оператор должен получить доступ только к именам, то достаточно создать представление, в которое выбрать поле "Имя", и дать оператору доступ к нему, а возможность чтения самой таблицы исключить.

Ну и напоследок - несколько слов о транзакциях. Этот механизм позволяет выполнять несколько операций по модификации БД единым блоком, что гарантирует целостность данных. Например, если при оформлении покупки через Интернет-магазин нужно занести запись о покупке и одновременно уменьшить количество соответствующих товаров на складе, то в результате сбоя может сложиться ситуация, когда запись о покупке будет сделана, а товар - не зарезервирован. В ходе же транзакции изменения в базу данных записываются только в случае успешности всех операций.

Транзакционный блок открывается командой BEGIN (не забывайте завершать эту команду, как и все остальные, точкой с запятой). Все изменения, выполняемые в блоке, не отражаются на базе данных, пока не поступит команда COMMIT, по которой выполняется запись всех сделанных изменений. Отменить транзакцию (например, в случае выявления ошибки) позволяет команда ROLLBACK. Если Вы недостаточно хорошо представляете, как это работает, попробуйте поработать с таблицами внутри транзакционного блока, анализируя изменения, происходящие при подтверждении транзакции (COMMIT) и ее откате (ROLLBACK). Обратите внимание, что такие операции, как удаление таблицы, внутри транзакции выполнены быть не могут.

Вопросы администрирования:

В начале статьи упоминалось, что локальные пользователи компьютера, на котором запущен сервер postmaster, могут подключаться к БД под именем любого пользователя без указания пароля. Связано это с тем, что хост localhost по умолчанию является доверенным. Изменить это можно в файле pg_hba.conf, находящемся в каталоге data, инициированном командой initdb.

Синтаксис этого файла хорошо прокомментирован в нем самом. Поле TYPE содержит тип записи, который может быть одним из следующих: local (соединение, устанавливаемое с того же компьютера, на котором работает сервер СУБД; использует сокеты Unix), host (соединение TCP/IP) или hostssl (защищенное соединение TCP/IP с использованием протокола SSL).

Поле METHOD может содержать следующие записи:

trust - доверенный хост, подключения не требуют пароля;
reject - отклонить соединение;
password - требовать соответствия пароля (передается в открытом виде);
crypt - требовать соответствия пароля (пароль шифруется);
krb4, krb5 - аутентификация Kerberos;
ident - аутентификация по карте соответствия имени пользователя PostgreSQL системному имени пользователя. Карты соответствия содержатся в файле pg_ident.conf.

Назначение остальных полей файла pg_hba.conf пояснений, думаю, не требует. При запросе соединения проверка условий выполняется с начала файла до обнаружения соответствия, после чего выполняется действие, заданное полем METHOD. Если соответствие не будет найдено, соединение не будет установлено.

Следует заметить, что после внесения изменений в этот файл требуется перезапустить процесс postmaster, послав ему сигнал SIGHUP, либо перезагрузить сервер СУБД командой:

	# pg_ctl reload

Чтобы сервер мог обслуживать подключения по протоколу TCP/IP, процесс postmaster должен быть запущен с ключом -i. Для SSL-соединений также требуется ключ -l, и, кроме того, СУБД должна быть собрана с поддержкой SSL (ключ --with-openssl).

Управление базой данных может осуществляться с помощью SQL-команд. Ряд функций может быть выполнен внешними программами. Ниже представлена таблица, описывающая некоторые полезные команды:
SQL-команда Имя программы Описание

create database createdb Создание новой базы данных

alter database -- Изменение параметров БД

drop database dropdb Удаление базы данных

create user createuser Создание нового пользователя

alter user -- Изменение параметров пользователя (в т.ч. пароля)

drop user dropuser Удаление пользователя

create group -- Создать группу пользователей

alter group -- Добавление / удаление пользователя в группу

drop group -- Удалить группу

vacuum vacuumdb Дефрагментация БД

grant -- Назначение привилегий

revoke -- Отмена привилегий

-- pg_dump Архивирование БД

-- pg_restore Восстановление БД из архива

SQL-команда	Имя программы	Описание
create database	createdb	Создание новой базы данных
alter database	--	Изменение параметров БД
drop database	dropdb	Удаление базы данных
create user	createuser	Создание нового пользователя
alter user	--	Изменение параметров пользователя (в т.ч. пароля)
drop user	dropuser	Удаление пользователя
create group	--	Создать группу пользователей
alter group	--	Добавление / удаление пользователя в группу
drop group	--	Удалить группу
vacuum	vacuumdb	Дефрагментация БД
grant	--	Назначение привилегий
revoke	--	Отмена привилегий
--	pg_dump	Архивирование БД
--	pg_restore	Восстановление БД из архива

Некоторого пояснения требуют команды назначения и отмены привилегий (GRANT и REVOKE). Команда GRANT позволяет давать пользователю или группе следующие права (перечислены только основные):

Команда Описание

grant select Доступ на чтение (выборку)

grant insert Право добавлять записи

grant delete
grant update Право на удаление и изменение

grant all Все полномочия

Команда	Описание
grant select	Доступ на чтение (выборку)
grant insert	Право добавлять записи
grant delete grant update	Право на удаление и изменение
grant all	Все полномочия

Аналогично, команда REVOKE ограничивает соответствующие права. В качестве объекта, для работы с которым назначаются права, могут выступать таблицы, представления пользователя и последовательности. Посмотреть назначенные привилегии доступа позволяет команда терминала \z.

При необходимости программного доступа к информации о пользователях и привилегиях весьма полезны служебные таблицы pg_shadow (таблица пользователей) и pg_class (права доступа к объектам БД).

Для тех, кто клавиатуре предпочитает мышь, разработан пакет phpPgAdmin (аналог phpMyAdmin для MySQL), позволяющий работать с СУБД через веб-интерфейс. Для FreeBSD найти его можно в коллекции портов: /usr/ports/databases/phppgadmin.

Также существует мультиплатформенная графическая оболочка pgAdmin (www.pgadmin.org/pgadmin3), обеспечивающая доступ ко всем функциям администрирования и разработки.

Заключение

Итак, были рассмотрены основные особенности СУБД PostgreSQL. Описание получилось несколько расплывчатым и отрывочным, но надеюсь, эта статья все же позволит Вам оценить возможности этой системы управления базами данных и принять принципиальное решение о ее использовании (или неиспользовании - вольному воля) для решения стоящих перед Вами задач.

За дополнительной информацией можно обращаться к традиционному "подножному корму": страницам справочного руководства man (см. каталог /usr/local/pgsql/man/man1 (программы "обвязки") и man7 (справки по командам SQL)), встроенной справке интерактивного терминала (\h - справочник по SQL-командам, \? - справочник по командам терминала), Интернету (официальный сайт проекта - www.postgresql.org, один из русскоязычных сайтов - www.linuxshare.ru/docs/software/SQL/postgresql).