PostgreSQL — объектно-реляционная система управления базами данных. PostgreSQL базируется на языке SQL, отличается гибкостью и надежностью и поддерживает множество возможностей. Чаще всего PostgreSQL используется для сложных проектов, где требуется работа со сложными структурами данных, которые могут не поддерживаться обычными СУБД.
В статье мы рассмотрим установку PostgreSQL на VDS с Ubuntu 18.04 и особенности работы с ней.
Для выполнения операций, описанных в инструкции, на сервере должен быть создан пользователь с привилегиями sudo.
Установка PostgreSQL
PostgreSQL присутствует в официальных репозиториях Ubuntu, поэтому для установки достаточно выполнить:
Таким образом вы обновите локальный индекс пакетов и установите PostgreSQL вместе с пакетом contrib, который содержит дополнительный функционал и утилиты для работы СУБД.
При установке будет автоматически создана роль и пользователь в системе Linux postgres, под которым можно будет начать работу с PostgreSQL.
Пользователи (роли)
Вместо привычного понятия пользователей, PostgreSQL использует концепцию ролей. Несмотря на определенные отличия, в целом можно определять роли как пользователей базы данных.
В зависимости от настройки, роль можно рассматривать как пользователя базы данных или как группу пользователей. Роли могут владеть объектами базы данных (например, таблицами) и выдавать другим ролям разрешения на доступ к этим объектам. Также можно предоставить одной роли членство в другой роли (схоже с добавлением пользователя в группу), чтобы одна роль могла использовать привилегии другой роли.
Как отмечалось выше, во время установки была автоматически создана роль postgres. Вы можете работать с СУБД из-под нее. Для этого переключитесь на сессию данного пользователя:
После чего запустите консоль Postgres:
После завершения работы вы сможете выйти из консоли Postgres командой \q:
Так как для каждой созданной роли Postgres предполагает наличие базы данных с таким же именем и по умолчанию подключается именно к ней, имеет смысл создавать новую роль для каждой базы. Кроме того, если имя роли совпадает с именем пользователя, созданного в системе Linux, подключение к БД также упрощается.
Создание новой роли
Создать роль из консоли системы (не psql) можно с помощью команды:
Ключ -P позволит сразу задать пароль пользователю, а --interactive запустит интерактивный режим для указания дополнительных параметров.
Система поочередно запросит имя новой роли, ее пароль и повтор пароля, а также позволит указать привилегии: сделать ли роль суперпользователем, должны ли быть права на создание баз данных и создание других ролей. Нажимайте y / n и Enter для выбора.
Создать роль из консоли Postgres можно с помощью команды CREATE ROLE.
При использовании этой команды без дополнительных опций пользователь будет создан без каких-либо привилегий, поэтому желательно сразу при создании роли указывать нужные привилегии. Полный список доступных опций можно просмотреть, выполнив:
Чтобы создать новую роль выполните:
Далее задайте новому пользователю пароль:
Просмотр существующих ролей
Чтобы просмотреть созданных пользователей и их привилегии, перейдите в консоль Postgres:
И выполните команду:
Пример вывода:
Нажмите q, чтобы закрыть вывод, и \q, если нужно выйти из консоли Postgres.
Удаление роли
Чтобы удалить роль, можно использовать команду в консоли системы:
Либо команду в консоли Postgres:
Смена пароля пользователя
Для смены пароля одной из ролей подключитесь к Postgres от суперпользователя (postgres или другой роли с такими привилегиями), после чего выполните:
Обратите внимание, что эта операция сохранится в файле .psql_history вместе с паролем в открытом виде. Из соображений безопасности рекомендуется удалить соответствующую запись из файла.
Как правило, файл размещается в директории /var/lib/postgresql. Проверить его расположение можно в терминале системы с помощью:
Откройте файл, указав корректный путь к нему:
Удалите запись с паролем и сохраните изменения.
Создание базы данных
Создать базу из консоли системы можно следующим образом:
Для создания базы из консоли Postgres, используйте:
Вывести список баз можно командой \l в консоли Postgres:
Подключение к базе данных
- По умолчанию (без дополнительных опций) Postgres пытается подключиться от имени текущего пользователя к одноименной базе. Поэтому, если имя пользователя Linux совпадает с именем роли, созданной в Postgres, а также с именем базы, достаточно выполнить:
Например, если на сервере создан пользователь timeweb, а в Postgres — одноименная роль и база, подключиться можно с помощью данной команды:
После подключения можно выполнить команду \conninfo, чтобы посмотреть информацию о текущем соединении.
1.2. Если имя базы, к которой нужно подключиться, отличается, нужно указать его в команде:
- Если имя роли не совпадает с именем пользователя Linux, необходимо указать дополнительные параметры в команде.
2.1. Если имя роли и имя базы совпадают, достаточно выполнить:
Как видно из вывода \conninfo, мы подключены к базе tmweb от одноименной роли.
2.2. Если имя роли и базы отличаются, базу также нужно указать в команде:
- Если вы подключаетесь к другой базе данных уже в консоли Postgres, используйте:
Резервные копии (экспорт и импорт дампа)
При установке PostgreSQL на сервер устанавливаются утилиты pg_dump и pg_restore, с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них (pg_restore).
Создание резервной копии
Чтобы создать резервную копию базы и сохранить ее на сервере, необходимо выполнить команду:
Параметры:
- хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- формат_дампа — формат, в котором будет сохранен дамп; указывается буквами c, t или p: 'с' (custom — архив .tar.gz), 't' (tar — архив .tar), 'p' (plain — текстовый файл без сжатия, как правило, .sql);
- путь_к_дампу — путь сохранения для файла дампа и имя файла;
- имя_базы — имя базы данных, для которой создается резервная копия.
Например:
После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb).
Восстановление из дампа
Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью pg_restore:
Параметры:
- хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- формат_дампа — формат, в котором был сохранен дамп; необходимо указать 'с' для архива .tar.gz и 't' для архива .tar;
- имя_базы — имя базы данных, в которую импортируется дамп;
- путь_к_дампу — путь к файлу дампа и имя файла.
Например:
Для импорта дампов в формате .sql используется cat:
Параметры:
- путь_к_дампу — путь к файлу дампа и имя файла;
- хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
- имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
- имя_базы — имя базы данных, в которую импортируется дамп.
Например:
Работа с шаблонами баз данных
При установке PostgreSQL по умолчанию создаются три базы данных: postgres, template0, template1.
Template0 и template1 — это шаблоны баз данных, из которых в дальнейшем будут создаваться пользовательские БД.
Фактически, когда вы выполняете команду CREATE DATABASE, Postgres создает клон базы template1. Если внести изменения в template1, они будут наследоваться всеми новыми создаваемыми базами. Это позволяет, например, добавить в template1 необходимые вам таблицы с данными или установить расширения, после чего не потребуется добавлять их для каждой новой базы.
Обратите внимание, что для установки расширений необходимо подключиться к template1 от суперпользователя (postgres или другой роли, имеющей данные привилегии).
Подключитесь к шаблону template1:
И установите расширение:
Например, если вы установите в template1 расширение pgcrypto, то в дальнейшем, при выполнении CREATE DATABASE, новые базы будут создаваться с уже установленным pgrypto.
Template0 — это исходная база, которая используется, когда нужно создать новую базу без каких-либо изменений, внесенных в шаблон template1, или же вернуть template1 в его изначальное состояние.
Чтобы создать «чистую базу на основе template0, нужно выполнить:
Также template0 необходима, если вам нужно внести изменения в кодировку или локаль создаваемой базы данных. По умолчанию (при использовании template1) эти изменения невозможны.
В этом случае нужно при создании базы указать template0 в качестве шаблона и указать требуемую кодировку и/или локаль, например:
Пересоздание template1
С помощью template0 вы также можете вернуть базу template1 в исходный вид: для этого потребуется ее удалить, а после создать заново, на основе шаблона template0.
Сначала необходимо указать, что template1 не является шаблоном, чтобы удаление стало возможно:
Далее удалить template1:
И создать базу template1 заново, указав, что она будет являться шаблоном:
Параметры и конфигурационные файлы
Узнать расположение конфигурационного файла (как правило, размещается по пути: /etc/postgresql/версия/main/postgresql.conf) можно с помощью:
Узнать значение какого-либо параметра Postgres:
Получить расширенную информацию о параметре можно из представления pg_settings: будет выведено не только установленное значение, но также файл, в котором он назначен, максимальные и минимальные значения.
Например:
Чтобы вывести информацию в удобном для восприятия формате, можно сменить представление командой \x:
Другие полезные команды для управления Postgres
Вывести все параметры терминального командного меню Postgres:
Просмотр баз данных из командной строки Linux:
Список команд psql выводится с помощью \?:
Список SQL-команд (запросов):
Справка по конкретному SQL- запросу:
Информация о подключении:
Выполнение shell-команд из командной строки Postgres:
Подключение к базе данных: