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

PostgreSQL – опенсорсная реляционная СУБД. Отличается гибкостью и надежностью, поддерживает большое количество полезных возможностей. Часто используется в проектах, где требуется работа со сложными структурами данных, с которыми не справляются простые СУБД. В этой статье мы разберемся, как работать с PostgreSQL. В качестве примера я буду использовать Ubuntu 18.04. Установка PostgreSQL

Установка PostgreSQL

PostgreSQL есть в репозитории Ubuntu, поэтому установка выполняется одной командой. Но сначала нужно проверить обновления самой системы:

sudo apt-get update

Для выполнения команды нужны права суперпользователя. Так что придется вспомнить пароль.

После установки апдейтов инсталлируем PostgreSQL:

sudo apt-get install postgresql postgresql-contrib

PostgreSQL установится вместе с пакетом contrib, в котором содержится дополнительная функциональность, а также утилиты для работы СУБД.

Чтобы убедиться, что все работает, проверим версию:

postgres --version

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

Настройка 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 имя_базы путь_к_дампу

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

Мы разобрались с основными действиями и настройками PostgreSQL. На этом все! 

Все права сохранены © Hobbymods - моддинг для души

Перепубликация материалов возможна только с устного или письменного разрешения администрации сайта!