Работа с базой данных PostgreSQL

Настройка PostgreSQL

Работать с PostgreSQL мы будем через терминал с помощью встроенной утилиты psql. Запускаем ее следующей командой:

psql postgres

Можно установить сторонние инструменты для администрирования PostgreSQL, но в этом мало смысла — psql справляется со всеми основными задачами.

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

\help

Если нужна справка по конкретной команде, пишем:

\help [имя команды]

Выйти из psql можно командой \q.

Управление пользователями

В PostgreSQL используется концепция ролей. Одну роль можно рассматривать как отдельного пользователя или как группу пользователей. Роли могут владеть объектами БД и выдавать разрешения другим ролям.

По умолчанию была создана роль postgres. Давайте создадим еще одну роль. Для этого из консоли системы выполняем команду:

createuser -P --interactive

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

Если вы уже зашли в psql, то создать новую роль можно командой:

CREATE ROLE имя_новой_роли WITH LOGIN CREATEDB CREATEROLE; // В конце обязательно ставим ;

Затем задаем пароль:

\password имя_роли

Вывести список всех ролей можно командой /du. Кроме имен отобразятся привилегии каждого роли.

Чтобы закрыть список ролей, выполняем команду q.

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

DROP ROLE имя_роли;

Это можно также сделать из консоли системы с помощью команды:

drop user имя_роли

Чтоб сменить пароль пользователя, подключаемся к psql с правами суперпользователя. Затем выполняем следующую команду:

ALTER USER имя_роли WITH PASSWORD 'новый_пароль';

Эта операция сохраняется в файле .psql_history вместе с паролем, который не будет зашифрован. В качестве дополнительной меры безопасности эту запись рекомендуется удалить. Файл обычно находится в директории /var/lib/postgresql.

Работа с базами данных в PostgreSQL

Создать базу данных из консоли можно следующей командой:

createdb имя_БД

Если вы используете терминальный клиент psql, то команда будет немного отличаться:

CREATE DATABASE имя_БД;

Чтобы посмотреть список всех БД, выполняем команду \l.

Для удаления базы данных используется та же команда, что и для удаления роли — drop. В терминале системы синтаксис будет таким:

drop database имя_БД

В клиенте psql синтаксис похожий:

DROP DATABASE имя_БД;

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

По умолчанию psql подключается от имени текущего пользователя Linux к БД с таким же названием. Если эти данные совпадают, достаточно выполнить запуск самого терминального клиента:

psql

Чтобы вывести информацию о текущем соединении, выполняем команду:

\conninfo

Если имя базы данных отличается от имени пользователя, нужно указать его явно:

psql -d имя_БД

Если имя роли не совпадает с именем пользователя в Linux, прописываем дополнительные параметры.

Имя роли и название БД совпадают:

psql -U имя_роли -h localhost -W

Название базы данных отличается от имени роли:

psql -U имя_роли -d имя_базы -h localhost -W // Разница в том, что явно указано название БД

Чтобы переключиться на другую базу данных внутри psql используем команду:

\c имя_БД

Создание резервной копии и восстановление из бэкапа

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

pg_dump -h хост -U имя_роли -F формат_дампа -f путь_к_дампу имя_БД

Чтобы было проще разобраться, рассмотрим каждый параметр:

• хост – сервер, на котором располагается БД. Например, можно указать localhost, домен, IP-адрес.

• имя_роли – имя пользователя PostgreSQL, под которым мы работаем с базой данных.

• формат_дампа – формат, в котором дамп сохранится на сервере. Доступны следующие форматы: c (custom) – архив .tar.gz, t (tar) – архив .tar, p (plain) – текст без сжатия, обычно .sql.

• путь_к_дампу – путь, по которому будет сохранена резервная копия.

• имя_БД – название БД, для которой будет создана резервная копия.

Выглядит это примерно так:

pg_dump -h localhost -U mybase -F c -f /home/user/backups/dump.tar.gz mybase

Для выполнения этой команды нужно ввести пароль, который используется при входе в psql от имени указанной роли (mybase в приведенном примере).

Восстановление из резервной копии выполняется аналогичным образом:

pg_restore -h хост -U имя_роли -F формат_дампа -d имя_базы путь_к_дампу

Параметры похожие, отличия минимальные. Важно знать хост, помнить формат и путь к бэкапу.

https://timeweb.com/ru/community/articles/rabota-s-bazoy-dannyh-postgresql