Введение: краткое описание 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 20.04, аккаунтом без root прав с sudo и брандмауэром.

Создание виртуальной машины (сервера)

При написании этой инструкции мы воспользуемся «Облачной платформой Selectel», на которой сконфигурируем сервер с нужной нам операционной системой.

На странице «Облачная платформа» перейдем в раздел «Серверы» и нажмем «Создать сервер».

Создание сервера в облачной платформе Selectel

Имя серверу присваивается в поле «Имя». Назовем машину postgresdoc.

В поле «Источник» из выпадающего списка выберем Ubuntu 20.04.

Выбор конфигурации сервера

Проскроллим экран вниз и в поле «Сеть» в выпадающем списке укажем «Плавающий IP-адрес» для входа на сервер с других устройств.

Минимальные настройки готовы, нажмем кнопку «Создать» — наша виртуальная машина готова и отображается в списке на вкладке «Серверы».

Мы собрали сервер с характеристиками:

  • Ubuntu 20.04 LTS 64-bit; 
  • ядро: 1vCPU;
  • память 1ГБ RAM;
  • память 5 ГБ HDD.
Облачный сервер postgresdoc

Как самостоятельно сконфигурировать сервер, развернуто написано в базе знаний.

Первичная настройка

До развертывания 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 20.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 20.04.

Рассмотрели базовые функции по работе в СУБД:

  • добавили роль, БД, таблицу;
  • внесли в нее нужные нам значения;
  • удалили таблицу и саму PostgreSQL.

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

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

Что дальше?

Зарегистрироваться в панели управления

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

Узнать о продукте больше

Все о принципах работы, задачах и фичах «Облачных баз данных» читайте на нашем сайте.
Читать про продукт

Комментарии