Введение

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

PostgreSQL или Postgres - это система управления реляционной базой данных, предоставляющая реализацию языка запросов SQL. Это популярный выбор для множества больших и малых проектов. Postgres также имеет преимущество соответствия стандартам (в том числе SQL), и, кроме того, обладает множеством продвинутых возможностей, например, надёжные транзакции (reliable transactions) и параллелизм без блокировки чтения (concurrency without read locks).

В этой статье мы продемонстрируем, как установить Postgres на виртуальный сервер с Ubuntu 16.04, а также осветим несколько простых сценариев его использования.

Перед установкой

Стандартные репозитории Ubuntu содержат пакеты Postgres, поэтому для установки мы будем использовать пакетный менеджер apt.

Сначала обновим локальный список пакетов. Далее мы установим пакет Postgres и пакет -contrib, который добавляет дополнительную функциональность:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

Использование ролей и баз данных PostgreSQL

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

После установки Postgres настраивает так называемую идентичную аутентификацию (ident authentication). Это означает, что Postgres ассоциирует свои роли с подходящими Unix/Linux аккаунтами. Если роль существует в Postgres, пользователь с этим же именем в Unix/Linux сможет осуществлять вход под этой ролью.

Существует несколько способов использовать это для доступа к Postgres.

Переключение на аккаунт postgres

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

Переключимся на использование postgres аккаунта на вашем сервере этой командой:

sudo -i -u postgres

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

psql

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

Для выхода из PostgreSQL напечатайте:

\q

После этого вы должны увидеть стандартное приглашение командной строки Linux (Linux command prompt).

Доступ к командной строке Postgres без переключения аккаунтов

Вы также можете выполнять команды postgres напрямую с помощью sudo.

Например, выше мы хотели получить доступ к интерфейсу командной строки Postgres. Мы можем сделать это одной строкой, используя команду psql от имени пользователя postgres с sudo:

sudo -u postgres psql

Это позволит вам войти в интерфейс командной строки Postgres без использования оболочки bash.

Для выхода из интерактивной сессии Postgres напечатайте:

\q

Создание новой роли

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

Если вы осуществили вход с помощью аккаунта postgres, вы можете создать нового пользователя напечатав команду:

createuser --interactive

Если вы предпочитаете использовать sudo перед вашими командами и не переключаться между пользователями, вы можете сделать это следующим образом:

sudo -u postgres createuser --interactive

Этот скрипт задаст вам несколько вопросов, и, в зависимости от ваших ответов, выполнит необходимые команды Postgres для создания желаемого аккаунта пользователя.

Вывод
Enter name of role to add: sammy
Shall the new role be a superuser? (y/n) y

Вы можете использовать дополнительные флаги при выполнении этой команды. Ознакомиться с полным списком флагов можно изучив страницу man:

man createuser

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

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

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

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

createdb sammy

Если вы предпочитаете использовать sudo перед вашими командами и не переключаться между пользователями, вы можете сделать это следующим образом:

sudo -u postgres createdb sammy

Запуск командной строки Postgres с новой ролью

Для использования идентичной аутентификации (ident authentication) вам необходимо иметь пользователя Linux с тем же именем, что и имя роли и базы данных Postgres.

Если у вас нет такого пользователя, вы можете создать его с помощью команды adduser. Для этого вам потребуется аккаунт с привилегиями sudo (вы не сможете использовать для этого пользователя postgres):

sudo adduser sammy

После создания пользователя вы можете сменить пользователя и зайти в Postgres командами:

sudo -i -u sammy
psql

Или вы можете сделать это следующим образом:

sudo -u sammy psql

Вы автоматически войдёте в Postgres, если все компоненты были сконфигурированы правильным образом (как описано выше).

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

psql -d postgres

После осуществления входа вы можете проверить информацию о текущем подключении командой:

Вывод
You are connected to database "sammy" as user "sammy" via socket in "/var/run/postgresql" at port "5432".

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

Создание и удаление таблиц

Теперь, когда вы знаете, как осуществлять доступ к СУБД Postgres, мы можем кратко рассмотреть вопрос выполнения некоторых распространённых задач в Postgres.

Для начала создадим таблицу для хранения данных. Давайте создадим таблицу, которая описывает параметры инвентаря детской площадки.

Базовый синтаксис для этой команды выглядит примерно так:

CREATE TABLE table_name (
    column_name1 col_type (field_length) column_constraints,
    column_name2 col_type (field_length),
    column_name3 col_type (field_length)
);

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

Вы можете более детально ознакомиться с процессом создания и управления таблицами Postgres в этой статье.

Для целей нашего примера мы создадим простую таблицу:

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
);

Мы создали таблицу для инвентаря детской площадки. Первый столбец содержит идентификатор инвентаря с типом serial. Это целочисленный тип данных с автоинкрементом. Мы задали этот столбец с параметром первичного ключа (primary key), что означает, что все значения должны быть ненулевыми и уникальными.

Для двух наших столбцов (equip_id и install_date) мы не задали максимальную длину значений. Некоторые типы столбцов не требует задания максимальной длины значения, потому что длина определяется типом данных в этом столбце.

Кроме того мы создали столбцы type и color, которые не могут быть пустыми. Мы также создали столбец location и настроили его таким образом, что его значение может принимать одно из восьми заданных значений. Последний столбец задаёт дату установки данной единицы инвентаря детской площадки.

Мы можем убедиться, что таблица создана, следующей командой:

\d

Вывод
                  List of relations
 Schema |          Name           |   Type   | Owner 
--------+-------------------------+----------+-------
 public | playground              | table    | sammy
 public | playground_equip_id_seq | sequence | sammy
(2 rows)

Наша таблица на месте, но помимо неё у нас теперь есть и нечто под названием playground_equip_id_seq с типом sequence. Это нечто представляет собой реализацию типа serial, который мы задали для столбца equip_id. Таким образом Postgres будет следить за следующим номером в последовательности идентификаторов, эта связь создаётся автоматически для столбцов данного типа.

Если вы хотите увидеть список только таблиц, без объектов-последовательностей, вы можете набрать следующую команду:

\dt

Вывод
          List of relations
 Schema |    Name    | Type  | Owner 
--------+------------+-------+-------
 public | playground | table | sammy
(1 row)

Добавление, запрос и удаление данных из таблицы

Теперь, когда у нас есть таблица, мы можем добавить в неё данные.

Сначала добавим горку (slide) и качели (swing). Для того, чтобы это сделать, нам необходимо указать имя таблицы, названия столбцов, а также сами данные:

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 было успешно заполнено автоматически, а остальные поля также получили корректные значения.

Если горка на нашей детской площадке сломалась и нам необходимо её убрать, мы можем удалить эту запись из нашей базы следующей командой:

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)

Как добавлять и удалять столбцы из таблицы

При необходимости мы можем легко добавить дополнительные столбцы и после создания таблицы.

Для примера добавим в нашу таблицу столбец, содержащий информацию о дате последнего осмотра единицы инвентаря детской площадки:

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;

Как обновлять данные в таблице

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

Вы можете обновлять значения существующих записей путём выборки необходимых записей и установки значений необходимых столбцов. Например, мы можем найти все записи для качелей (swing) и изменить их цвет на красный, потому что ранее мы покрасили все качели на нашей детской площадке в красный цвет:

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)

Как видите, теперь наши качели красного цвета.

Заключение

Теперь у вас есть установленный и настроенный PostgreSQL на вашем сервере с Ubuntu 16.04. Тем не менее, полученных нами знаний не достаточно для полноценной работы с Postgres. Мы рекомендуем ознакомиться с этими статьями о работе с данной СУБД:

• Сравнение систем управления реляционными базами данных
• Узнайте как создавать и управлять таблицами в Postgres
• Управляем ролями и полномочиями в Postgres
• Создание Select запросов в Postgres
• Делаем PostgreSQL безопаснее
• Делаем резервную копию базы данных Postgres