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 4, как показано на рисунке.
Install for both desktop and web modes sudo apt install pgadmin4 Install for desktop mode only sudo apt install pgadmin4-desktop Install for web mode only sudo apt install pgadmin4-web Configure the webserver, if you installed 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
Как использовать роли и базы данных 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, чтобы применить изменения:
sudo systemctl restart postgresql
Как использовать PostgreSQL на Ubuntu
После того как все настроено, вы можете получить доступ к системной учетной записи postgres с помощью следующей команды, где флаг -i указывает sudo на запуск оболочки, указанной в записи базы данных паролей целевых пользователей, в качестве оболочки для входа в систему.
sudo -i -u postgres psql # to launch the postgres shell program
Чтобы получить доступ к оболочке postgres напрямую, без предварительного доступа к учетной записи пользователя postgres, выполните команду:
sudo -u postgres psql
Вы можете выйти из оболочки postgres, набрав \q или нажав Ctrl + D.
\q
Создание ролей базы данных PostgreSQL
Создайте новую роль пользователя с помощью следующей команды:
CREATE ROLE tecmint;
Чтобы создать роль с атрибутом LOGIN (роли с атрибутом LOGIN можно считать такими же, как пользователи базы данных):
CREATE ROLE tecmint LOGIN;
Или используйте команду CREATE USER, которая по умолчанию принимает на себя функцию входа в систему:
CREATE USER tecmint;
Создание роли с паролем
Роль также можно создать с паролем, что очень важно, если вы настроили метод аутентификации клиента на использование зашифрованных паролей:
CREATE ROLE tecmint WITH LOGIN PASSWORD 'secure_password_here';
Или с помощью синтаксиса CREATE USER:
CREATE USER tecmint WITH PASSWORD 'secure_password_here';
Создание роли с дополнительными привилегиями
Вы можете создать роль с привилегиями суперпользователя (используйте осторожно):
CREATE ROLE admin WITH LOGIN PASSWORD 'admin_password' SUPERUSER;
Или создайте роль, которая может создавать базы данных:
CREATE ROLE developer WITH LOGIN PASSWORD 'dev_password' CREATEDB;
Список существующих ролей баз данных PostgreSQL
Чтобы перечислить существующие роли пользователей, используйте любую из этих команд:
\du -- shows actual users with details
Или:
SELECT rolname FROM pg_roles;
Для получения более подробной информации:
\du+
Изменение ролей базы данных PostgreSQL
Чтобы изменить пароль роли:
ALTER ROLE tecmint WITH PASSWORD 'new_password';
Чтобы предоставить привилегии суперпользователя существующей роли:
ALTER ROLE tecmint WITH SUPERUSER;
Чтобы отозвать привилегии суперпользователя:
ALTER ROLE tecmint WITH NOSUPERUSER;
Снятие роли базы данных PostgreSQL
Чтобы отказаться от существующей роли пользователя, используйте команду DROP ROLE:
DROP ROLE tecmint;
Примечание: Вы не можете отказаться от роли, которая владеет объектами базы данных, вы должны сначала переназначить или отказаться от этих объектов.
Создание базы данных PostgreSQL
Создав роль с определенным именем (например, tecmint user), вы можете создать базу данных, которая будет управляться этой ролью:
CREATE DATABASE tecmint;
Чтобы создать базу данных, принадлежащую определенной роли:
CREATE DATABASE tecmint OWNER tecmint;
Чтобы создать базу данных с определенной кодировкой:
CREATE DATABASE tecmint ENCODING 'UTF8' LC_COLLATE='en_US.UTF-8' LC_CTYPE='en_US.UTF-8' OWNER tecmint;
Предоставление привилегий роли
После создания базы данных предоставьте все привилегии роли:
GRANT ALL PRIVILEGES ON DATABASE tecmint TO tecmint;
Теперь, чтобы управлять базой данных tecmint, войдите в оболочку postgres с ролью tecmint:
psql -U tecmint -d tecmint
Если вам будет предложено ввести пароль, введите пароль, который вы установили для роли.
Создание таблицы PostgreSQL
Создайте тестовую таблицу authors, в которой хранится информация об авторах TecMint.com:
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
('Ravi Saive', 'Mumbai', '2012-08-15'),
('Aaron Kili', 'Nairobi', '2014-03-20'),
('Matei Cezar', 'Bucharest', '2015-06-10');
Запрос данных из таблицы PostgreSQL
Чтобы просмотреть данные, хранящиеся в таблице, выполните команду SELECT:
SELECT * FROM authors;
Для конкретных столбцов:
SELECT name, city FROM authors;
С фильтрацией:
SELECT * FROM authors WHERE city = 'Mumbai';
С упорядочиванием:
SELECT * FROM authors ORDER BY joined_on DESC;
Обновление данных в таблице PostgreSQL
Чтобы изменить существующие данные:
UPDATE authors SET city = 'Delhi' WHERE name = 'Ravi Saive';
Удаление данных из таблицы PostgreSQL
Чтобы удалить определенные строки:
DELETE FROM authors WHERE name = 'Ravi Saive';
Список таблиц базы данных 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 tecmint;
Предупреждение: Это навсегда удаляет все данные в базе данных.
Переход на другую базу данных 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 = 256MB # 25% of RAM effective_cache_size = 1GB # 50-75% of RAM maintenance_work_mem = 64MB checkpoint_completion_target = 0.9 wal_buffers = 16MB default_statistics_target = 100 random_page_cost = 1.1 effective_io_concurrency = 200 work_mem = 4MB
После внесения изменений перезапустите PostgreSQL:
sudo systemctl restart postgresql
Резервное копирование и восстановление баз данных PostgreSQL
Для резервного копирования одной базы данных:
pg_dump -U postgres tecmint > tecmint_backup.sql
Для резервного копирования всех баз данных.
pg_dumpall -U postgres > all_databases_backup.sql
Восстановление базы данных.
psql -U postgres tecmint < tecmint_backup.sql
Справочник по полезным командам PostgreSQL
Здесь представлен краткий справочник часто используемых команд PostgreSQL:
| Команда | Описание |
|---|---|
\l или \list |
Список всех баз данных |
\c dbname |
Подключиться к базе данных |
\dt |
Список всех таблиц |
\d tablename |
Описать структуру таблицы |
\du |
Перечислите все роли/пользователей |
\dn |
Перечислите все схемы |
\df |
Список всех функций |
\dv |
Список всех представлений |
\timing |
Переключение синхронизации запросов |
\x |
Переключение расширенного отображения |
\i filename |
Выполнение команд из файла |
\q |
Выход из psql |
\h |
Справка по командам SQL |
\? |
Помощь по командам psql |
Заключение
Вот и все! В этой статье мы рассказали, как установить и использовать систему управления базами данных PostgreSQL на Ubuntu 24.04 LTS.
Мы рассмотрели установку, управление пользователями, работу с базами данных, настройку удаленного доступа, настройку производительности и стратегии резервного копирования. Вы можете прислать нам свои вопросы или мысли в комментариях ниже.
Для получения дополнительной информации обратитесь к официальной документации PostgreSQL 18 или изучите полезные веб-сайты для изучения PostgreSQL.
Комментарии (0)