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

Введение: что такое PostgreSQL

PostgreSQL (Postgres) — одна из популярных СУБД для проектов различных уровней: от стартапа до высоконагруженной системы. Она выполняет множество функций, которые помогают в разработке приложений, защите целостности данных и их управлении. PostgreSQL стандартизирована, надежна и способна выполнять операции параллельно без блокировки при чтении. Она универсальна, подходит под любую операционную систему. В числе преимуществ также можно выделить следующие возможности.

• Расширяемость. В Postgres можно определять свои типы данных и создавать пользовательские функции.
• Возможность индексировать географические объекты и использовать расширение 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). За счет этого исключается необходимость блокировок чтения.

Требования к серверу

СУБД развернем на виртуальной машине 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

    $ 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 

Если СУБД остановлена, в ответе на команду не должно быть никаких результатов. В ином случае обслуживание нужно остановить:

    $ 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 большое сообщество пользователей, а открытый исходный код СУБД способствует успешному тестированию, а также быстрому выявлению и исправлению ошибок.