Самоучитель по SQL-сервер в Linux
         

Создание и удаление баз данных

В процессе установки PostgreSQL создаются два стандартных шаблона баз данных, tempi ateO и tempi atel, на основе которых создаются новые базы данных. Из этих двух шаблонов вы можете подключиться только к tempi atel. Это связано с тем, что шаблон tempi ateO всегда остается пустым, a tempi atel можно модифицировать и включать в него поддержку языков, функции и даже объекты баз данных (таблицы, представления и последовательности). Удаление шаблонов из системы не разрешается.
Далее описаны процедуры создания и удаления баз данных в PostgreSQL.

Создание базы данных

В PostgreSQL существует два способа создания новых баз данных: команда SQL CREATE DATABASE и программа createdb, работающая в режиме командной строки. Оба способа требуют наличия необходимых прав. Для создания базы данных не нужно быть суперпользователем PostgreSQL, но в таблице pg_shadow должно быть установлено право usecreatedb.

Если вы не уверены в том, обладает ли этим правом конкретный пользователь, для проверки можно воспользоваться запросом к представлению pgjjser (которое, в свою очередь, получает информацию из таблицы pg_shadow; прямые запросы




к pg_shadow разрешены только суперпользователям). Поле usecreatedb таблицы pg_shadow содержит логический признак наличия права создания таблиц у данного пользователя. В листинге 9.9 приведен пример запроса к представлению pg_user, проверяющего наличие права usecreatedb для пользователя guest.

Листинг 9.9. Проверка наличия права usecreatedb

tempiatel=> SELECT usecreatedb FROM pg_user WHERE usename='guest':

usecreatedb

f

(1 row)

Команда CREATE DATABASE

Команда SQL CREATE DATABASE имеет следующий синтаксис:

CREATE DATABASE база_дднных

[ WITH [ LOCATION = 'каталог' ]

[ TEMPLATE = шаблон ]

[ ENCODING = кодировка ] ]

Параметр база_данных определяет имя создаваемой базы данных. Имена баз данных должны начинаться с алфавитного символа, а их длина не должна превышать 31 символа. PostgreSQL позволяет создать в заданном каталоге любое количество баз данных (естественно, при наличии свободного места на диске).

За необязательным ключевым словом WITH можно указать до трех дополнительных атрибутов.

  • LOCATION = 'каталог'. В апострофах передается имя переменной среды, инициализированной в среде пользователя, запустившего серверный процесс PostgreSQL. Предположим, в файл /home/postgres/.bash_profile входит следующая строка:

export PGDATA2="/usr/local/pgsql/data2"

После запуска PostgreSQL (при наличии определенной переменной PGDATA2) переменная может использоваться в качестве значения параметра LOCATION. Эта общая мера безопасности предотвращает запись в посторонние каталоги файловой системы. Если ключевое слово LOCATION отсутствует, PostgreSQL создает базу данных в каталоге данных по умолчанию (например, /usr/local/pgsql/data).

  • TEMPLATE = шаблон. Шаблон, используемый в качестве прототипа для создания новой базы данных. Все объекты, присутствующие в шаблоне, воспроизводятся в созданной базе. Если шаблон не задан, PostgreSQL создает новую базу данных на основе шаблона tempi atel. Если вы хотите создать пустую базу данных, не содержащую объектов из tempi atel, укажите шаблон tempi ateO.
  • ENCODING = кодировка. Значение параметра определяется либо в виде строковой константы, описывающей тип кодировки (SQL_ASCII, LATIN1 и т. д.), либо в виде эквивалентной числовой константы PostgreSQL. Допустимые типы кодировок PostgreSQL и их числовые значения перечислены в приложении А. Если ключевое слово ENCODING не задано, PostgreSQL создает базу данных в кодировке по умолчанию. Обычно это кодировка SQL_ASCII, хотя в процессе первоначальной настройки конфигурации PostgreSQL можно выбрать другую кодировку (процедура выбора кодировки описана в главе 2).

ВНИМАНИЕ

Значение, передаваемое с ключевым словом LOCAi ION, должно определять имя переменной среды. Оно не может напрямую описывать системный путь (например, /usr/local/pgsql/data2), если только в процессе первоначальной компиляции и установки PostgreSQL команде gmake не был передан аргумент CPPFLAGS=-DALLOW_ABSOLUTE_DBPATHS.

Для выполнения команды CREATE DATABASE необходимо предварительно подключиться к базе данных. Если вы еще не создали ни одной базы данных, воспользуйтесь стандартным шаблоном tempi atel. Подключившись к этой базе данных, вы сможете создать новую базу для последующего подключения.

Создатель автоматически становится владельцем новой базы данных (также используется термин «администратор», или DBA — database administrator). Владельцу базы данных принадлежат все объекты, хранящиеся в базе, и он может предоставлять право доступа к ним другим пользователям. База данных должна создаваться пользователем, который в дальнейшем будет активно заниматься ее сопровождением.

В листинге 9.10 пользователь manager подключается к шаблону tempi atel и создает базу данных booktown. В примере использован клиент psql, но аналогичный синтаксис подходит для любого другого клиента PostgreSQt.

Листинг 9.10. Создание базы данных

[jworsley@booktown -]$ psql -U manager tempiatel

Welcome to psql. the PostgreSQL interactive terminal.

Type: \copyright for distribution terms

\h for help with SQL commands

\? for help on internal slash commands

\g or terminate with semicolon to execute query

\q to quit

tempiatel-# CREATE DATABASE booktown;

CREATE DATABASE

Сообщение CREATE DATABASE говорит о том, что база данных была создана успешно. Также встречаются сообщения об ошибках.

  • ERROR: CREATE DATABASE: permission denied. Пользователь пытается создать базу данных, не имея для этого необходимых прав. Право создания баз данных определяется состоянием поля usecreatedb таблицы pg_shadow, упоминавшейся ранее в этой главе. За дополнительной информацией о предоставлении этого права обращайтесь к главе 10.
  • ERROR: CREATE DATABASE: database "booktown" already exists. База данных с заданным именем (в данном примере — booktown) уже существует. PostgreSQL запрещает создавать базы данных с одинаковыми именами, даже если они находятся в разных физических каталогах файловой системы.

ПРИМЕЧАНИЕ

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

Приложение createdb

В PostgreSQL вместо команды CREATE DATABASE также можно воспользоваться приложением командной строки createdb. Единственное функциональное отличие createdb от команды SQL заключается в том, что программа запускается в режиме командной строки и позволяет включить комментарий в базу данных одновременно с ее созданием. В SQL для этого потребовалось бы две команды: CREATE DATABASE и COMMENT.

Синтаксис запуска программы createdb:

createdb [ параметры ] база_данных [ описание ]

Параметр база_данных определяет имя создаваемой базы данных. В строке параметры передаются любые из перечисленных ниже ключей, а описание содержит комментарий, включаемый в базу данных скрытой командой COMMENT (комментарии описаны в подразделе «Документирование базы данных» раздела «Сопровождение базы данных»).

Строка параметров может содержать либо ключи с дефисом, отделенные от значения пробелом (например, -D каталог), либо ключи в стиле GNU с двумя дефисами и знаком равенства, если он нужен (например, - -1оса11оп=католог). Ключи с одним дефисом всегда ограничиваются одной буквой, а ключи с двумя дефисами состоят из нескольких букв (обычно это целое слово).

Ниже перечислены ключи программы createdb.

  • -О каталог, --]осаИоп=каталог. Аналог ключевого слова LOCATION в команде CREATE DATABASE. В качестве значения ключа указывается имя переменной среды (установленной для пользователя, запустившего серверный процесс PostgreSQL), содержащей путь к каталогу, в котором размещаются файлы новой базы данных.
  • -Т шаблон, - -tempi ate=шaблoн. Аналог ключевого слова TEMPLATE в команде CREATE DATABASE. Ключ определяет идентификатор базы данных (например, tempi ateO), которая используется в качестве шаблона для новой базы с дублированием всех объектов.
  • -Е кодировка, - -епсоб1пд=кодировка. Аналог ключевого слова ENCODING в команде CREATE DATABASE. Ключ содержит строковую константу кодировки (см. приложение А). Числовые константы не могут передаваться программе createdb, хотя у команды CREATE DATABASE такая возможность имеется.
  • - h хост, - - host=xocm. Хост, с которым устанавливается связь для создания базы данных. По умолчанию используется имя local host или значение переменной среды PGHOST.
  • - р порт, - - рогЪ=порт. Порт, по которому должно производиться подключение к серверу, вместо порта по умолчанию (обычно 5432, хотя при компиляции PostgreSQL можно задать другой порт при помощи флага - -with-pgport).
  • -U пользователь, Имя пользователя, с которым производится подключение к PostgreSQL для создания базы данных (вместо имени системного пользователя, запустившего программу createdb).
  • -W, - -password. Ключ передается без параметров и обеспечивает запрос пароля у пользователя. Это происходит автоматически, если в файле pg_hba.conf хост, от которого поступил запрос, не объявлен доверенным.
  • -е, - -echo. Ключ передается без параметров. При включении вывода эха команда CREATE DATABASE, передаваемая PostgreSQL, выводится на экран в момент ее выполнения программой createdb.
  • -q, - -quiet. Ключ передается без параметров и запрещает вывод данных в стандартный поток stdout (хотя ошибки по-прежнему направляются в стандартный поток stderr).

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

В листинге 9.11 приведен пример использования программы createdb. База данных example создается пользователем manager в каталоге, заданном переменной PGDATA2. Обратите внимание на смешанное использование двух разновидностей ключей (с одним дефисом и в стиле GNU).

Листинг 9.11. Использование приложения createdb

[jworsley@booktown -]$ createdb --location=PGDATA2 -U manager example

CREATE DATABASE

Удаление базы данных

По аналогии с созданием баз данных в PostgreSQL предусмотрено два способа удаления базы данных из системы: команда SQL DROP DATABASE и программа dropdb. При удалении базы данных в таблице pg_shadow для соответствующего пользователя должно быть установлено право usecreatedb.

ВНИМАНИЕ

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

Команда DROP DATABASE

Синтаксис команды SQL DROP DATABASE: DROP DATABASE база_данных

Параметр команды DROP DATABASE определяет имя базы данных, удаляемой из системы. Учтите, что удаление базы данных возможно лишь при отсутствии подключившихся пользователей, в противном случае команда завершается неудачей. В листинге 9.12 приведен пример удаления базы данных с именем example.

Листинг 9.12. Команда DROP DATABASE

tempiatel=# DROP DATABASE example;

DROP DATABASE

Сообщение сервера DROP DATABASE говорит о том, что база данных успешно удалена, а соответствующие системные файлы уничтожены. Также встречаются сообщения об ошибках.

  • ERROR: DROP DATABASE: cannot be executed on the currently open database. Вы подключены к базе данных, которую пытаетесь удалить. Прежде чем удалять базу данных, необходимо отключиться от нее.
  • ERROR: DROP DATABASE: database "example" Is being accessed by other users. Кбазе данных, которую вы пытаетесь удалить, подключены другие пользователи. Подождите, пока они не завершат работу с базой данных.
  • ERROR: DROP DATABASE: database "example" does not exist. База данных с указанным именем (в данном примере — база данных example) не существует.

Приложение dropdb

Для команды SQL DROP DATABASE также существует вспомогательное приложение dropdb, работающее в режиме командной строки. Единственное отличие между двумя способами заключается в том, что при запуске приложения из командного интерпретатора можно установить флаг, по которому приложение предлагает подтвердить удаление базы данных.

Синтаксис запуска сценария dropdb:

dropdb [ параметры ] база_данных

Параметр 6аза_данных определяет имя удаляемой базы данных. В строке параметры передаются ключи приложения. Большинство ключей dropdb описывает параметры подключения к PostgreSQL и совпадает с ключами, описанными выше в пункте «Команда createdb» подраздела «Создание базы данных». Добавился всего один новый ключ — -1 или --interactive.

Ниже приведен полный список ключей приложения dropdb.

  • - h хост, -- host=xocm. Хост, с которым устанавливается связь для удаления базы данных. По умолчанию используется имя local host или значение переменной среды PGHOST.
  • -р порт, --port=nopm. Порт, по которому должно производиться подключение к серверу, вместо порта по умолчанию (обычно 5432, хотя при компиляции PostgreSQL можно задать другой порт при помощи флага - -with-pgport).
  • -U пользователь, --цзегтте^пользователь. Имя пользователя, с которым производится подключение к PostgreSQL для удаления базы данных (вместо имени системного пользователя, запустившего программу dropdb).
  • -W, - -password. Ключ передается без параметров и обеспечивает запрос пароля у пользователя. Это происходит автоматически, если в файле pg_hba.conf хост, от которого поступил запрос, не объявлен доверенным.
  • -i, --interactive. Ключ передается без параметров. Перед уничтожением данных пользователю предлагается подтвердить свое решение.
  • -е, - -echo. Ключ передается без параметров. При включении вывода эха команда DROP DATABASE, передаваемая PostgreSQL, выводится на экран в момент ее выполнения программой dropdb.
  • -q, - -quiet. Ключ передается без параметров и запрещает вывод данных в стандартный поток stdout (хотя ошибки по-прежнему направляются в стандартный поток stderr).

Команду dropdb всегда рекомендуется выполнять с флагом - i, поскольку это дает возможность лишний раз подумать перед удалением информации из PostgreSQL.

В листинге 9.13 приведен пример удаления базы данных example пользователем manager с ключом -1.

Листинг 9.13. Использование команды dropdb

[jworsley@booktown -]$ dropdb -U manager -1 example

Database "example" will be permanently deleted.

Are you sure? (y/n) у

DROP DATABASE




Содержание раздела