Установка и базовое управление PostgreSQL

PostgreSQL — одна из самых мощных реляционных систем управления базами данных с открытым исходным кодом в мире. Благодаря соответствию ACID, расширенным типам данных, поддержке JSON и широким возможностям расширения она охватывает широкий спектр применений — от корпоративных приложений до небольших проектов. В этом руководстве рассматриваются установка, базовое управление и настройка PostgreSQL.

Установка на Ubuntu/Debian

Установка из репозитория пакетов

На системах Ubuntu и Debian PostgreSQL можно установить из официальных репозиториев:

hljs bash

# Обновить список пакетов
apt update

# Установить PostgreSQL
apt install -y postgresql postgresql-contrib

# Проверить статус службы
systemctl status postgresql

# Включить автозапуск
systemctl enable postgresql

Использование официального репозитория PostgreSQL

Для получения более новой версии можно добавить официальный репозиторий PostgreSQL:

hljs bash

# Установить необходимые инструменты
apt install -y curl ca-certificates

# Добавить ключ подписи PostgreSQL
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg

# Добавить репозиторий
echo 'deb https://apt.postgresql.org/pub/repos/apt '$(lsb_release -cs)'-pgdg main' > /etc/apt/sources.list.d/pgdg.list

# Обновить и установить
apt update
apt install -y postgresql-16

Установка на RHEL/CentOS/AlmaLinux

hljs bash

# Добавить официальный репозиторий PostgreSQL
dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Отключить встроенный модуль PostgreSQL
dnf -qy module disable postgresql

# Установить PostgreSQL 16
dnf install -y postgresql16-server postgresql16-contrib

# Инициализировать базу данных
/usr/pgsql-16/bin/postgresql-16-setup initdb

# Запустить и включить службу
systemctl enable --now postgresql-16

# Проверить статус службы
systemctl status postgresql-16

Проверка версии PostgreSQL

hljs bash

psql --version
# Вывод: psql (PostgreSQL) 16.x

# Информация о службе
psql -U postgres -c 'SELECT version();'

Переключение на пользователя postgres и подключение

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

hljs bash

# Переключиться на системного пользователя postgres
su - postgres

# Подключиться к консоли PostgreSQL
psql

# Подключиться напрямую от root
sudo -u postgres psql

# Подключиться к конкретной базе данных
sudo -u postgres psql -d mydb

Создание пользователей и баз данных

Создание нового пользователя

hljs sql

-- В консоли PostgreSQL
-- Создать нового пользователя
CREATE USER appuser WITH PASSWORD 'НадёжныйПароль123!';

-- Создать суперпользователя (использовать с осторожностью)
CREATE USER adminuser WITH PASSWORD 'АдминПароль!' SUPERUSER;

-- Список пользователей
\du

-- Изменить пароль пользователя
ALTER USER appuser WITH PASSWORD 'НовыйПароль456!';

-- Предоставить право создания баз данных
ALTER USER appuser CREATEDB;

Создание базы данных и назначение прав

hljs sql

-- Создать базу данных
CREATE DATABASE myapp OWNER appuser ENCODING 'UTF8' LC_COLLATE 'ru_RU.UTF-8' LC_CTYPE 'ru_RU.UTF-8' TEMPLATE template0;

-- Список баз данных
\l

-- Подключиться к базе данных
\c myapp

-- Предоставить полные права пользователю
GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

-- Права на схему (PostgreSQL 15+)
GRANT ALL ON SCHEMA public TO appuser;

-- Удалить базу данных
DROP DATABASE staraya_baza;

Основные SQL-команды

hljs sql

-- Создать таблицу
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  email VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Вставить данные
INSERT INTO users (username, email) VALUES ('ivan', 'ivan@example.com');

-- Запросить данные
SELECT * FROM users;
SELECT username, email FROM users WHERE id = 1;

-- Обновить данные
UPDATE users SET email = 'novyi@example.com' WHERE id = 1;

-- Удалить данные
DELETE FROM users WHERE id = 5;

-- Список таблиц
\dt

-- Просмотр структуры таблицы
\d users

-- Создать индекс
CREATE INDEX idx_users_email ON users(email);

Настройка удалённого подключения

По умолчанию PostgreSQL принимает подключения только с localhost. Для удалённого доступа необходимо отредактировать два файла.

1. Редактирование postgresql.conf

hljs bash

# Найти расположение файла конфигурации
sudo -u postgres psql -c 'SHOW config_file;'

# Редактировать
nano /etc/postgresql/16/main/postgresql.conf

hljs ini

# Изменить строку listen_addresses
listen_addresses = '*'

# Порт (по умолчанию 5432)
port = 5432

2. Редактирование pg_hba.conf

hljs bash

nano /etc/postgresql/16/main/pg_hba.conf

# Добавить строку для удалённых подключений (в конец файла)
# TYPE  DATABASE  USER      ADDRESS         METHOD
host    myapp     appuser   192.168.1.0/24  scram-sha-256
host    all       all       0.0.0.0/0       scram-sha-256

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

hljs bash

# Перезапустить службу
systemctl restart postgresql

# Открыть порт с помощью UFW
ufw allow 5432/tcp

# С помощью iptables
iptables -A INPUT -p tcp --dport 5432 -j ACCEPT

Открытие порта PostgreSQL (5432) в интернет является угрозой безопасности. По возможности используйте SSH-туннель или VPN. Если удалённый доступ необходим, ограничьте его конкретными IP-адресами.

Резервное копирование и восстановление

Резервное копирование с помощью pg_dump

hljs bash

# Резервная копия одной базы данных
pg_dump -U postgres myapp > myapp_backup.sql

# Сжатый формат (более быстрое восстановление)
pg_dump -U postgres -Fc myapp > myapp_backup.dump

# Резервная копия всех баз данных
pg_dumpall -U postgres > all_databases.sql

# Резервная копия с удалённого сервера
pg_dump -h 192.168.1.100 -U postgres myapp > myapp_remote.sql

Восстановление из резервной копии

hljs bash

# Восстановление из формата SQL
psql -U postgres myapp < myapp_backup.sql

# Восстановление из сжатого формата
pg_restore -U postgres -d myapp myapp_backup.dump

# Восстановление всех баз данных
psql -U postgres < all_databases.sql

Автоматическое резервное копирование через Cron

hljs bash

# Редактировать crontab
crontab -e

# Создавать резервную копию каждую ночь в 02:00
0 2 * * * pg_dump -U postgres myapp | gzip > /backup/myapp_$(date +\%Y\%m\%d).sql.gz

# Удалять резервные копии старше 30 дней
0 3 * * * find /backup -name 'myapp_*.sql.gz' -mtime +30 -delete

Управление службой

hljs bash

# Запустить службу
systemctl start postgresql

# Остановить службу
systemctl stop postgresql

# Перезапустить службу
systemctl restart postgresql

# Перезагрузить конфигурацию (без перезапуска)
systemctl reload postgresql

# Просмотр журналов
journalctl -u postgresql -f
tail -f /var/log/postgresql/postgresql-16-main.log

Заключение

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

Установка и базовое управление PostgreSQL

Содержание