Установка PostgreSQL в Ubuntu: настройка и работа с базой данных

PostgreSQL — объектно-реляционная СУБД с открытым исходным кодом. PostgreSQL используют в e-commerce, аналитике, стартапах и везде, где важны производительность и надежность. По информации за 2024 год, PostgreSQL — вторая по популярности в России, ее используют на 97% чаще Oracle и на 118% чаще MySQL.

Рассмотрим плюсы PostgreSQL:

Активное сообщество разработчиков, которые выпускают регулярные обновления, закрывают уязвимости, добавляют новые функции.

Масштабируемость. В PostgreSQL есть партицирование и шардинг данных, что позволяет дробить большие таблицы для удобства управления, распределять данные по серверам. Доступны холодная и горячая репликация, чтобы настраивать доступ к данным с минимальным временем отклика. Логическая репликация помогает в реальном времени обновлять отдельные таблицы и схемы.

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

Надежность. PostgreSQL соответствует принципам ACID: атомарность, непротиворечивость, изоляция и устойчивость. Это говорит о том, что СУБД работает стабильно и предсказуемо.

Гибкость в моделировании больших структур данных. PostgreSQL — объектно-реляционная модель, которая сочетает стабильность реляционных таблиц и возможности объектно-ориентированного подхода. Данные представляются как объекты с атрибутами и методами, сохраняя при этом преимущества работы с таблицами и связями между ними.

Кроссплатформенность. PostgreSQL работает на Linux, Ubuntu, Windows, macOS и других ОС. Еще есть много драйверов для интеграции с приложениями на разных языках программирования (ЯП). Доступны драйверы для популярных ЯП, например: Python, Java, JavaScript, Go, C#, PHP, Ruby. PostgreSQL можно развернуть на собственном железе, виртуальной машине или в облаке.

Работа с большими объемами данных. В СУБД нет ограничений по размеру баз данных, а максимальный размер таблицы — 32 ТБ. PostgreSQL используют в проектах с терабайтами информации.

Открытый исходный код. PostgreSQL — бесплатное open source решение: не нужно платить, даже если в планах использовать решение в коммерческих целях. У вас есть доступ к исходному коду, возможность модифицировать его под свои задачи.

Безопасность. Защита данных реализована комплексно. PostgreSQL поддерживает множество механизмов аутентификации: пароли, Kerberos, LDAP, GSSAPI, SSPI, PAM. Гибкая ролевая модель позволяет тонко настраивать права доступа: от уровня всей базы данных до отдельных таблиц, столбцов и даже строк.

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

• ОС: Linux, для Ubuntu лучше использовать версию 20.04.

• Одноядерный процессор с тактовой частотой 1 ГГц. Но лучше многоядерный процессор от 2 ГГц и выше.

• Оперативная память от 4 ГБ — для небольших систем, 16 ГБ и более — для высоконагруженных баз данных.

• Место на диске — от 200 МБ для установки системы и дополнительное пространство, чтобы хранить данные. Хорошо, если это будет SSD-диск с достаточным объемом для хранения данных и журналов транзакций.

Еще потребуется поддержка SQL, пакеты для работы с Python, Perl или Java, если интеграция с ними необходима, и сетевые порты: необходимо открыть порт 5432 для доступа к серверу PostgreSQL.

Шаг 1. Обновите ОС до актуальной версии. Для этого выполните команды sudo apt update && sudo apt upgrade -y. Обновление нужно, чтобы Ubuntu была совместима с PostgreSQL. Также лучше удалить старые версии СУБД, чтобы не было конфликта при установке.

Шаг 2. Установите PostgreSQL. Ubuntu предоставляет PostgreSQL в своем официальном репозитории. Для установки выполните команду sudo apt install postgresql postgresql-contrib. Пакет postgresql содержит саму систему управления, а postgresql-contrib — дополнительные модули и утилиты, которые расширяют функции PostgreSQL.

После установки проверьте статус командой sudo systemctl status postgresql. Если СУБД не запустилась автоматически, сделайте это вручную: sudo systemctl start postgresql. Потом включите автоматический запуск при старте системы командой sudo systemctl enable postgresql. 

Шаг 3. Проверьте версию установленной PostgreSQL. Введите команду psql --version. После этого на мониторе покажется актуальная версия: «psql (PostgreSQL) 17.2».

В документации PostgreSQL есть информация о дополнительных настройках и модулях для каждой версии. А еще кластеры PostgreSQL можно создать в облачном сервисе Managed PostgreSQL. Вам будут доступны удобный интерфейс для создания, управления и мониторинга кластерами БД, масштабирование в пару кликов, автоматическое резервное копирование и восстановление.

Первым делом создайте сам массив данных. Для этого PostgreSQL по умолчанию создает пользователя с именем postgres. Ему даются права администратора, который может управлять БД. Чтобы работать с базой данных под postgres, введите команду sudo -i -u postgres.

Теперь разберемся с конфигурационными файлами PostgreSQL, которые нужны, чтобы управлять параметрами баз данных. Они лежат в каталоге, обычно расположенном в /etc/postgresql/{version}/main/ или /var/lib/postgresql/{version}/main/ — зависит от конфигурации системы.

Основные конфигурационные файлы:

• postgresql.conf — можно настроить, допустим, порты подключения, размер буферов, регистрацию: sudo nano /etc/postgresql/{version}/main/postgresql.conf.

• pg_hba.conf — файл для управления доступами к БД. Здесь настраиваются разрешения для подключения пользователей с разными хостами. Можно разрешить доступ только с локального хоста или с определенных IP: sudo nano /etc/postgresql/{version}/main/pg_hba.conf.

• pg_ident.conf — файл, который объединяет системных пользователей с пользователями PostgreSQL. Это нужно, чтобы управлять доступом и назначать гибкие права на уровне системы включения.

Изменения, которые вносите через конфигурационные файлы, не вступят в силу сами по себе. Перезапустите сервер PostgreSQL командой sudo systemctl restart postgresql.

Разбираемся, какие команды использовать, чтобы инициировать БД, подключиться к ней, настроить доступы и начать работу.

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

Создать базу данных можно двумя способами: с помощью утилиты командной строки psql или инструмента createdb. Рассмотрим каждый.

Как создать БД с помощью psql: 

1. переключитесь на пользователя PostgreSQL: sudo -i -u postgres;

2. войдите в консоль psql: psql;

3. создайте базу данных: CREATE DATABASE имя_базы_данных;

4. выйдите из консоли: \q.

Чтобы создать БД с помощью createdb, из консоли Ubuntu от имени пользователя PostgreSQL выполните команду: createdb имя_базы_данных.

Подключение к базе данных с помощью psql

Чтобы подключиться к уже созданной БД, выполните команду: psql -d имя_базы_данных -U postgres. Здесь -d — имя базы, -U — пользователь, который подключается к БД. После подключения можно выполнять SQL-запросы, управлять таблицами и данными.

Команды для удаления БД и настроек доступа

Команда \l или psql -c "\l" покажет базы данных, которые есть. Удалить одну из них можно командой DROP DATABASE имя_базы_данных. Чтобы команда сработала, закройте удаляемую БД.

Настроить права доступа можно по следующему алгоритму:

• Создать пользователя: CREATE USER имя_пользователя WITH PASSWORD 'пароль'.

• Назначить роли владельца базы данных: GRANT ALL PRIVILEGES ON DATABASE имя_базы_данных TO имя_пользователя.

• Изменить роли, например, дать права суперпользователя: ALTER USER имя_пользователя WITH SUPERUSER. Суперпользователь способен управлять базами данных, обычными пользователями и доступами. Этому пользователю доступны те функции, которые закрыты для остальных.

• Удалить пользователя: DROP USER имя_пользователя.

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

Создание таблиц

Укажите имена столбцов, типы данных, которые будут в столбцах. А еще дополнительные параметры, допустим, уникальность и ограничения:

Где:

• id SERIAL PRIMARY KEY отвечает за автоинкрементное поле, у которого есть идентификатор. Автоинкрементное поле — это колонка в БД, значение которой автоматически увеличивается с каждой новой записью. Поле нужно, чтобы автоматически создавать уникальные идентификаторы, а не делать это вручную для каждого нового ряда.

• name VARCHAR(100) NOT NULL столбец для имени, где NOT NULL запрещает оставлять поле пустым.

• created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP — столбец для даты создания записи. Если дату не указать, автоматически подставится текущая.

Чтобы проверить, создалась ли таблица, используйте команду \d users.

Добавление, изменение и удаление данных в таблице

Рассмотрим команды, которые нужны для совершения действий в таблице, и покажем примеры:

• Вставить новую запись в таблицу — INSERT.

• Поменять данные — UPDATE.

• Удалить данные — DELETE. Будьте внимательны, если не указать условие WHERE, будут удалены все строки в таблице.

• Добавить столбец — ADD COLUMN.

• Удалить столбец — DROP COLUMN.

• Изменить тип данных столбца, например, увеличить длину поля для имени — ALTER COLUMN.

• Удалить таблицу — DROP TABLE. Учтите, что отменить действие нельзя. Если не уверены, существует ли таблица, которую хотите удалить, воспользуйтесь командой DROP TABLE IF EXISTS users.

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

Как создать резервные копии базы данных, или экспорт дампа, чтобы сохранить данные в одном файле. Для резервного копирования используется утилита pg_dump, с ее помощью создают дампы БД в формате SQL или архивном формате.

Команда может выглядеть так: pg_dump -U <пользователь> -F c -d <имя_базы> -f /путь/к/резервной_копии.dump, где:

• -U <пользователь> — имя пользователя PostgreSQL;

• -F c — формат дампы, в примере он архивный;

• -d <имя_базы> — название БД, которую резервируем;

• -f /путь/к/резервной_копии.dump — путь, где сохранится копия.

Как восстановить данные, если произошел сбой . Здесь нужна утилита pg_restore, с ее помощью импортируют данные из дампа. 

Пример команды: pg_restore -U <пользователь> -d <имя_новой_базы> -1 /путь/к/резервной_копии.dump, где:

• -U <пользователь> — имя пользователя PostgreSQL;

• -d <имя_новой_базы> — имя БД, куда будет загружена информация;

• -1 — выполнение дампа одной транзакцией для предотвращения частичного повреждения;

• /путь/к/резервной_копии.dump — путь к файлу резервной копии.

Перед восстановлением убедитесь, что нужная БД создана и доступна. Чтобы заменить существующую базу, сначала удалите старую, а после создайте новую с тем же названием.

Разберемся, как настроить удаленное подключение в базе данных.

Включение внешнего доступа и безопасных соединений

1. Откройте конфигурацию файла postgresql.conf и измените параметр значения listen_addresses. Так сервер сможет принимать запросы на указанных IP-адресах или на всех интерфейсах listen_addresses = '*'.

2. Затем настройте файл pg_hba.conf, он лежит по пути /etc/postgresql/<версия>/main/pg_hba.conf. Добавьте строки Управляющие подключения с нужными IP-адресами, например, host all all 192.168.1.0/24 md5.

3. Активируйте SSL, чтобы шифровать трафик между клиентом и сервером. Включите параметр SSL в postgresql.conf командой ssl = on.

4. Убедитесь, что на сервере установлены SSL-сертификаты. Сертификат сервера server.crt и закрытый ключ server.key находятся в каталоге /etc/postgresql/<версия>/main/. Право доступа к ключу проверьте командой sudo chmod 600 /etc/postgresql/<версия>/main/server.key.

5. Убедитесь, что для пользователей установлены надежные пароли. Для этого выполните команду: ALTER USER <username> WITH PASSWORD '<secure_password>'.

Открытие порта и настройка брандмауэра для доступа

По умолчанию PostgreSQL для подключения использует порт 5432. Чтобы сервер был доступен:

1. Замените основной конфигурационный файл PostgreSQL командой sudo nano /etc/postgresql/<версия>/main/postgresql.conf. Найдите текст listen_addresses и замените его: listen_addresses = '*'. Это нужно, чтобы к серверу подключались с любых IP-адресов. Перезагрузите СУБД: sudo systemctl restart postgresql.

2. Настройте брандмауэр. Откройте порт 5432: sudo ufw allow 5432/tcp. Убедитесь, что брандмауэр активирован: sudo ufw enable.

3. Для дополнительной безопасности ограничьте доступ к порту для определенных IP-адресов: sudo ufw allow from <IP_адрес> to any port 5432.

После проверьте удаленное подключение с помощью клиентского PostgreSQL или утилиты psql. Для этого укажите IP-адрес сервера и порт 5432. Если все настроено правильно, сервер PostgreSQL станет доступен при удаленном подключении.

PostgreSQL оптимизируют через настройки параметров, которые влияют на производительность системы. Для этого нужен файл конфигурации postgresql.conf, обычно он расположен по пути /etc/postgresql/<версия>/main/postgresql.conf.

Что нужно настроить:

• shared_buffers показывает объем оперативной памяти, который дадим PostgreSQL для буферизации данных. Установите значение в пределах 25–40% от объема оперативной памяти сервера: shared_buffers = 2GB.

• work_mem — объем памяти для выполнения операций по сортировке и хешированию. Установите значение побольше: work_mem = 64MB.

• maintenance_work_mem — объем памяти для операций вакуумирования и анализа. Для основной БД увеличьте это значение, чтобы процессы отрабатывались быстрее: maintenance_work_mem = 512MB.

• effect_cache_size указывает объем файловой системы, которую PostgreSQL может использовать для кеширования. Этот параметр помогает оптимизировать выбор запросов. Рекомендуется устанавливать 50–75% доступной оперативной памяти: effective_cache_size = 6GB.

• max_connections определяет максимальное количество одновременных соединений. Для высоконагруженных систем увеличьте это значение с учетом возможностей сервера: max_connections = 200.

После изменения параметров перезагрузите СУБД: sudo systemctl restart postgresql.

Чтобы удалить PostgreSQL и ее данные:

1. Введите команду, чтобы удалить все версии PostgreSQL: sudo apt-get --purge remove postgresql*.

2. Удалите дополнительные пакеты, например postgresql-client или postgresql-contrib, если они установлены. Для этого нужны команды: sudo apt-get --purge remove postgresql-client* или sudo apt-get --purge remove postgresql-contrib*.

3. PostgreSQL сохраняет данные в каталоге /var/lib/postgresql. Если нужно что-то скопировать, выполните команду: sudo rm -rf /var/lib/postgresql.

4. Удалите конфигурационные файлы PostgreSQL, которые обычно хранятся в /etc/postgresql: sudo rm -rf /etc/postgresql.

После удаления PostgreSQL убедитесь, что в системе не осталось никаких файлов:

1. Проверьте, запущен ли PostgreSQL, с помощью команды: ps aux | grep postgres. Если какие-либо процессы еще работают, завершите их вручную: sudo kill -9 <PID>.

2. Удалите ненужные зависимости после деинсталляции PostgreSQL командами: sudo apt-get autoremove и sudo apt-get autoclean.

3. Выполните поиск с помощью команды: dpkg -l | grep postgresql. Если поиск ничего не дает, значит, СУБД удалена. Если что-то осталось, удалите вручную.

4. Очистите журналы PostgreSQL, если они хранятся в /var/log/postgresql: sudo rm -rf /var/log/postgresql.

5. Если PostgreSQL создал пользователя или группу, удалите их. Понадобятся команды sudo deluser postgres и sudo delgroup postgres.

• PostgreSQL — объектно-реляционная СУБД с открытым исходным кодом, подходит и для команд с небольшими проектами, и для корпораций.

• Создавать базы данных и управлять ими в PostgreSQL на Ubuntu можно через команды CREATE DATABASE, DROP DATABASE.

• В PostgreSQL можно создавать таблицы, структурировать данные, управлять содержимым и настраивать поля под задачи. Для этого предусмотрены команды CREATE TABLE, INSERT, DROP COLUMN.

• Для большей производительности дополнительно настройте память и подключение. А для удаленного доступа используйте SSL-соединение, чтобы данные оставались в безопасности.

• Удаление PostgreSQL из Ubuntu — это пошаговый процесс. После удаления проверьте, чтобы не осталось файлов.