PostgreSQL

PostgreSQL — это объектно-реляционная система управления базами данных с открытым исходным кодом. Она отличается высокой надежностью, масштабируемостью и широкими возможностями: поддержкой JSON, полнотекстовым поиском, хранимыми процедурами, расширениями и прочим.

Вы получаете полностью управляемый кластер PostgreSQL без необходимости ручной настройки — все происходит автоматически.

При создании кластера можно выбрать версию PostgreSQL: 14, 15, 16, 17 или 18.

Создание баз

При создании нового кластера автоматически создается база с именем default_db. Дополнительные базы можно создавать во вкладке «Базы данных» в интерфейсе.

Импорт базы данных

Для импорта базы данных перейдите во вкладку «Базы данных» в панели управления кластером и нажмите кнопку «Импортировать».

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

Для запуска импорта нажмите кнопку «Начать импорт». Статус импорта будет отображаться для базы в разделе «Базы данных».

Во время импорта операции с базами данных не блокируются.

Требования и ограничения

• Сетевой доступ: исходная база данных должна быть доступна для подключения из подсети 92.53.116.0/24. Без этого импорт не начнется.
• Уникальность имени: в целевом кластере не должно существовать базы данных с таким же (идентичным) именем.

Что будет перенесено:

• структура таблиц;
• индексы;
• данные в таблицах.

Что не будет перенесено:

• пользователи и роли;
• расширения.

Вам необходимо будет создать пользователей и установить необходимые расширения в новой базе данных вручную.

Совместимость версий: импорт поддерживает перенос данных между разными версиями PostgreSQL (например, из 17 в 18). Но, следует учитывать, что некоторые расширения и параметры могут быть несовместимы или иметь отличия в разных версиях.

Пользователи и привилегии

Пользователей можно создавать на вкладке «Пользователи». Для этого нажмите кнопку «Добавить».

В открывшемся окне задайте имя пользователя и пароль:

• Имя: от 3 до 64 символов. Допускаются латинские буквы, цифры и спецсимволы.
• Пароль: от 8 до 30 символов. Допускаются латинские буквы, цифры и спецсимволы.

После создания вы можете настроить привилегии для пользователя.

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

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

Доступные привилегии

Расширения

PostgreSQL поддерживает расширения, которые позволяют расширить стандартную функциональность — от работы с новыми типами данных до сбора статистики и управления задачами.

Набор доступных расширений может отличаться в разных версиях PostgreSQL.

Ниже представлены расширения, доступные для PostgreSQL:

Для включения нужного расширения перейдите во вкладку «Конфигурация» и нажмите на кнопку «Изменить» в строке «Расширения».

Настройка параметров

Вы можете изменить параметры, используемые для PostgreSQL, перейдя во вкладку «Конфигурация» и нажав на кнопку «Изменить параметры» в строке «Параметры базы данных».

Для получения дополнительной информации о параметрах нажмите кнопку «Открыть описание» рядом с нужным параметром.

Чтобы применить изменения, нажмите кнопку «Применить» внизу страницы.

Подключение к базе

Подключиться к базе можно через встроенный веб-интерфейс — Adminer. Для этого нажмите кнопку «Веб-интерфейс» и выберите Adminer.

Подключение из терминала

Установите клиент PostgreSQL:

sudo apt install postgresql-client

sudo pacman -S postgresql-libs

sudo dnf install postgresql

Существует три способа подключения к кластеру PostgreSQL:

• по публичному IP;
• по приватному IP;
• по домену.

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

• на вкладке «Дашборд»;

• на вкладке «Подключение».

При подключении по домену используется защищенное подключение по TLS. В новых кластерах оно включено по умолчанию, в старых — требуется включение вручную. 

Чтобы включить TLS:

1. Перейдите во вкладку «Подключение».
2. Нажмите кнопку «Включить защищенное подключение».
3. Дождитесь завершения настройки.

После включения TLS для подключения потребуется установить сертификат.

Готовые команды для установки доступны в панели управления, во вкладке «Подключение», для всех популярных операционных систем.

Чтобы отключить TLS, нажмите кнопку «Выключить защищенное подключение». В появившейся форме подтвердите действие и дождитесь завершения перенастройки базы.

Создание ролей

Пользователь с привилегией CREATE ROLE может:

• создавать новых пользователей (CREATE USER);
• создавать роли (CREATE ROLE);
• управлять членством в ролях (GRANT ... TO ...).

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

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

Создать пользователя с паролем можно при помощи запроса:

CREATE USER someuser WITH PASSWORD 'password';

Где someuser — логин пользователя, password — пароль.

Новый пользователь отобразится в панели управления DBaaS через некоторое время после создания.

Создание роли и назначение ее пользователю

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

Создадим две роли: одна с правами только на чтение, вторая — с правами на чтение и запись.

Роль с правами только на чтение:

CREATE ROLE app_reader;

Роль с правами на чтение и запись:

CREATE ROLE app_writer;

Назначим роль пользователю:

GRANT app_writer TO someuser;

Настройка схемы

По умолчанию все таблицы в PostgreSQL создаются в схеме public. Для выдачи грантов необходимо создать новую схему:

CREATE SCHEMA app_data;

Создадим таблицы и явно укажем новую схему:

CREATE TABLE app_data.users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE app_data.orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES app_data.users(id),
  order_date TIMESTAMP DEFAULT now()
);

Теперь можем выдать права на схему для ранее созданных ролей:

GRANT USAGE ON SCHEMA app_data TO app_reader;
GRANT USAGE ON SCHEMA app_data TO app_writer;

И право на создание объектов в схеме:

GRANT CREATE ON SCHEMA app_data TO app_writer;

Установка прав на таблицы

Выдадим права на чтение всех существующих таблиц в схеме:

GRANT SELECT ON ALL TABLES IN SCHEMA app_data TO app_reader;

И права на запись:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA app_data TO app_writer;

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

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT ON TABLES TO app_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_writer;

И права по умолчанию для новых последовательностей:

ALTER DEFAULT PRIVILEGES IN SCHEMA app_data 
GRANT USAGE, SELECT ON SEQUENCES TO app_writer;

Создание дампа

Для создания дампа используйте следующую команду:

pg_dump -x 'postgresql://<user>:<password>@<host>:5432/<db>' | gzip > default_db.sql.gz

Параметр -x (--no-privileges) исключает из дампа команды GRANT и REVOKE, так как назначение прав осуществляется через панель. Без этого флага возможны ошибки при загрузке дампа, хотя они не помешают восстановлению данных.

Загрузка дампа

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

zcat default_db.sql.gz | psql 'postgresql://<user>:<password>@<host>:5432/<db>'

Команда распаковывает дамп и передает его в клиент PostgreSQL для выполнения.

Репликация

Кластеры PostgreSQL в DBaaS используют связку Patroni и Etcd для обеспечения отказоустойчивости. Репликация работает по принципу leader-replica: один узел принимает все записи (лидер), а остальные получают изменения в асинхронном режиме.

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

Логи баз данных

Мы собираем и выводим в панель управления логи сервисов баз данных, которые они автоматически генерируют в стандартные файлы логов. Просмотреть их можно на вкладке «Логи».

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

Для PostgreSQL мы выводим основной лог ошибок (PostgreSQL Log), расположенный по пути /var/log/postgresql/postgresql-<version>-main.log.

Лог полезен для общего мониторинга состояния кластера, диагностики сбоев, анализа подключений и запросов.

Он содержит следующие типы информации:

• ERROR, FATAL, PANIC: Критические ошибки, приводящие к прерыванию сеанса или работы сервера.

• WARNING: Предупреждения о потенциальных проблемах.

• INFO: Общая информация о работе сервера (например, запуск, остановка, контрольные точки).

• LOG: Стандартные информационные сообщения (например, подключения, завершения длительных транзакций, контрольные точки).

В целях безопасности из всех логов автоматически удаляются записи, содержащие фразы WITH LOGIN PASSWORD или WITH PASSWORD, чтобы исключить возможность утечки паролей.

Сценарии использования

Рассмотрим примеры использования логов на практике. Для удобного поиска информации в логе его можно скачать в формате .txt.

Кейс 1: Внезапный рост потребления vCPU

В этом случае нам нужно определить запрос, создающий повышенную нагрузку. Для этого можно проанализировать лог медленных запросов (log_min_duration_statement) и быстро выявить операции, которые выполняются дольше других и активно потребляют процессорные ресурсы. Это позволит оперативно оптимизировать проблемный запрос или добавить недостающий индекс.

Кейс 2: Частые разрывы соединения у приложения

Здесь нам нужно определить причину обрыва подключений к БД. Для этого можно проанализировать лог ошибок и проверить, появляются ли в момент разрыва сообщения уровня FATAL или ERROR. Логи позволяют указать на конкретную причину — например, нехватка памяти, превышение таймаута или лимита подключений. Такой анализ помогает понять, связано ли поведение с сетевыми проблемами, ограничениями ресурсов или ошибками конфигурации.