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

СЕРГЕЙ СУПРУНОВ

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

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

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

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

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

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

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

Существуют также различия в реализации некоторых 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 – базы данных, требующие высокой степени надежности хранения информации, предъявляющие повышенные требования к проверке всех изменений, имеющие необходимость в автоматической корректировке большого числа данных при изменении информации в одной из таблиц, а также задачи, где требуется возможность разработки нетривиальных решений, использование нестандартных операторов и т. д. Что, впрочем, не мешает использовать эту СУБД и для веб-приложений, например, форума или галереи изображений.

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

Для установки 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

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

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

$ psql -U pgsql template1

template1=# l

template1=# q

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

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

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

$ psql -U pgsql template1

template1=# create user test nocreatedb nocreateuser;

template1=# create database test owner test;

template1=# l

template1=# connect test test

test=# q

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

$ psql –U test test

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

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

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

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

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

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

Создаем таблицу с полями person, post и phone:

$ psql -U test test

test=# create table phbook(person varchar,

test-# post varchar, phone char(20));

Просмотр списка таблиц в базе:

test=# dt

Просмотр структуры таблицы:

test=# d phbook

Добавляем запись:

test=# insert into phbook(person, post, phone)

test-# values("Сергей Иванович", "директор", "(012) 345-67-89");

Смотрим, что получилось:

test=# select * from phbook;

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

test=# insert into phbook values("Васька", "друган", "222-22-22");

test=# select * from phbook;

Команда h позволяет получить подсказку по любой SQL-команде:

test=# h insert

Так можно просмотреть все команды интерактивной оболочки (здесь приведены наиболее часто используемые):

test=# ?

Просматривать можно не всю таблицу, а только ее часть, для чего условие выборки следует указать в предложении where:

test=# select person from phbook where phone="222-22-22";

Изменим поле записи. Обратите внимание, что если не указать условие where, то будут изменены соответствующие поля всех записей:

test=# update phbook set post = "лучший друг" where person = "Васька";

Удалим одну из записей. Отсутствие предложения where очистит всю таблицу:

test=# delete from phbook where post = "директор";

Наигравшись, удаляем таблицу phbook.

test=# drop table phbook;

Выходим:

test=# q

Вот так мы познакомились с интерактивным терминалом и убедились, что с 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[][]);

test=> insert into artest(archar, arint, doublear)

test-> values("{"один", "два"}", "{1,2,3}", "{{"1", "a"}, {"2", "b"}}");

test=> select doublear[1][2] from artest;

test=> update artest set arint[3]=5;

test=> insert into artest values("{"Q"}", "{123}", "{{}{}}");

test=> select * from artest where arint[1] > 2;

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

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

test=> h create sequence

test=> create sequence seqtest;

test=> ds

test=> select nextval("seqtest");

test=> select nextval("seqtest");

test=> select currval("seqtest");

test=> drop sequence seqtest;

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 – переименовываем новую.

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

Создаем родительскую таблицу (как обычно):

test=> create table mashines(ip inet, placement varchar, ostype varchar);

Добавляем в нее запись для рабочей станции (все операции – обычные):

test=> insert into mashines values("192.168.0.1", "Зал", "Windows 98");

Создаем дочернюю таблицу (предложение inherits с указанием родительских таблиц):

test=> create table servers(services varchar[]) inherits(mashines);

Хотя таблица servers создавалась с одним полем, три других она унаследовала от родительской:

test=> insert into servers values("192.168.0.254", "Серверная",

test-> "FreeBSD 5.2", "{"WWW", "FTP"}");

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

test=> select * from mashines;

В дочерней – только те, которые заносились непосредственно в нее:

test=> select * from servers;

Ключевое слово ONLY позволяет выбрать записи из родительской таблицы без учета содержимого дочерней:

test=> select * from only mashines;

Как видите, наследование позволяет создавать вполне логичные и удобные для работы структуры. Следует также заметить, что удалить базовую таблицу можно только со всеми производными (указав ключевое слово 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-команд. Ряд функций может быть выполнен внешними программами. Ниже представлена таблица, описывающая некоторые полезные команды:

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

Аналогично, команда 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.

Комментарии могут оставлять только зарегистрированные пользователи