Как установить и использовать PostgreSQL 18 в Ubuntu 24.04 LTS

PostgreSQL (сокращенноPostgres) — это мощная, продвинутая, высокопроизводительная и стабильная система баз данных с реляционными данными с открытым исходным кодом, который расширяет возможности языка SQL и включает широкий спектр функций для безопасного хранения данных и управления ими.

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

Postgres также обладает широкими возможностями расширения за счёт таких функций, как расширенная индексация, полнотекстовый поиск, а также имеет API, позволяющие разрабатывать собственные решения для хранения данных.

В этой статье мы расскажем, как установить PostgreSQL 18 (который был выпущен 25 сентября 2025 года) на сервер Ubuntu 24.04 LTS, а также рассмотрим основные способы его эффективного использования.

Как установить PostgreSQL в Ubuntu 24.04

PostgreSQL по умолчанию входит в состав всех версий Ubuntu. Однако в Ubuntu входит специальный «снимок» PostgreSQL, который остаётся неизменным на протяжении всего жизненного цикла этой версии Ubuntu.

Если вам нужен доступ к более новым версиям PostgreSQL, вы можете воспользоваться следующим скриптом для автоматической настройки репозитория, который автоматически настраивает официальный репозиторий PostgreSQL Apt.

sudo apt install -y postgresql-common ca-certificates
sudo /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Теперь обновите список пакетов и установите PostgreSQL (на сегодняшний день последняя версия — PostgreSQL 18):

sudo apt update
sudo apt install postgresql-18 postgresql-contrib-18

Установка pgAdmin 4 для администрирования PostgreSQL

Если вы предпочитаете графический интерфейс для управления базами данных PostgreSQL, вы можете установить pgAdmin 4 — официальный инструмент управления PostgreSQL, который позволяет управлять базами данных, пользователями, запросами и многим другим с помощью веб-панели.

В Ubuntu 24.04 pgAdmin 4 недоступен в репозиториях по умолчанию, поэтому мы будем использовать официальный репозиторий pgAdmin.

sudo apt install -y curl ca-certificates gnupg
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

После добавления репозитория pgAdmin вы можете установить pgAdmin 4, как показано на рисунке.

# Установка для настольного и веб-режимов
sudo apt install pgadmin4

# Установка только для настольного режима
sudo apt install pgadmin4-desktop

# Установка только для веб-режима
sudo apt install pgadmin4-web

# Настройка веб-сервера, если вы установили pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

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

sudo systemctl status postgresql

Чтобы включить запуск PostgreSQL при загрузке (если он ещё не включен):

sudo systemctl enable postgresql

После этого вы можете открыть pgAdmin в браузере по адресу:

http://127.0.0.1/pgadmin4

Инструмент управления pgAdmin для PostgreSQL

Как использовать роли и базы данных в PostgreSQL

В PostgreSQL аутентификация клиента контролируется файлом конфигурации /etc/postgresql/18/main/pg_hba.conf (номер версии может отличаться в зависимости от вашей установки).

Для администратора базы данных метод аутентификации по умолчанию — "peer", то есть он получает имя пользователя в операционной системе клиента и проверяет, совпадает ли оно с именем пользователя в запрошенной базе данных, чтобы разрешить доступ для локальных подключений.

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

sudo nano /etc/postgresql/18/main/pg_hba.conf

Понимание Ролей PostgreSQL

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

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

Роли могут:

• Собственные объекты базы данных (таблицы, представления, функции и т. д.).
• Назначьте другим ролям права доступа к этим объектам.
• Предоставьте членство в одной роли другой роли (наследование ролей).

Включите шифрование паролей для ролей PostgreSQL

Чтобы настроить использование зашифрованных паролей вместо одноранговой аутентификации, необходимо изменить файл pg_hba.conf . Измените метод аутентификации с peer на scram-sha-256 (современный безопасный метод) или md5 для аутентификации по паролю.

sudo nano /etc/postgresql/18/main/pg_hba.conf

Найдите строки, которые выглядят так:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     peer

И изменить на:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     scram-sha-256

Это позволяет peer аутентифицировать postgres пользователя, но требует ввода пароля для других пользователей.

Настройте роли PostgreSQL с использованием зашифрованных паролей

Затем перезапустите службу PostgreSQL, чтобы изменения вступили в силу:

sudo systemctl restart postgresql

Как использовать PostgreSQL в Ubuntu

После того как всё будет настроено, вы сможете получить доступ к системной учётной записи postgres с помощью следующей команды, где флаг -i указывает sudo на необходимость запуска оболочки, указанной в базе данных паролей целевого пользователя, в качестве оболочки для входа в систему.

sudo -i -u postgres
psql # для запуска программы оболочки postgres

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

sudo -u postgres psql

Чтобы выйти из оболочки postgres, введите \q или нажмите Ctrl+D.

\q

CREATE USER andreyex;

Создайте роль с паролем

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

CREATE ROLE andreyex WITH LOGIN PASSWORD 'здесь_надежный_пароль';

Или с помощью синтаксиса CREATE USER:

CREATE USER andreyex WITH PASSWORD 'здесь_надежный_пароль';

Создайте роль с дополнительными привилегиями

Вы можете создать роль с правами суперпользователя (используйте с осторожностью):

CREATE ROLE andreyex WITH LOGIN PASSWORD 'здесь_надежный_пароль';

Или создайте роль, которая сможет создавать базы данных:

CREATE USER andreyex WITH PASSWORD 'здесь_надежный_пароль';

Перечислите Существующие роли базы данных PostgreSQL

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

\du — показывает реальных пользователей с подробной информацией

Или:

SELECT rolname FROM pg_roles;

Чтобы просмотреть более подробную информацию:

\du+

Изменение ролей базы данных PostgreSQL

Чтобы изменить пароль от роли:

ALTER ROLE andreyex WITH PASSWORD 'новый_пароль';

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

ALTER ROLE andreyex WITH SUPERUSER;

Чтобы отозвать права суперпользователя:

ALTER ROLE andreyex WITH NOSUPERUSER;

Удаление роли базы данных PostgreSQL

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

DROP ROLE andreyex;

Примечание: вы не можете удалить роль, которой принадлежат объекты базы данных. Сначала необходимо переназначить или удалить эти объекты.

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

После создания роли с определённым именем (например, tecmint user) вы можете создать базу данных, которой будет управлять эта роль.

CREATE DATABASE andreyex;

Чтобы создать базу данных, принадлежащую определённой роли:

CREATE DATABASE andreyex OWNER andreyex;

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

CREATE DATABASE andreyex ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' OWNER andreyex;

Предоставление привилегий роли

После создания базы данных предоставьте роли все необходимые права:

GRANT ALL PRIVILEGES ON DATABASE andreyex TO andreyex;

Теперь для управления базой данных tecmint войдите в оболочку postgres под ролью tecmint:

psql -U andreyex -d andreyex

Если вам будет предложено ввести пароль, введите пароль, который вы установили для этой роли.

Создайте таблицу PostgreSQL

Мы создадим тестовую таблицу под названием authors, в которой будет храниться информация об авторах:

CREATE TABLE authors (
    code      SERIAL PRIMARY KEY,
    name      VARCHAR(40) NOT NULL,
    city      VARCHAR(40) NOT NULL,
    joined_on DATE NOT NULL
);

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

Вставка данных в таблицу PostgreSQL

После создания таблицы заполните её некоторыми данными:

INSERT INTO authors (name, city, joined_on) VALUES 
    ('Andrey Ex', 'Moscow', '2014-02-02'),
...

Запрашивать данные из таблицы PostgreSQL

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

SELECT * FROM authors;

Для определенных столбцов:

SELECT name, city FROM authors;

С фильтрованием:

SELECT * FROM authors WHERE city = 'Moscow';

С заказом:

SELECT * FROM authors ORDER BY joined_on DESC;

Обновите данные в таблице PostgreSQL

Чтобы изменить существующие данные:

UPDATE authors SET city = 'Moscow' WHERE name = 'Andrey Ex';

Удаление данных из таблицы PostgreSQL

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

DELETE FROM authors WHERE name = 'Andrey Ex';

Список таблиц базы данных PostgreSQL

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

\dt

Для получения более подробной информации:

\dt+

Чтобы увидеть структуру таблицы:

\d authors

Изменить структуру таблицы PostgreSQL

Чтобы добавить новый столбец в существующую таблицу:

ALTER TABLE authors ADD COLUMN email VARCHAR(100);

Чтобы удалить столбец:

ALTER TABLE authors DROP COLUMN email;

Чтобы переименовать столбец:

ALTER TABLE authors RENAME COLUMN code TO author_id;

Удаление/отмена таблицы PostgreSQL

Чтобы удалить таблицу в текущей базе данных:

DROP TABLE authors;

Чтобы удалить каскадно (удаляет зависимые объекты):

DROP TABLE authors CASCADE;

Перечислите Все базы данных PostgreSQL

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

SELECT datname FROM pg_database;

Или для получения подробного описания:

\list

Или стенография:

\l

Удаление базы данных PostgreSQL

Если вы хотите удалить базу данных:

DROP DATABASE andreyex;

Предупреждение: при этом все данные в базе будут безвозвратно удалены.

Переключиться на другую базу данных PostgreSQL

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

\connect database_name

Или стенография:

\c database_name

Настройка PostgreSQL для удаленного доступа

По умолчанию PostgreSQL принимает подключения только с localhost.

Чтобы разрешить удалённые подключения, вам нужно отредактировать файл postgresql.conf.

sudo nano /etc/postgresql/18/main/postgresql.conf

Найди линию:

#listen_addresses = 'localhost'

Измените его на:

listen_addresses = '*'

Затем отредактируйте файл pg_hba.conf.

sudo nano /etc/postgresql/18/main/pg_hba.conf

Добавьте строку, разрешающую подключения из вашей сети (замените 192.168.1.0/24 на название вашей сети):

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             192.168.1.0/24          scram-sha-256

Или разрешить доступ с любого IP-адреса (менее безопасно):

host    all             all             0.0.0.0/0               scram-sha-256

Разрешите доступ к PostgreSQL через брандмауэр:

sudo ufw allow 5432/tcp

Наконец, перезапустите PostgreSQL.

sudo systemctl restart postgresql

Базовая настройка производительности PostgreSQL

Для повышения производительности в Ubuntu 24.04 рассмотрите возможность изменения следующих настроек в postgresql.conf:

sudo nano /etc/postgresql/18/main/postgresql.conf

Рекомендуемые изменения (корректируйте в зависимости от объёма оперативной памяти вашего сервера):

shared_buffers = 256 МБ # 25 % оперативной памяти
effective_cache_size = 1 ГБ # 50–75 % оперативной памяти
maintenance_work_mem = 64 МБ
checkpoint_completion_target = 0,9
wal_buffers = 16 МБ
default_statistics_target = 100
random_page_cost = 1,1
effective_io_concurrency = 200
work_mem = 4 МБ

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

sudo systemctl restart postgresql

Резервное копирование и восстановление баз данных PostgreSQL

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

pg_dump -U postgres andreyex > andreyex_backup.sql

Для резервного копирования всех баз данных.

pg_dumpall -U postgres > all_databases_backup.sql

Чтобы восстановить базу данных.

psql -U postgres andreyex < andreyex_backup.sql

Полезная ссылка на команды PostgreSQL

Вот краткий список часто используемых команд PostgreSQL:

Заключение

Вот и всё! В этой статье мы рассказали, как установить и использовать систему управления базами данных PostgreSQL в Ubuntu 24.04 LTS.

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

Редактор: AndreyEx