PostgreSQL

Установка PostgreSQL

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql

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

PosgreSQL имеет клиент-серверную модель взаимодействия.

postgres – это сервер базы данных PostgreSQL. Чтобы клиентское приложение могло получить доступ к базе данных, оно подключается (по сети или локально) к работающему экземпляру postgres. Затем экземпляр postgres запускает отдельный серверный процесс для обработки соединения.

psql — это клиентский интерфейс для PostgreSQL на основе терминала. Он позволяет вам вводить запросы в интерактивном режиме, отправлять их в PostgreSQL и просматривать результаты запросов. В качестве альтернативы ввод может быть из файла или из аргументов командной строки. Кроме того, psql предоставляет ряд мета-команд и различных функций, подобных оболочке, для облегчения написания сценариев и автоматизации широкого спектра задач.

Проверка установленной версии серверной части:

/usr/lib/postgresql/15/bin/postgres -V

В Debian 11 postgres находится в /usr/lib/postgresql/15/bin. Во время установки в переменные окружения PATH не прописывается путь до postgres, т.к. в системе может быть установлено несколько версий.

Проверка установленной версии клиентской части:

psql --version

Проверить статус службы:

systemctl status postgresql.service

Запуск службы:

sudo systemctl start postgresql.service

Остановка службы:

sudo systemctl stop postgresql.service

Добавление службы в автозагрузку:

sudo systemctl enable postgresql.service

Удаление службы из автозагрузки:

sudo systemctl disable postgresql.service

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

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

sudo -u postgres createdb [название базы данных]

Будет создана база данных от пользователя postrges. Для создания базы данных другими пользователями, их необходимо создать.

Чтобы удалить базу данных необходимо ввести команду:

dropdb [название базы данных]

Роли пользователей

По умолчанию PostgreSQL создает пользователя postgres и базу данных postgres. Для подключения к базе данных необходимо ввести команду:

sudo -u postgres psql

Теперь вы подключены пользователем postgres к базе данных postgres.

PostgreSQL ассоциирует свои роли с системными учетными записями Unix. Подробнее про роли можно почитать здесь.

Создать роль bob для нового пользователя в интерактивной оболочке psql:

CREATE ROLE bob WITH LOGIN CREATEDB;

Изменить пароль для пользователя bob в интерактивной оболочке psql:

ALTER ROLE bob WITH PASSWORD 'пароль в кавычках';

Удалить пароль для пользователя bob в интерактивной оболочке psql:

ALTER ROLE bob WITH PASSWORD NULL;

Теперь пользователь bob может подключиться к базе данных postgres, созданной по умолчанию во время установки. Для входа необходимо ввести команду:

psql -U bob -W postgres

Подключение к базе данных по ssh

Конфигурационный файл pg_hba.conf отвечает за аутентификацию и настройку подключения клиентами, например, такими как интерактивный терминал psql.

Расположение конфигурационного файла можно определить, запустив команду:

locate pg_hba.conf

В Debian 11 для PosgreSQL версии 15 это директории:

/etc/postgresql/15/main/ 
/usr/share/postgresql/15/

Подробнее об аутентификации и параметрах конфигурационного файла pg_hba.conf можно почитать в официальной документации.

Чтобы пользователь bob мог подключиться к localhost с помощью пароля необходимо в конфигурационном файле вставить строку вида:

host    all             bob             localhost               password

Далее перезапустить службу и войти с помощью интерактивной оболочки psql:

psql -U bob -d [database name] -h localhost -p 5432

На входе нужно будет ввести пароль. Порт 5432 установлен по умолчанию.

Postgres в контейнере Docker

Создать Dockerfile. Содержимое файла:

FROM postgres:17

Перейти в директорию с файлом Dockerfile. Создать образ командой:

$ sudo docker build -t "Название образа" .

Запустить контейнер:

$ sudo docker run --network music-api-net --name postgres -dit -e POSTGRES_PASSWORD=1234 -d postgres postgres

Пояснение команды:

`–network music-api-net` Контейнер подключается к ранее созданной сети “music-api-net”.
`–name postgres` Присвоить название “postgres” контейнеру.
`-dit` Отключить интерактивный режим.
`-e POSTGRES_PASSWORD=1234` Присвоить значение переменной. Присваивается пароль, который запрашивается при подключении к БД.
`-d postgres` Название базы данных для подключения.
`postgres` Образ из которого будет создан контейнер.

Ссылки на источники:

Официальная документация – https://www.postgresql.org/docs/
Описание конфигурационного файла pg_hba.conf – https://www.postgresql.org/docs/15/auth-pg-hba-conf.html
Статья про создания роли – https://www.postgresqltutorial.com/postgresql-administration/postgresql-roles/
Установка и настройка PostgreSQL – https://zalinux.ru/?p=6695
Список SQL команд – https://www.postgresql.org/docs/current/sql-commands.html
Пример работы с таблицами через psql – https://docs.arenadata.io/ru/ADPG/current/how-to/queries/psql.html