PostgreSQL: все, что нужно знать для быстрого старта

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

Кроме того, информация обновляется настолько часто, что ее корректировка требует выделенных решений, оптимизированных под обработку и хранение конкретных видов информации. Для решения этой задачи было разработано специальное ПО — системы управления базами данных (СУБД). Впоследствии базы данных стали использоваться и на сайтах: например, для регистрации пользователей или онлайн-оплаты.

Многие облачные провайдеры предлагают готовые решения с предустановленными СУБД. Например, в Рег.ру можно заказать сервер с настроенной облачной PostgreSQL — СУБД , о которой пойдет речь.

Что такое PostgreSQL

PostgreSQL — это объектно-реляционная система управления базами данных. Она написана на языке C и распространяется свободно. СУБД PostgreSQL работает со стандартным языком запросов SQL, а также с его дополненной версией — PL/pgSQL.

Изначально СУБД носила название Postgres и с самого начала была некоммерческой. Она разрабатывалась в Калифорнийском университете в Беркли с 1986 года по 1994 год. За это время в Постгрес были добавлены правила, процедуры, пользовательские типы и многие другие компоненты, которые считаются основными.

В 1995 году началась разработка новой версии Постгрес, которая получила имя Postgres95. В этом варианте СУБД произошло глобальное изменение: первичный язык запросов POSTQUEL был заменен на популярный SQL.

Со временем Postgres95 была доработана до привычной PostgreSQL. Разработка этой версии вышла за пределы Калифорнийского университета в Беркли и была передана в руки программистов сообщества.

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

Источник: Shutterstock. В состав PostgreSQL входит большое количество инструментов безопасности

Особенности PostgreSQL

Любая СУБД имеет уникальные особенности, и с не исключение. Какими преимуществами она обладает:

Масштабируемость. PostgreSQL позволяет распределить входящие запросы между несколькими инстансами — экземплярами (репликами) БД. Также зачастую на облачных решениях с предустановленным Постгрес можно в любой момент добавить дополнительные ресурсы.
Кроссплатформенность. СУБД Постгрес можно установить практически на любую известную операционную систему. В число поддерживаемых ОС входят дистрибутивы Linux (Ubuntu, Debian, CentOS и другие), macOS, Windows 10 и 11.
Безопасность. PostgreSQL позволяет шифровать данные на разных уровнях, а также выбирать средства защиты информации от несанкционированного доступа. В состав Постгрес входит большое количество инструментов безопасности: например, LDAP, SSPI, Kerberos, GSSAPI и многие другие. Эти технологии можно использовать для создания дополнительных опций безопасности а также регулировать настройки доступа к объектам базы данных на разных уровнях: от самой БД до столбцов в ней.
Поддержка различных типов данных. PostgreSQL может работать не только со стандартными типами данных, которые существуют в большинстве популярных языков, но и со специфическими типами. Среди них можно встретить геометрические расчеты, сетевые адреса, типы полнотекстового поиска и многие другие.
Расширяемость. Вы можете написать собственные функции для Постгрес на Python, PHP, Java и Ruby. Также для загрузки доступны готовые модули на языке C из репозитория PGXN.
Открытый код. Постгрес распространяется по лицензии BSD. Это значит, что проект можно использовать, редактировать и распространять среди других пользователей бесплатно. Также разработчики могут модифицировать оригинальный код, скачав его с официального сайта.
Активное сообщество. Пользователи и разработчики собственного ПО еженедельно получают новости о Postgres из рассылок. Также участники сообщества делятся опытом, ведут дискуссии и помогают в решении вопросов разработки.
Поддержка NoSQL. PostgreSQL поддерживает форматы XML, JSON и JSONB. Это позволяет записать JSON-документ в базу данных, не разбирая его — от этого производительность сервера БД становится выше.

Также PostgreSQL имеет два недостатка:

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

Типы данных в PostgreSQL

PostgreSQL поддерживает большое количество типов данных. Среди них:

числовые типы, которые включают в себя целые числа, числа с плавающей точкой и десятичные числа с задаваемой точностью;
геометрические типы состоят из точек, прямых, отрезков, прямоугольников, многоугольников, путей и окружностей;
двоичный тип включает в себя двоичную строку переменной длины;
денежный тип хранит сумму с фиксированной дробной частью;
дата/время содержит набор типов даты и времени SQL. Все даты будут указаны согласно григорианскому календарю;
логический тип может иметь один из трех статусов: true, false или unknown;
типы перечислений представляют собой статический упорядоченный набор данных. Это могут быть дни недели или месяцы в календарном порядке;
диапазонные типы. Название полностью отражает их суть: это диапазоны значений определенного типа данных. Например, это могут быть временные интервалы, диапазоны дат, цен и многое другое.
символьные типы представлены строками переменной длины (ограниченными и неограниченными), а также строками фиксированной длины с пробелами;
составные типы представляют собой соотношения списка имен полей и типов данных;
массивы. Этот тип позволяет обозначить колонки таблицы в базе данных как многомерные массивы — то есть в виде массива, элементы которого также представлены массивами;
идентификаторы объектов — важный тип данных, который представляет собой первичный ключ для многих системных таблиц;
типы для описания сетевых адресов позволяют хранить адреса IPv4 и IPv6, а также MAC-адреса.

Полный список типов данных и их описания можно найти в официальной документации PostgreSQL.

Как установить PostgreSQL

В этом разделе мы рассмотрим установку Постгрес на ОС семейства Linux и Windows.

Linux

В качестве примера в этой инструкции мы опишем установку на ОС Ubuntu. Однако этот алгоритм применим и к Debian.

Чтобы установить Постгрес:

1. Подключитесь к серверу по протоколу SSH.

2. Обновите пакеты:

sudo apt update

1	sudo apt update

sudo apt upgrade

1	sudo apt upgrade

3. Установите PostgreSQL и подпакет contrib:

sudo apt install postgresql postgresql-contrib

1	sudo apt install postgresql postgresql-contrib

4. Добавьте публичный ключ репозитория pgAdmin4 APT:

curl 
https://www.pgadmin.org/static/packages_pgadmin_org.pub | 
sudo apt-key add

curl

https://www.pgadmin.org/static/packages_pgadmin_org.pub |

sudo apt-key add

5. Создайте конфигурационный файл репозитория:

sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

1	sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

6. Установите pgAdmin:

sudo apt install pgadmin4

1	sudo apt install pgadmin4

7. Перезапустите службу Apache с помощью скрипта:

sudo /usr/pgadmin4/bin/setup-web.sh

1	sudo /usr/pgadmin4/bin/setup-web.sh

8. В брандмауэре разрешите доступ для Apache:

sudo ufw allow 'Apache'

1	sudo ufw allow 'Apache'

9. Запустите брандмауэр:

sudo ufw enable

1	sudo ufw enable

10. Проверьте, добавлена ли служба Apache в список разрешенных:

sudo ufw status

1	sudo ufw status

Напротив службы Apache будет указано действие Allow.

11. Откройте браузер и в адресной строке введите

http://123.123.123.123/pgadmin4

1	http://123.123.123.123/pgadmin4

Вместо 123.123.123.123 укажите IP-адрес вашего сервера.

12. Укажите логин и пароль, которые вы вводили на шаге 7. Затем нажмите Login.

Windows

1. Подключитесь к серверу по протоколу RDP.

2. Перейдите на официальный сайт PostgreSQL.

3. Выберите операционную систему Windows:

4. Кликните по ссылке Download the installer:

5. В строке с именем нужной вам версии нажмите на значок загрузки:

6. По окончании загрузки запустите установочный файл.
7. В стартовом окне нажмите Next:

8. Выберите директорию для установки и кликните Next:

9. Оставьте список компонентов без изменений. Затем нажмите Next:

10. Выберите директорию для хранения файлов баз данных. Затем кликните Next:

11. Введите пароль суперпользователя (root-пароль). После этого нажмите Next:

12. Укажите порт для работы PostgreSQL. Затем кликните Next:

13. Выберите географическое положение вашего будущего кластера PostgreSQL и нажмите Next:

14. Проверьте параметры установки. Если они верны, кликните Next:

15. По окончании установки нажмите Finish:

Начало работы с PostgreSQL

Чтобы начать работу с БД в консоли PostgreSQL, необходимо знать базовые команды. В этом разделе мы разберем основные действия, которые могут потребоваться при работе с базой данных.

Команды PostgreSQL можно выполнить как в Linux, так и на Windows. В этом помогут специальные инструменты. Их выбор будет зависеть от операционной системы:

терминал, если вы работаете с Linux;
PowerShell, если вы работаете с Windows.

Открытие консоли Postgres

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

psql

psql

Если вы хотите открыть консоль от имени суперпользователя, выполните следующую команду:

sudo -u postgres psql

1	sudo -u postgres psql

В качестве postgres указано имя суперпользователя.

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

Для создания базы данных выполните команду:

CREATE DATABASE new_db

1	CREATE DATABASE new_db

Вместо new_db укажите имя базы данных.

Если вы хотите создать базу данных и сразу назначить ее владельца, выполните эту команду:

CREATE DATABASE new_db OWNER new_role;

1	CREATE DATABASE new_db OWNER new_role;

Где:

new_db — имя базы данных,
new_role — название роли.

Создание пользователя

Для создания нового пользователя выполните команду:

CREATE USER new_user;

1	CREATE USER new_user;

Вместо new_user укажите логин нового пользователя.

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

CREATE USER new_user WITH LOGIN PASSWORD 'user_password';

1	CREATE USER new_user WITH LOGIN PASSWORD 'user_password';

Где:

new_user — логин нового пользователя,
user_password — пароль нового пользователя.

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

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

CREATE TABLE new_table (


 address varchar(80),
 id int,        
 service real,          
 day date
);

CREATE TABLE new_table (

address varchar(80),

id int,

service real,

day date

);

Где:

new_table — название таблицы;
address, id, service, day — названия столбцов новой таблицы;
varchar, int, real, date — типы данных, которые будут храниться в каждом из столбцов.

Изменение настроек базы данных

Чтобы изменить настройки базы данных, используйте команду ALTER DATABASE:

ALTER DATABASE new_db ACTION value;

1	ALTER DATABASE new_db ACTION value;

Где:

new_db — имя базы данных;
ACTION — действие, которое нужно выполнить (переименовать, скопировать и др.);
value — значение (новое имя, место копирования и др.)

Ниже мы привели примеры команды ALTER DATABASE, которые можно применить на практике.

Переименовать базу данных:

ALTER DATABASE old_db RENAME TO new_db

1	ALTER DATABASE old_db RENAME TO new_db

Где:

old_db — текущее имя базы данных;
RENAME TO — действие, которое нужно выполнить;
new_db — новое имя базы данных.

Передать базу данных во владение другому пользователю:

ALTER DATABASE new_db OWNER TO new_user

1	ALTER DATABASE new_db OWNER TO new_user

Где:

new_db — имя базы данных;
OWNER TO — действие, которое нужно выполнить;
new_user — имя пользователя, которому нужно передать базу данных.

Закрытие консоли Postgres

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

/q

Полный список команд вы можете найти в официальной документации PostgreSQL.

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

Заказать PostgreSQL в облаке

Галина Ашмарина

18 октября 2024