Введение
Реляционные СУБД – это важные элементы многих веб-сайтов и приложений. Они позволяют удобно и структурированно хранить, организовывать и иметь доступ к информации.
PostgreSQL (или просто Postgres) – это реляционная СУБД, которая основывается на языке SQL. Это популярное решение для многих небольших и крупных проектов, так как сочетает в себе такие преимущества, как соответствие стандартам, эффективную сериализацию транзакций и многовариантное управление параллелизмом.
Из этого руководства вы узнаете, как установить PostgreSQL на сервер Ubuntu 16.04 и затем использовать эту СУБД.
Шаг 1: установка
Пакеты PostgreSQL есть в стандартных репозиториях Ubuntu, так что их можно установить при помощи утилиты apt.
Так как это первое использование apt в текущей сессии, необходимо обновить локальный индекс пакетов. А затем можно будет установить пакет PostgreSQL и еще пакет –contrib, который добавляет некоторые функции:
$ sudo apt-get update $ sudo apt-get install postgresql postgresql-contrib
Теперь, когда ПО установлено, можно перейти к ознакомлению с его работой, а также с различиями от других СУБД, с которыми вы, вероятно, работали.
Шаг 2: использование ролей и баз данных PostgreSQL
По умолчанию PostgreSQL использует концепцию под названием «роли» для работы с аутентификацией и авторизацией. Это в каком-то смысле похоже на стандартные Unix аккаунты, с тем различием, что PostgreSQL не делает различий между пользователями и группами, и поэтому здесь подходит более гибкий термин «роль».
После установки СУБД PostgreSQL настроена на использование идентичной аутентификации – то есть PostgreSQL получает имя пользователя Unix/Linux системы, которое будет использоваться в качестве разрешенного имени пользователя базы данных. Иными словами, если такое имя пользователя есть у пользователя Unix/Linux системы, то он сможет авторизоваться под этой ролью в PostgreSQL.
Есть несколько способов использовать этот аккаунт для получения доступа в PostgreSQL.
Переключение на PostgreSQL аккаунт
В процессе установки был создан пользовательский аккаунт postgres, который является стандартной ролью PostgreSQL. Для того, чтобы использовать PostgreSQL, нужно авторизоваться под этим аккаунтом.
Переключитесь на аккаунт postgres на сервере:
$ sudo -i -u postgres
Теперь нужно перейти в консоль PostgreSQL:
$ psql
После входа в консоль вы сможете взаимодействовать с СУБД.
Чтобы выйти из консоли, введите:
\q
Вы вернетесь в обычную консоль Linux.
Вход в консоль PostgreSQL без переключения аккаунтов
Еще один способ, как можно выполнять команды PostgreSQL, - это при помощи sudo.
Допустим, вы хотите попасть в консоль PostgreSQL. Это можно сделать, просто введя одну команду psql от имени пользователя postgres с sudo:
$ sudo -u postgres psql
Так вы сразу будете авторизованы в PostgreSQL без необходимо заходить в оболочку bash.
Выйти можно так же при помощи команды:
\q
Шаг 3: создание новой роли
Сейчас у вас есть роль postgres, настроенная внутри базы данных. Новые роли можно создавать в командной строке при помощи команды createrole. Использование ключа --interactive позволит вводить необходимые для системы значения.
Если вы авторизованы под аккаунтом postgres, введите следующую команду для того, чтобы создать нового пользователя:
$ createuser --interactive
Также можно использовать sudo, если вы не хотите переключаться со своего стандартного аккаунта. В этом случае нужно ввести:
$ sudo -u postgres createuser --interactive
После этого скрипт уточнит у вас некоторые данные, и в зависимости от того, что вы ответите, создаст требуемого пользователя.
Enter name of role to add: tommy Shall the new role be a superuser? (y/n) y
Существует несколько ключей, которые можно использовать с командой создания роли. Чтобы посмотреть их, наберите:
$ man createuser
Шаг 4: создание новой базы данных
По умолчанию система аутентификации PostgreSQL настроена так, что для каждой роли есть база данных с таким же именем, как и роль, которая используется для авторизации и получения доступа к этой базе данных.
Поэтому если ранее был создан пользователь под названием tommy, эта роль и будет использоваться для того, чтобы присоединиться к базе данных, которая по умолчанию будет тоже называться tommy. Создать эту базу данных можно при помощи команды createdb.
Если вы авторизованы под аккаунтом postgres, то введите:
$ createdb tommy
А без переключения аккаунтов можно ввести вот такую команду:
$ sudo -u postgres createdb tommy
Как открыть командную строку PostgreSQL с использованием новой роли
Для того, чтобы авторизоваться с использованием идентичной аутентификации, у вас должен быть пользователь Linux с таким же именем, как и роль и база данных PostgreSQL.
Если такого пользователя нет, его можно создать при помощи команды adduser:
$ sudo adduser tommy
Теперь, когда у вас есть подходящий аккаунт, вы можете либо переключиться на него и зайти в базу данных:
$ sudo -i -u tommy $ psql
Либо это можно сделать одной командой:
$ sudo -u tommy psql
Вы будете авторизованы автоматически, но при условии, что все компоненты были настроены правильно.
Для того, чтобы подключиться к другой базе данных, нужно указать конкретно ее название:
$ psql -d postgres
После авторизации можно проверить информацию о соединении при помощи команды:
$ \conninfo
В выводе будет:
You are connected to database "tommy" as user "tommy" via socket in "/var/run/postgresql" at port "5432".
Такая информация будет полезна в тех случаях, когда вы используете базы данных или пользователей, которые отличаются от заданных по умолчанию.
Шаг 5: создание и удаление таблиц
Теперь вы знаете, как подключиться к PostgreSQL, поэтому перейдем к базовым задачам, которые можно выполнять в базе данных.
Начнем с создания таблицы, в которой можно хранить какие-либо данные (в данном примере речь пойдет о детской площадке).
Базовый синтаксис команды выглядит вот так:
CREATE TABLE table_name ( column_name1 col_type (field_length) column_constraints, column_name2 col_type (field_length), column_name3 col_type (field_length) );
То есть вы должны дать таблице название, затем написать столбцы, их тип и максимальную длину значений. По желанию на значение каждого столбца можно сделать ограничения.
В качестве примера создадим вот такую таблицу:
CREATE TABLE playground ( equip_id serial PRIMARY KEY, type varchar (50) NOT NULL, color varchar (25) NOT NULL, location varchar(25) check (location in ('north', 'south', 'west', 'east', 'northeast', 'southeast', 'southwest', 'northwest')), install_date date );
Выше - таблица, которая содержит информацию об оборудовании детской площадки. Она начинается с ID оборудования; эти данные относятся к типу serial. Этот тип данных - автоинкрементное целое число. Этот столбец имеет значение первичного ключа - значит, значения должны быть уникальными и не должны равняться нулю.
Два столбца - equip_id и install_date - не имеют заданной длины поля. Некоторые столбцы могут не иметь заданной длины, так как она определяется типом данных этого столбца.
Затем есть столбцы для type и color, каждый их которых не может быть пустым. Наконец, есть столбец location, который может иметь одно из восьми заданных значений. Последний столбец - это дата, когда оборудование было установлено.
Посмотреть эту новую таблицу можно, если ввести:
$ \d
В выводе вы увидите:
List of relations Schema | Name | Type | Owner --------+-------------------------+----------+------- public | playground | table | tommy public | playground_equip_id_seq | sequence | tommy (2 rows)
Здесь вы можете заметить playground_equip_id_seq с типом sequence. Это реализация типа serial, который был дан столбцу equip_id. Это создается автоматически для столбцов такого типа и используется PostgreSQL для отслеживания следующего номера в последовательности идентификаторов.
Если вы хотите посмотреть таблицу без последовательности, введите:
$ \dt
Вывод:
List of relations Schema | Name | Type | Owner -------+------------+-------+------- public | playground | table | tommy (1 row)
Шаг 6: работа с данными в таблице
Как добавить, запросить и удалить данные в таблице
Теперь нужно научиться выполнять различные действия в созданной таблице.
Допустим, в таблицу необходимо добавить два новых элемента - slide (горка) и swing (качели). Для этого нужно выбрать и назвать таблицу, куда будут добавлены данные (playground), обозначить нужные столбцы и написать данные для каждого столбца. Например, вот так:
INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2014-04-28'); INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2010-08-16');
Здесь нужно обратить на пару важных моментов. Во-первых, название столбцов нужно писать без кавычек, а вот сами значения обязательно брать в кавычки.
Во-вторых, не нужно вводить значение для столбца equip_id, т.к. оно автоматически генерируется в тех случаях, когда в таблице появляется новая запись.
А теперь проверьте информацию, которую вы только что добавили:
SELECT * FROM playground;
Вы увидите:
equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 1 | slide | blue | south | 2014-04-28 2 | swing | yellow | northwest | 2010-08-16 (2 rows)
Тут сразу видно, что equip_id заполнено автоматически, и остальные данные тоже корректно добавлены в таблицу.
Теперь о том, как удалить данные.
Для того, чтобы убрать строку, нужно добавить следующее (в данном случае для удаления элемента slide):
DELETE FROM playground WHERE type = 'slide';
Если еще раз посмотреть таблицу, то видно, что этого элемента больше нет:
SELECT * FROM playground;
Вывод:
equip_id | type | color | location | install_date ----------+-------+--------+-----------+-------------- 2 | swing | yellow | northwest | 2010-08-16 (1 row)
Шаг 7: работа со столбцами в таблице
Как добавить и удалить столбцы в таблице
Если вы хотите изменить таблицу после того, как она была создана (например, добавить еще один столбец last_maint date), то сделать это можно вот таким образом:
ALTER TABLE playground ADD last_maint date;
Вам просто нужно указать название таблицы, действие и название столбца.
Если после этого вы посмотрите таблицу, то увидите, что новый столбец был добавлен, но никакой информации он пока не содержит.
SELECT * FROM playground;
Вывод:
equip_id | type | color | location | install_date | last_maint ----------+-------+--------+-----------+--------------+------------ 2 | swing | yellow | northwest | 2010-08-16 | (1 row)
Так же легко можно и удалять ненужные столбцы:
ALTER TABLE playground DROP last_maint;
Шаг 8: обновление данных в таблице
Выше вы научились добавлять и удалять данные, но бывают случаи, когда нужно изменить уже существующие данные.
Значения уже существующих данных можно изменить следующим образом: нужно ввести название записи и столбца, а также значение, которое вы хотите использовать. В примере ниже значение “color” у всех записей “swing” будет изменено на “red”:
UPDATE playground SET color = 'red' WHERE type = 'swing';
Проверьте, что изменение было сделано:
SELECT * FROM playground;
Вывод:
equip_id | type | color | location | install_date ----------+-------+-------+-----------+-------------- 2 | swing | red | northwest | 2010-08-16 (1 row)
Заключение
Теперь вы научились установке и работе c СУБД PosgreSQL, установленной на сервере с ОС Ubuntu 16.04. Конечно же, существует множество вещей, о которых не было рассказано в этой статье. Поэтому рекомендую обратиться к следующим ресурсам:
- Официальный сайт
- PostgreSQL Wiki: на английском, на русском
- Русскоязычный форум
Комментарии