Установка PostgreSQL 11

Thank you for reading this post, don't forget to subscribe!

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

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

Данный мануал поможет установить PostgreSQL на виртуальный выделенный сервер CentOS 7 и научит работать с этой СУБД.

1: Установка PostgreSQL

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

Прежде чем перейти к настройке нового репозитория, исключите поиск пакетов postgresql из репозитория CentOS-Base. В противном случае зависимости могут разрешиться на другую версию пакета.

Откройте конфигурационный файл репозитория с помощью текстового редактора, например, vim:

sudo vi /etc/yum.repos.d/CentOS-Base.repo

Найдите разделы [base] и [updates], перейдите в режим вставки, нажав i, и вставьте строку exclude=postgresql* в оба раздела. В результате файл будет выглядеть следующим образом:

... [base] name=CentOS-$releasever - Base mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=os&infra=$infra #baseurl=http://mirror.centos.org/centos/$releasever/os/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 exclude=postgresql* #released updates [updates] name=CentOS-$releasever - Updates mirrorlist=http://mirrorlist.centos.org/?release=$releasever&arch=$basearch&repo=updates&infra=$infra #baseurl=http://mirror.centos.org/centos/$releasever/updates/$basearch/ gpgcheck=1 gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-7 exclude=postgresql* ...

Когда вы закончите, нажмите ESC, чтобы выйти из режима вставки, затем :wq и Enter, чтобы сохранить и закрыть файл.

Теперь установите пакет конфигурации репозитория, используйте для этого официальный репозиторий PostgreSQL для CentOS:

sudo yum install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Подтвердите установку, нажав у.

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

yum list postgresql*

Выберите и установите нужную версию PostgreSQL. В этом мануале мы используем релиз PostgreSQL 11.

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

sudo yum install postgresql11-server

В процессе установки вам будет предложено импортировать ключ GPG:

... Importing GPG key 0x442DF0F8: Userid : "PostgreSQL RPM Building Project <pgsqlrpms-hackers@pgfoundry.org>" Fingerprint: 68c9 e2b9 1a37 d136 fe74 d176 1f16 d2e1 442d f0f8 Package : pgdg-redhat-repo-42.0-5.noarch (installed) From : /etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG Is this ok [y/N]:

Чтобы продолжить установку, введите у.

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

2: Создание кластера PostgreSQL

Нам нужно создать новый кластер базы данных PostgreSQL, прежде чем начать использовать Postgres. Кластер баз данных – это набор баз, которые управляются одним экземпляром сервера. Чтобы создать кластер, нужно создать ряд каталогов, в которые будут помещены данные БД, создать таблицы и базы данных template1 и postgres.

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

Создайте новый кластер PostgreSQL с помощью initdb:

sudo /usr/pgsql-11/bin/postgresql-11-setup initdb

Вы увидите следующий вывод:

Initializing database ... OK

Теперь запустите и включите сервис PostgreSQL с помощью systemctl:

sudo systemctl start postgresql-11 sudo systemctl enable postgresql-11

Вы увидите такой вывод:

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-11.service to /usr/lib/systemd/system/postgresql-11.service.

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

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

По умолчанию PostgreSQL использует так называемые роли. Они чем-то похожи на учётные записи пользователей или группы Unix-подобных систем. Только PostgreSQL не различает пользователей и группы, она объединяет их под понятием «роли».

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

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

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

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

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

sudo -i -u postgres

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

psql

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

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

\q

Это вернёт командную строку Linux. Чтобы вернуться в сессию обычного пользователя sudo, введите:

exit

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

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

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

sudo -u postgres psql

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

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

\q

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

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

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

createuser --interactive

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

sudo -u postgres createuser --interactive

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

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

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

man createuser

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

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

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

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

createdb mid

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

sudo -u postgres createdb mid

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

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

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

sudo adduser mid

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

sudo -i -u mid psql

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

sudo -u mid psql

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

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

psql -d postgres

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

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

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

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

Вы уже знаете, как подключиться к системе баз данных PostgreSQL. Теперь вы можете изучить некоторые основные задачи по управлению 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) );

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

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

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 указывает дату установки оборудования.

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

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

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

7.1 Управление Таблицами

Синтаксис таблиц PostgreSQL

Новая БД ещё не содержит таблиц. В этом можно убедиться, запросив список доступных таблиц:

\d No relations found.

Чтобы создать новую таблицу, придерживайтесь следующего синтаксиса:

CREATE TABLE new_table_name ( table_column_title TYPE_OF_DATA column_constraints, next_column_title TYPE_OF_DATA column_constraints, table_constraint table_constraint ) INHERITS existing_table_to_inherit_from;

Часть от закрытой скобки и до запятой – это дополнительная конструкция, позволяющая наследовать все столбцы существующей таблицы в дополнение к столбцам, перечисленным в команде.

Часть синтаксиса, взятая в скобки, делится на две части: определения столбцов и ограничения таблиц.

Определения столбцов PostgreSQL

Определение столбцов осуществляется согласно шаблону:

column_name data_type (optional_data_length_restriction) column_constraints

Примечание: Имя столбца должно быть описательным.

Типы данных PostgreSQL

boolean: (или bool) объявляет значения true и false.

Символьные значения

char: хранит один символ
char (#): содержит # количество символов; свободные места будут заполнены пробелами.
varchar (#): хранит данные переменной длины (не в Юникоде); параметр # определяет длину строки.

Целые значения

smallint: целое число между -32768 и 32767.
int: целое число между -214783648 и 214783647.
serial: целое число с автоувеличением.

Числа с плавающей точкой

float (#): число с плавающей точкой, где # — количество битов.
real: 8-битное число с плавающей точкой.
numeric (#,after_dec): число с # количеством цифр, где after_dec – количество цифр после десятичного знака

Дата и время

date: дата
time: время
timestamp: дата и время
timestamptz: дата, время и часовой пояс
interval: разница между двумя значениями timestamp

Геометрические типы

point: хранит пару координат определённой точки.
line: набор точек, определяющих линию.
lseg: набор данных, определяющий сегмент линии.
box: набор данных, который определяет прямоугольник.
polygon: набор данных, определяющий любое закрытое пространство

Сетевые адреса

inet: IP-адрес
macaddr: MAC адреса.

Ограничения столбцов и таблиц PostreSQL

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

NOT NULL: столбец не может иметь значение 0.
UNIQUE: значение столбца должно быть разным для каждой записи. Нуль всегда считается уникальным значением.
PRIMARY KEY: комбинирует первые два ограничения. Можно использовать только раз на всю таблицу.
CHECK: проверяет, истинно ли условие для значений в столбце.
REFERENCES: значение должно существовать в столбце в другой таблице.

После определения ограничений столбцов можно объявить ограничения для всей таблицы. Среди них UNIQUE, PRIMARY KEY, CHECK и REFERENCES.

Создание таблицы в PostgreSQL

Попробуйте создать тестовую таблицу. Для примера назовём её pg_equipment и поместим в неё данные о различном оборудовании для детских площадок. Введите следующее определение таблицы:

CREATE TABLE pg_equipment ( 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 ); NOTICE: CREATE TABLE will create implicit sequence "pg_equipment_equip_id_seq" for serial column "pg_equipment.equip_id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "pg_equipment_pkey" for table "pg_equipment" CREATE TABLE

Чтобы просмотреть таблицу, введите в командную строку \d.

Эта команда выведет на экран таблицу и создаст последовательность согласно типу данных serial.

Изменение таблиц в PostgreSQL

Теперь попробуйте внести в таблицу изменения. Для этого используется следующий синтаксис:

ALTER TABLE table_name Action_TO_Take;

К примеру, можно добавить в таблицу pg_equipment новый столбец.

ALTER TABLE pg_equipment ADD COLUMN functioning bool; ALTER TABLE

Чтобы просмотреть новый столбец, введите:

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

ALTER TABLE pg_equipment ALTER COLUMN functioning SET DEFAULT 'true';

Чтобы указать также, что значение не может быть нулём, используйте:

ALTER TABLE pg_equipment ALTER COLUMN functioning SET NOT NULL;

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

ALTER TABLE pg_equipment RENAME COLUMN functioning TO working_order;

Удалить столбец можно с помощью команды:

ALTER TABLE pg_equipment DROP COLUMN working_order;

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

ALTER TABLE pg_equipment RENAME TO playground_equip;

Удаление таблиц PostgreSQL

Чтобы удалить таблицу PostgreSQL, введите:

DROP TABLE playground_equip; DROP TABLE

Если применить эту команду к таблице, которой не существует, появится следующее сообщение об ошибке:

ERROR: table "playground_equip" does not exist

Чтобы избежать этой ошибки, можно добавить в команду IF EXISTS; тогда таблица будет удалена, если она существует. В любом случае команда будет выполнена успешно.

DROP TABLE IF EXISTS playground_equip; NOTICE: table "playground_equip" does not exist, skipping DROP TABLE

В этот раз команда сообщает, что заданная таблица не найдена, но не возвращает ошибки.

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

Теперь попробуйте добавить в таблицу данные.

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

INSERT INTO playground (type, color, location, install_date) VALUES ('slide', 'blue', 'south', '2017-04-28'); INSERT INTO playground (type, color, location, install_date) VALUES ('swing', 'yellow', 'northwest', '2018-08-16');

Добавляя данные в таблицу, помните:

Имя таблицы не нужно брать в кавычки.
Значения столбцов необходимо взять в кавычки.
В столбец equip_id не нужно добавлять данные; его значения генерируются автоматически.

Просмотрите добавленные данные:

Как видите, столбец equip_id был заполнен автоматически. Чтобы удалить значение (к примеру, slide), используйте команду:

DELETE FROM playground WHERE type = 'slide';

Проверьте таблицу:

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

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

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

ALTER TABLE playground ADD last_maint date;

Просмотрите таблицу, и вы увидите новый столбец (пока что он пуст):

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

ALTER TABLE playground DROP last_maint;

Это удаляет столбец last_maint и все найденные в нем значения, но оставляет все другие данные без изменений.

10: Обновление данных в таблице

Записи в таблице можно редактировать.

Чтобы обновить значение существующей записи, нужно запросить эту запись и указать столбец, значение которого нужно изменить. Для примера попробуйте обновить значение записи swing; замените yellow в столбце color новым значением (например, red).

UPDATE playground SET color = 'red' WHERE type = 'swing';

Просмотрите таблицу, чтобы убедиться, что данные были обновлены:

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

Centos