Установка и настройка 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, который предоставляет дополнительные функции и утилиты для работы с СУБД.

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

Пользователи и Роли

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

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

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

sudo su - postgres

Затем, запустите интерактивный интерфейс PostgreSQL:

psql

Для выхода из интерфейса PostgreSQL, введите команду \q:

postgres=# \q

PostgreSQL предполагает создание отдельной роли для каждой базы данных, чтобы облегчить управление правами доступа. Также, при совпадении имен ролей и пользователей 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, и если вам нужно выйти из командной строки Postgres, введите \q.

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

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

dropuser имя_роли

Также можно использовать команду в консоли Postgres:

DROP ROLE имя_роли;

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

Чтобы изменить пароль одной из ролей, выполните следующие шаги:

1. Подключитесь к Postgres от имени суперпользователя (например, postgres) или другой роли с соответствующими привилегиями.

2. Выполните команду:

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 имя_базы;

Для просмотра списка баз данных в консоли Postgres выполните команду \l:

postgres=# \l

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

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

psql

Например, если на сервере есть пользователь "linuxuser" и роль "linuxuser" в 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, которые позволяют вам создавать резервные копии баз данных из командной строки (pg_dump) и восстанавливать данные из этих копий (pg_restore).

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

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

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

Где параметры означают:

• хост — сервер, на котором расположена база данных, может быть указан как localhost, IP-адрес или доменное имя;
• имя_роли — имя пользователя PostgreSQL, от имени которого вы хотите создать резервную копию;
• формат_дампа — формат, в котором будет сохранен дамп, указывается как 'с' (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, указанного в параметре -U (в данном случае, 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, PostgreSQL создает новую базу данных, основанную на шаблоне template1. Если вы вносите изменения в шаблон template1, эти изменения будут отражаться во всех новых базах данных, созданных на его основе. Это удобно, например, для добавления таблиц и данных в template1, так как они автоматически будут доступны во всех новых базах.

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

Для подключения к шаблону template1 выполните:

\c template1

Затем вы можете установить расширение, например:

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

Например, если вы устанавливаете расширение pgcrypto в template1, все новые базы данных будут автоматически иметь это расширение при создании.

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

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

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

Если вам нужно указать конкретную кодировку или локаль при создании базы данных, используйте template0 следующим образом:

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

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

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

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

Затем удалите базу template1:

DROP DATABASE template1;

И, наконец, создайте новую базу template1 на основе template0:

CREATE DATABASE template1 OWNER postgres TEMPLATE template0 is_template true;

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

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

SHOW config_file;

Чтобы узнать значение определенного параметра PostgreSQL, используйте следующую команду:

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-команд (запросов), используйте \h:

postgres=# \h

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

postgres=# \h запрос

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

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

postgres=# \conninfo

Если вам нужно выполнить команды оболочки (shell) из командной строки Postgres, используйте \!:

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

# Например, чтобы вывести текущий рабочий каталог:
postgres=# \! pwd

Для подключения к другой базе данных используйте:

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