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

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

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

Данное руководство поможет установить PostgreSQL на виртуальный выделенный сервер Ubuntu 14.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.

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

sudo -i -u postgres

Программа запросит пароль пользователя, а затем откроет командную строку пользователя postgres.

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

psql

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

Рассмотрим подробнее использование ролей и баз данных.

Закройте командную строку PostgreSQL:

\q

Эта команда вернёт командную строку Linux для пользователя postgres.

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

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

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

createuser --interactive

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

man createuser

Создание БД

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

Примечание: В данном руководстве используется учетная запись пользователя с условием именем test1; соответственно, БД для этого пользователя тоже должна называться test1.

Чтобы создать БД, введите команду:

createdb test1

Эта команда создаст тестовую БД по имени test1.

Подключение к PostgreSQL при помощи нового пользователя

Предположим, у вас есть пользователь Linux по имени test1, одноименная роль PostgreSQL и одноименная БД.

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

sudo -i -u test1

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

psql

Программ выполнит автоматический вход при условии, что все компоненты были настроены правильно.

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

psql -d postgres

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

\conninfo

You are connected to database “postgres” as user “postgres” 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, что значит, что значения должны быть уникальными и не должны быть нулём.

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

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

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

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

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

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

\dt
List of relations
Schema |    Name    | Type  |  Owner
-------+------------+-------+----------
public | playground | table | postgres
(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 14.04 и готова к использованию.

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

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