Установка и работа с PostgreSQL

PostgreSQL — объектно-реляционная система управления базами данных. PostgreSQL базируется на языке SQL, отличается гибкостью и надежностью и поддерживает множество возможностей. Чаще всего PostgreSQL используется для сложных проектов, где требуется работа со сложными структурами данных, которые могут не поддерживаться обычными СУБД.

В статье мы рассмотрим установку PostgreSQL на VDS с Ubuntu 18.04 и особенности работы с ней. 

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

Установка PostgreSQL

PostgreSQL присутствует в официальных репозиториях Ubuntu, поэтому для установки достаточно выполнить:

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

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

При установке будет автоматически создана роль и пользователь в системе Linux postgres, под которым можно будет начать работу с PostgreSQL.

Пользователи (роли)

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

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

Как отмечалось выше, во время установки была автоматически создана роль postgres. Вы можете работать с СУБД из-под нее. Для этого переключитесь на сессию данного пользователя:

sudo su - postgres

После чего запустите консоль Postgres:

psql

После завершения работы вы сможете выйти из консоли Postgres командой \q:

postgres=# \q

Так как для каждой созданной роли Postgres предполагает наличие базы данных с таким же именем и по умолчанию подключается именно к ней, имеет смысл создавать новую роль для каждой базы. Кроме того, если имя роли совпадает с именем пользователя, созданного в системе Linux, подключение к БД также упрощается.

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

Создать роль из консоли системы (не psql) можно с помощью команды:

createuser -P --interactive

Ключ -P позволит сразу задать пароль пользователю, а --interactive запустит интерактивный режим для указания дополнительных параметров.

Система поочередно запросит имя новой роли, ее пароль и повтор пароля, а также позволит указать привилегии: сделать ли роль суперпользователем, должны ли быть права на создание баз данных и создание других ролей. Нажимайте y / n и Enter для выбора.

Создать роль из консоли Postgres можно с помощью команды CREATE ROLE.

При использовании этой команды без дополнительных опций пользователь будет создан без каких-либо привилегий, поэтому желательно сразу при создании роли указывать нужные привилегии. Полный список доступных опций можно просмотреть, выполнив:

\h CREATE ROLE

Чтобы создать новую роль выполните:

CREATE ROLE имя_роли WITH LOGIN CREATEDB CREATEROLE;

Далее задайте новому пользователю пароль:

\password имя_роли

Просмотр существующих ролей

Чтобы просмотреть созданных пользователей и их привилегии, перейдите в консоль Postgres:

psql

И выполните команду:

\du

Пример вывода:

                                   List of roles
 Role name |                        Attributes                          | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 tmweb     | Create role, Create DB                                     | {}
 tweb      | Create role, Create DB                                     | {}

Нажмите q, чтобы закрыть вывод, и \q, если нужно выйти из консоли Postgres.

Удаление роли

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

dropuser имя_роли

Либо команду в консоли Postgres:

DROP ROLE имя_роли;

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

Для смены пароля одной из ролей подключитесь к Postgres от суперпользователя (postgres или другой роли с такими привилегиями), после чего выполните:

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

Обратите внимание, что эта операция сохранится в файле .psql_history вместе с паролем в открытом виде. Из соображений безопасности рекомендуется удалить соответствующую запись из файла.

Как правило, файл размещается в директории /var/lib/postgresql. Проверить его расположение можно в терминале системы с помощью: 

grep postgres /etc/passwd | cut -d ':' -f 6

Откройте файл, указав корректный путь к нему:

sudo nano /var/lib/postgresql/.psql_history

Удалите запись с паролем и сохраните изменения.

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

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

createdb имя_базы

Для создания базы из консоли Postgres, используйте:

CREATE DATABASE имя_базы;

Вывести список баз можно командой \l в консоли Postgres:

postgres=# \l

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

1. По умолчанию (без дополнительных опций) Postgres пытается подключиться от имени текущего пользователя к одноименной базе. Поэтому, если имя пользователя Linux совпадает с именем роли, созданной в Postgres, а также с именем базы, достаточно выполнить:

psql

Например, если на сервере создан пользователь timeweb, а в Postgres — одноименная роль и база, подключиться можно с помощью данной команды:

После подключения можно выполнить команду \conninfo, чтобы посмотреть информацию о текущем соединении.

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

psql -d имя_базы

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

2.1. Если имя роли и имя базы совпадают, достаточно выполнить:

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

Как видно из вывода \conninfo, мы подключены к базе tmweb от одноименной роли.

2.2. Если имя роли и базы отличаются, базу также нужно указать в команде:

psql -U имя_роли -d имя_базы -h localhost -W

3. Если вы подключаетесь к другой базе данных уже в консоли Postgres, используйте:

\c имя_базы

Резервные копии (экспорт и импорт дампа)

При установке PostgreSQL на сервер устанавливаются утилиты pg_dump и pg_restore, с помощью которых вы сможете из консоли Linux создавать резервные копии базы данных (pg_dump) и восстанавливать данные из них (pg_restore).

Создание резервной копии

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

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

Параметры:

• хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• формат_дампа — формат, в котором будет сохранен дамп; указывается буквами c, t или p: 'с' (custom — архив .tar.gz), 't' (tar — архив .tar), 'p' (plain — текстовый файл без сжатия, как правило, .sql);
• путь_к_дампу — путь сохранения для файла дампа и имя файла;
• имя_базы — имя базы данных, для которой создается резервная копия.

Например:

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

После выполнения команды будет запрошен пароль пользователя Postgres, указанного в команде (в примере — tmweb). 

Восстановление из дампа

Импорт дампов, сохраненных в форматах .tar.gz и .tar, выполняется с помощью pg_restore:

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

Параметры:

• хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• формат_дампа — формат, в котором был сохранен дамп; необходимо указать 'с' для архива .tar.gz и 't' для архива .tar;
• имя_базы — имя базы данных, в которую импортируется дамп;
• путь_к_дампу — путь к файлу дампа и имя файла.

Например:

pg_restore -h localhost -U tmweb -F c -d new_db /home/user/backups/dump.tar.gz

Для импорта дампов в формате .sql используется cat:

cat путь_к_дампу | psql -h хост -U имя_роли имя_базы

Параметры:

• путь_к_дампу — путь к файлу дампа и имя файла;
• хост — сервер, на котором располагается база; может быть указан localhost, IP-адрес или домен;
• имя_роли — имя пользователя PostgreSQL, под которым вы подключаетесь к базе;
• имя_базы — имя базы данных, в которую импортируется дамп.

Например:

cat /home/user/backups/dump.sql | psql -h localhost -U tmweb new_db

Работа с шаблонами баз данных

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

Template0 и template1 — это шаблоны баз данных, из которых в дальнейшем будут создаваться пользовательские БД.

Фактически, когда вы выполняете команду CREATE DATABASE, Postgres создает клон базы template1. Если внести изменения в template1, они будут наследоваться всеми новыми создаваемыми базами. Это позволяет, например, добавить в template1 необходимые вам таблицы с данными или установить расширения, после чего не потребуется добавлять их для каждой новой базы.

Обратите внимание, что для установки расширений необходимо подключиться к template1 от суперпользователя (postgres или другой роли, имеющей данные привилегии).

Подключитесь к шаблону template1:

\c template1

И установите расширение:

CREATE EXTENSION название_расширения;

Например, если вы установите в template1 расширение pgcrypto, то в дальнейшем, при выполнении CREATE DATABASE, новые базы будут создаваться с уже установленным pgrypto.

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

Чтобы создать «чистую базу на основе template0, нужно выполнить:

CREATE DATABASE имя_базы TEMPLATE template0;

Также template0 необходима, если вам нужно внести изменения в кодировку или локаль создаваемой базы данных. По умолчанию (при использовании template1) эти изменения невозможны.

В этом случае нужно при создании базы указать template0 в качестве шаблона и указать требуемую кодировку и/или локаль, например:

CREATE DATABASE имя_базы TEMPLATE template0 ENCODING 'SQL_ASCII';

Пересоздание template1

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

Сначала необходимо указать, что template1 не является шаблоном, чтобы удаление стало возможно:

UPDATE pg_database SET datistemplate = false WHERE datname = 'template1';

Далее удалить template1:

DROP DATABASE template1;

И создать базу template1 заново, указав, что она будет являться шаблоном:

CREATE DATABASE template1 OWNER postgres TEMPLATE template0 is_template true;

Параметры и конфигурационные файлы

Узнать расположение конфигурационного файла (как правило, размещается по пути: /etc/postgresql/версия/main/postgresql.conf) можно с помощью:

SHOW config_file;

Узнать значение какого-либо параметра Postgres:

SHOW параметр;

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

SELECT * FROM pg_settings WHERE name = 'параметр';

Например:

SELECT * FROM pg_settings WHERE name = 'max_connections';

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

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

Вывести все параметры терминального командного меню Postgres:

psql --help

Просмотр баз данных из командной строки Linux:

psql -l

Список команд psql выводится с помощью \?:

postgres=# \?

Список SQL-команд (запросов):

postgres=# \h

Справка по конкретному SQL- запросу:

postgres=# \h запрос

# Например:
postgres=# \h CREATE TABLE

 Информация о подключении:

postgres=# \conninfo

Выполнение shell-команд из командной строки Postgres: 

postgres=# \! команда_shell

# Например, чтобы вывести директорию нахождения:
postgres=# \! pwd

 

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

postgres=# \c имя_базы