Установка и использование PostgreSQL в Ubuntu 16.04

Реляционные базы данных являются одним из основных компонентов большинства сайтов и приложений. Они необходимы для структурирования и хранения данных.

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

Данное руководство поможет установить PostgreSQL на виртуальный выделенный сервер Ubuntu 16.04; также оно ознакомит с основами работы с данной СУБД.

Установка PostgreSQL

Пакет PostgreSQL можно загрузить из официального репозитория Ubuntu с помощью стандартного менеджера пакетов.

Сначала нужно обновить список пакетов системы, а затем можно загрузить пакеты PostgreSQL и contrib:

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

Установка программы завершена. Теперь можно ознакомиться с её функциями и особенностями.

Роли и базы данных PostgreSQL

По умолчанию PostgreSQL использует так называемые роли. Роли чем-то похожи на учётные записи пользователей и на группы Unix-подобных систем.

Во время установки PostgreSQL использует модель аутентификации ident, которая связывает роли PostgreSQL со стандартными пользователями Unix/Linux. Открыть существующую роль можно при помощи связанной с ней учётной записи Linux.

Существует несколько способов получить доступ к PostgreSQL при помощи этого аккаунта.

Пользователь postgres

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

Чтобы перейти в сессию пользователя postgres, введите:

sudo -i -u postgres

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

psql

Команда выполнит вход и откроет доступ к управлению СУБД.

Чтобы закрыть командную строку PostgreSQL, наберите:

\q

Это вернёт командную строку postgres в Linux.

Прямой доступ к командной строке PostgreSQL

Можно также получить доступ к командной строке PostgreSQL, не изменяя при этом сессии пользователя. Для этого нужен доступ к команде sudo.

Чтобы получить доступ к командной строке Postgres, запустите команду psql как пользователь postgres с правами sudo:

sudo -u postgres psql

Эта команда выполнит автоматический вход в PostgreSQL без промежуточной оболочки bash.

Чтобы закрыть эту командную строку, введите:

\q

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

На данный момент в СУБД есть только одна роль, postgres. Чтобы создать новую роль, используйте команду createrole. Чтобы создать роль в интерактивном режиме, добавьте флаг –interactive.

В сессии пользователя postgres создать нового пользователя можно с помощью команды:

createuser --interactive

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

sudo -u postgres createuser --interactive

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

Enter name of role to add: 8host
Shall the new role be a superuser? (y/n) y

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

man createuser

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

По умолчанию роль Postgres запрашивает одноименную базу данных. Потому следует создать такую БД для нового пользователя.

Только что вы создали нового пользователя ( в руководстве он условно называется 8host). Для создания БД используется команда createdb.

В сессии пользователя postgres создать новую БД можно с помощью команды:

createdb 8host

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

sudo -u postgres createdb 8host

Аутентификация новой роли PostgreSQL

Чтобы выполнить ident аутентификацию PostgreSQL, нужно иметь пользователя системы Linux, чье имя совпадает с именем роли и БД.

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

sudo adduser 8host

После этого можно перейти в сессию нового пользователя и подключиться к БД:

sudo -i -u 8host
psql

Или же подключиться к БД напрямую:

sudo -u 8host psql

Если все предыдущие настройки были выполнены правильно, команда выполнит автоматическое подключение к БД.

Чтобы подключиться к другой БД, нужно указать её имя:

psql -d postgres

Чтобы получить информацию о текущем подключении, введите:

\conninfo
You are connected to database "8host" as user "8host" via socket in "/var/run/postgresql" at port "5432".

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

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

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

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

Как видите, для таблицы нужно выбрать имя, определить столбцы, тип столбцов и максимальную длину поля. Опционально можно также установить ограничения для каждого столбца.

Примечание: Подробнее о работе с таблицами можно прочитать в руководстве «Управление таблицами PostgreSQL».

К примеру, тестовая страница может иметь такой вид:

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, который указывает порядковый номер записи. Этот столбец был ограничен по PRIMARY KEY, что значит, что значения должны быть уникальными и не должны быть нулём.

Длина поля столбцов equip_id и install_date не ограничена; дело в том, что задавать длину некоторых столбцов необязательно, так как она автоматически задаётся типом данных.

Столбцы type и color не могут быть пустыми. Столбец location ограничен восемью возможными значениями. Столбец date указывает дату установки оборудования.

Просмотреть таблицу можно при помощи команды:

\d
List of relations
Schema |          Name           |   Type   | Owner
-------+-------------------------+----------+-------
public | playground              | table    | 8host
public | playground_equip_id_seq | sequence | 8host
(2 rows)

playground_equip_id_seq имеет тип данных sequence. Это представление типа данных serial, установленного для столбца equip_id. Этот тип отслеживает следующий номер в последовательности.

Чтобы просмотреть таблицу вне этого типа данных, введите:

\dt
List of relations
Schema |    Name    | Type  | Owner
-------+------------+-------+-------
public | playground | table | 8host
(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 был заполнен автоматически. Чтобы удалить значение (к примеру, 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)

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

PostgreSQL позволяет изменять количество столбцов таблицы после её создания.

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

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; замените yellow в столбце color новым значением (например, 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)

Как видите, значение было успешно обновлено.

Заключение

Теперь система управления базами данных PostgreSQL установлена на сервер Ubuntu 16.04 и готова к использованию.

Полезные статьи:

• Управление таблицами PostgreSQL на облачном сервере
• Использование ролей и управление доступом в PostgreSQL
• Установка и использование phpPgAdmin
• Защита PostgreSQL на сервере Ubuntu
• Репликация баз данных PostgreSQL по типу Master/Slave
• Создание резервной копии данных PostgreSQL на сервере Ubuntu
• Запрос данных PostgreSQL при помощи команды select