Установка и использование PostgreSQL в Ubuntu 22.04
Инструкция о том, как развернуть PostgreSQL на виртуальной машине и научиться работать с базовыми функциями сервиса.
Введение: что такое PostgreSQL
PostgreSQL (Postgres) — одна из популярных СУБД для проектов различных уровней: от стартапа до высоконагруженной системы. Она имеет большой набор функций, помогающий разработчикам и администраторам в разработке приложений, защите целостности данных и их управлении. PostgreSQL стандартизирована, надежна и способна осуществлять параллельность операций без блокировки при чтении. Она универсальна, подходит под любую операционную систему. В числе преимуществ также можно выделить:
- расширяемость (можно определять свои типы данных, создавать пользовательские функции);
- возможность индексирования географических объектов и присутствие расширения PostGIS;
- наследование (концепция ООП, считающая, что абстрактный тип данных способен наследовать данные и набор функций уже существующего типа, способствуя переиспользованию компонентов ПО;
- масштабируемая система встроенных языков программирования;
- устойчивые и надежные функции репликаций и транзакций.
Поскольку PostgreSQL продукт open-source, ее функции можно расширять и дополнять. Для дополнения функций можно воспользоваться одним из следующих языков:
- процедурный язык PL/pgSQL, встроенный язык, аналогичный PL/SQL, который используется в СУБД Oracle;
- классические языки C, C++, Java (с модулем PL/Java);
- скриптовые языки PL/v8 (JS), PL/Scheme, PL/Tcl, PL/sh, PL/Ruby, PL/Python, PL/PHP, PL/Perl, PL/LOLCODE, PL/Lua;
- Статистический язык R (используя модуль PL/R).
PostgreSQL поддерживает следующие объекты БД: B-дерево, хеш, GiST, BRIN, Bloom.
У PostgreSQL большой список поддерживаемых данных: численные типы, символьные типы, двоичные типы, «дата/время», булев тип, геометрические примитивы и т.д.
PostgreSQL позволяет нескольким пользователям работать одновременно с БД благодаря механизму MVCC (Multiversion Concurrency Control). За счет этого исключается необходимость блокировок чтения.
В этой инструкции мы развернем PostgreSQL на виртуальной машине. Рассмотрим базовые функции сервиса: генерацию роли, базы данных и таблицы, работу с консолью с добавленной ролью и удаление СУБД на виртуальной машине
Требования к серверу
СУБД развернем на виртуальной машине Ubuntu 22.04, аккаунтом без root прав с sudo и брандмауэром.
Создание виртуальной машины (сервера)
При написании этой инструкции мы воспользуемся Облачной платформой Selectel, на которой сконфигурируем сервер с нужной нам операционной системой.
На странице Облачная платформа перейдем в раздел Серверы и нажмем Создать сервер.
В поле Источник из выпадающего списка выберем Ubuntu 22.04.
В качестве ОС выберем образ Ubuntu 22.04. Для небольшого сервера PostgreSQL будет достаточно конфигурации с 1 CPU, 2 ГБ оперативной памяти и 5 ГБ диска. В настройках сети не забудьте выделить машине внешний IP-адрес, если планируете подключаться к ней из интернета. Также сохраните пароль от root-пользователя и проверьте, что выбран правильный SSH-ключ.
Минимальные настройки готовы, нажмем кнопку Создать — наша виртуальная машина готова и отображается в списке на вкладке Серверы. Как самостоятельно сконфигурировать сервер, развернуто написано в базе знаний.
Первичная настройка
До развертывания PostgreSQL настроим сервер: перейдем к нему по SSH и настроим брандмауэр с утилитой UFW.
Подключение по SSH
В терминале локальной машины введем:
$ ssh root@server_ip
server_ip — значение IP-адреса сервера, находится в разделе «Порты».
Для аутентификации потребуется пароль root-пользователя, его можно увидеть в разделе «Консоль».
Настройка брандмауэра
Чтобы сервер позволял подключаться пользователям по SSH, разберемся с брандмауэром.
На сервер мы подключились под root-пользователем и прежде чем настраивать, переключимся на аккаунт без root-прав с sudo.
Обновим пакеты:
$ sudo apt update
Для брандмауэра скачиваем утилиту UFW:
$ sudo apt install ufw
Список профилей UFW можно вывести, написав:
$ sudo ufw app list
OpenSSH будет отображаться в списке:
Available applications:
OpenSSH
Разрешаем воспользоваться подключением по SSH:
$ sudo ufw allow OpenSSH
И стартуем брандмауэр:
$ sudo ufw enable
Проверим статус брандмауэра и список подключений OpenSSH:
$ sudo ufw status
Система даст ответ:
Status: active
To Action From
-- ------ ----
OpenSSH ALLOW Anywhere
OpenSSH (v6) ALLOW Anywhere (v6)
Займемся непосредственно развертыванием PostgreSQL.
Установка PostgreSQL на Ubuntu
В репозиториях Ubuntu уже включена PostgreSQL. Развертывание выполняется командой apt.
До загрузки PostgreSQL обновляем списки пакетов:
$ sudo apt update
Загрузим PostgreSQL с утилитой -contrib:
$ sudo apt install postgresql postgresql-contrib
Загрузятся драйверы PostgreSQL последней версии и развернутся необходимые компоненты на виртуальной машине с Ubuntu.
Запускаем сервис:
$ sudo systemctl start postgresql.service
Проверка статуса сервиса:
$ sudo systemctl status postgresql.service
Сервис развернули, разберемся в работе аккаунта postgres.
Работа с аккаунтом PostgreSQL
PostgreSQL применяет термин «Роль». Практически это тот же аккаунт в Ubuntu. При запуске СУБД роли сервиса привязываются к одноименным аккаунтам в Unix-системах. Другими словами, при наличии роли в PostgreSQL, войти в СУБД можно с аккаунтом Ubuntu. При запуске СУБД генерируется аккаунт postgres, привязываемый к роли PostgreSQL.
Вариант 1
Войдем в аккаунт:
$ sudo -i -u postgres
После ввода команды видим подтверждение о переходе в аккаунт:
postgres@postgresdoc:~$
Откроем консоль Postgres:
$ psql
Консоль открыта, что подтверждается записью в начале строки:
postgres=#
Работа в СУБД ведется из консоли.
Узнать статус подключения:
postgres=# \conninfo
Возврат в аккаунт:
postgres=# \q
Вариант 2
Войдем в аккаунт postgres с sudo. Если сейчас находимся в аккаунте postgres, нужно выйти, набрав exit. В этом варианте перейдем в аккаунт postgres с sudo:
$ sudo -u postgres psql
Возврат в аккаунт:
postgres=# \q
Создание роли
Аккаунт postgres обладает правами администратора. Напишем createuser, эта команда сообщает, что мы добавляем новую роль. Чтобы указать имя роли и выдать суперюзера, применим флаг —interactive.
Запись будет такой:
postgres@postgresdoc:~$ createuser --interactive
Вариант работы без переходов между аккаунтами:
$ sudo -u postgres createuser --interactive
Вводим имя, выдаем суперюзера:
Enter name of role to add: tester
Shall the new role be a superuser? (y/n) y
Посмотреть другие ключи настроек:
postgres@postgresdoc:~$ man createuser
Роль создана, поднимаем БД.
Создание базы данных
Любому созданному аккаунту привязывается база данных с идентичным именем, то есть наш созданный tester начнет подключаться к базе данных tester.
Командой createdb добавим БД (поднимем новую базу PostgreSQL на Ubuntu), назвав ее tester:
postgres@postgresdoc:~$ createdb tester
Вариант работы без переходов между аккаунтами:
$ sudo -u postgres createdb tester
Переход в командную строку PostgreSQL с новой ролью
Работа в консоли PostgreSQL подразумевает наличие аккаунта Ubuntu с именем БД в Postgres.
Добавим аккаунт Ubuntu, используя adduser (предварительно выйдя из аккаунта postgres), назвав аналогично новой роли:
$ sudo adduser tester
Добавив аккаунт tester, переключаемся на него и подключаемся к консоли:
$ sudo -i -u tester
$ psql
второй вариант:
$ sudo -u tester psql
Переключиться на другую БД:
$ psql -d postgres
Проверка статуса:
tester=# \conninfo
Увидим:
You are connected to database " tester " as user " tester " 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)
);
Создавая таблицу, указываем ее имя, столбцы, их типы, ограничения размеров полей. В качестве непосредственного примера добавим таблицу комплекта сноубордиста:
tester=# CREATE TABLE snowboarder (
equip_id serial PRIMARY KEY,
title varchar (50) NOT NULL,
company varchar (25) NOT NULL,
size varchar (25) check (size in ('XS', 'S', 'M', 'L', 'XL', 'XXL'))
);
СУБД выводит информацию:
CREATE TABLE
- Equip_id — столбец с идентификатором типа serial и автоинкрементом. Ему присвоили свойство primary key, указывающее на использование не нулевых показателей.
- Title и company указывают наименование и фирму-производителя единицы комплекта.
- Size хранит размеры наименований комплекта, предлагая выбор одного из предложенных размеров.
Посмотреть таблицу:
tester=# \d
Вывод:
List of relations
Schema | Name | Type | Owner
-----------+-------------------------------------+--------------+--------
public | snowboarder | table | tester
public | snowboarder_equip_id_seq | sequence | tester
(2 rows)
Создана таблица с переменной snowboarder_equip_id_seq, тип данных sequence. Переменная указывает на номера последовательности и генерируется автоматически.
Вывести таблицу без переменной:
tester=# \dt
Вывод:
List of relations
Schema | Name | Type | Owner
-----------+-------------------------------------+-------------+---------
public | snowboarder | table | tester
(1 rows)
Работа с данными таблицы
Таблица есть, остается ее наполнить.
Добавление данных в таблицу
Состав комплекта нашего сноубордиста: snowboard (сноуборд), binding (крепления), boots (ботинки). Вызовем таблицу, где укажем столбцы и их значения. Добавим 3 строки:
tester=# INSERT INTO snowboarder (title, company, size) VALUES ('snowboard', 'burton', 'XL');
tester=# INSERT INTO snowboarder (title, company, size) VALUES ('binding', 'burton', 'XL');
tester=# INSERT INTO snowboarder (title, company, size) VALUES ('boots', 'burton', 'XL');
Пишем внимательно, избегая ошибок. Неверно использовать кавычки в названиях столбцов. Кавычки указываются в значениях столбцов. Значение столбца equip_id генерируется самостоятельно во время создания строки.
После добавления каждой строки СУБД подтверждает операцию:
INSERT 0 1
Вывод данных
Выведем таблицу с новыми данными:
tester=# SELECT * FROM snowboarder;
Увидим в ответ:
equip_id | title | company | size
--------------+----------------+-------------+------
1 | snowboard | burton | XL
2 | binding | burton | XL
3 | boots | burton | XL
(3 rows)
Видим, что все поля заполнены значениями, прописанными нами, и в equip_id отображается нумерация строк.
Удаление данных
Удалить строку:
tester=# DELETE FROM snowboarder WHERE title = 'binding';
СУБД подтвердит удаление строки:
DELETE 1
Проверим удаление:
tester=# SELECT * FROM snowboarder;
Увидим в ответ:
equip_id | title | company | size
--------------+----------------+-------------+------
1 | snowboard | burton | XL
3 | boots | burton | XL
(2 rows)
Строка binding удалена.
Изменение данных
Иногда записи таблицы приходится менять. Для изменения записи указывают тип и устанавливают новое значение. Выберем boots и изменим компанию на blackfire:
tester=# UPDATE snowboarder SET company = 'blackfire' WHERE title = 'boots';
СУБД подтвердит обновление строки:
UPDATE 1
Проверим:
tester=# SELECT * FROM snowboarder;
СУБД даст ответ:
equip_id | title | company | size
--------------+----------------+----------------+------
1 | snowboard | burton | XL
3 | boots | blackfire | XL
(2 rows)
Фирма-производитель ботинок изменилась на blackfire.
Работа со столбцами
Таблицы можно редактировать, меняя состав столбцов.
Добавление столбца
Введем столбец с отображением стоимости:
tester=# ALTER TABLE snowboarder ADD price varchar (25);
СУБД подтвердит добавление:
ALTER TABLE
Выведя таблицу, увидим пустой добавленный столбец, так как добавили его без указания данных. Как его заполнить данными, описано выше.
tester=# SELECT * FROM snowboarder;
СУБД даст ответ:
equip_id | title | company | size | price
--------------+----------------+----------------+------+-------
1 | snowboard | burton | XL |
3 | boots | blackfire | XL |
(2 rows)
Удаление столбца
Удалить столбец из таблицы:
tester=# ALTER TABLE snowboarder DROP price;
Команда удалит столбец price и значения внутри него.
СУБД подтвердит удаление столбца:
ALTER TABLE
Удаление таблицы
Удаляется таблица командой:
tester=# DROP TABLE snowboarder;
СУБД подтвердит операцию:
DROP TABLE
Введя команду \dt, увидим сообщение от СУБД:
Did not find any relations.
Установка phppgadmin
Утилита PhpPgAdmin доступна в репозитории по дефолту в Ubuntu 22.04. Устанавливаем утилиту PhpPgAdmin под пользователем Ubuntu:
$ sudo apt-get install phppgadmin
Когда утилита установится, переходим в файл конфигурации phppgadmin.conf в директории /etc/apache2/conf-available и закомментируем строку Require local. Пропишем строку Allow From all. Такие изменения в файле конфигурации позволят подключаться к серверу как с локальной машины, так и с других устройств.
Перезагрузим Apache:
$ sudo systemctl restart apache2
Удаление PostgreSQL
Перед удалением PostgreSQL и очисткой сервера от следов сервиса удостоверимся, что СУБД остановлена.
В аккаунте Ubuntu напишем, что хотим узнать:
$ ps -Cpostgres
Если СУБД остановлена, ответ на команду не должен показать никаких результатов. В ином случае, обслуживание нужно остановить. Остановить PostgreSQL:
$ sudo systemctl stop postgresql
Далее укажем:
$ sudo apt-get --purge remove postgresql\*
PostgreSQL удалена на нашей виртуальной машине.
После удаления пропишем команды:
$ sudo rm -r /etc/postgresql/
$ sudo rm -r /var/lib/postgresql/
$ sudo userdel -r postgres
$ sudo groupdel postgres
Заключение
В этой инструкции мы развернули PostgreSQL на сервере Ubuntu 22.04.
Рассмотрели базовые функции по работе в СУБД:
- добавили роль, БД, таблицу;
- внесли в нее нужные нам значения;
- удалили таблицу и саму PostgreSQL.
Поскольку СУБД обладает множеством возможностей, рассчитанных на новых пользователей, со временем понадобятся и более продвинутые функции хранения и обработки данных. Все функции PostgreSQL можно изучить на странице официальной англоязычной версии документации.
Стоит отметить, что у PostgreSQL большое сообщество пользователей, а открытый исходный код СУБД способствует тому, что при новых релизах новые ошибки выявляются, быстро исправляются, и успешно осуществляется тестирование.