Home » Установка и использование PostgreSQL на последний CentOS

Установка и использование PostgreSQL на последний CentOS

Если тебе нужна надёжная и производительная база данных для проекта, PostgreSQL — это один из лучших выборов. Эта статья покажет, как правильно установить и настроить PostgreSQL на последней версии CentOS, не потратив на это целый день. Мы пройдём весь путь от установки до создания первой базы данных и пользователя, затронем важные моменты безопасности и оптимизации производительности.

Почему PostgreSQL и CentOS?

PostgreSQL — это объектно-реляционная система управления базами данных с открытым исходным кодом, которая славится своей надёжностью и соответствием стандартам SQL. В отличие от MySQL, PostgreSQL изначально проектировался с акцентом на расширяемость и соответствие стандартам.

CentOS (а теперь и его наследники) остаётся популярным выбором для серверов благодаря стабильности и долгосрочной поддержке. Если ты планируешь запустить PostgreSQL на продакшене, стоит присмотреться к VPS-серверам или выделенным серверам с достаточным объёмом RAM и быстрыми дисками.

Установка PostgreSQL на CentOS

Начнём с обновления системы и установки необходимых пакетов:

sudo dnf update -y
sudo dnf install -y wget

Теперь установим официальный репозиторий PostgreSQL. Стандартные репозитории CentOS обычно содержат устаревшие версии, поэтому лучше использовать официальный:

# Установка репозитория PostgreSQL 15
sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Установка PostgreSQL
sudo dnf install -y postgresql15-server postgresql15

# Инициализация базы данных
sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

# Запуск и автозагрузка
sudo systemctl enable postgresql-15
sudo systemctl start postgresql-15

Проверим, что сервис запустился корректно:

sudo systemctl status postgresql-15

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

После установки PostgreSQL создаёт пользователя `postgres` с правами суперпользователя. Сначала установим пароль для этого пользователя:

sudo -u postgres psql
ALTER USER postgres PASSWORD 'твой_надёжный_пароль';
\q

Теперь настроим аутентификацию в файле `pg_hba.conf`:

sudo nano /var/lib/pgsql/15/data/pg_hba.conf

Найди строки с `local` и `host` и измени метод аутентификации с `peer` на `md5` для использования паролей:

# Было:
local   all             all                                     peer
host    all             all             127.0.0.1/32            ident

# Стало:
local   all             all                                     md5
host    all             all             127.0.0.1/32            md5

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

sudo systemctl restart postgresql-15

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

Создадим отдельную базу данных и пользователя для проекта:

# Подключаемся к PostgreSQL
sudo -u postgres psql

# Создаём базу данных
CREATE DATABASE myproject;

# Создаём пользователя
CREATE USER myprojectuser WITH PASSWORD 'secure_password';

# Даём права на базу данных
GRANT ALL PRIVILEGES ON DATABASE myproject TO myprojectuser;

# Выходим
\q

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

psql -h localhost -U myprojectuser -d myproject

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

Основные параметры для тюнинга находятся в файле `postgresql.conf`:

sudo nano /var/lib/pgsql/15/data/postgresql.conf

Вот основные параметры, которые стоит настроить:

Параметр	Рекомендуемое значение	Описание
shared_buffers	25% от RAM	Кэш для страниц данных
effective_cache_size	75% от RAM	Оценка доступной памяти для кэша ОС
work_mem	4MB-256MB	Память для операций сортировки
maintenance_work_mem	64MB-1GB	Память для операций обслуживания
max_connections	100-200	Максимальное количество соединений

Пример настройки для сервера с 4GB RAM:

shared_buffers = 1GB
effective_cache_size = 3GB
work_mem = 16MB
maintenance_work_mem = 256MB
max_connections = 100
random_page_cost = 1.1

Настройка удалённого доступа

Если нужно подключаться к PostgreSQL с других серверов, настрой удалённый доступ:

# В postgresql.conf
listen_addresses = '*'
port = 5432

В `pg_hba.conf` добавь разрешение для нужных IP:

# Для конкретного IP
host    all             all             192.168.1.100/32        md5

# Для подсети
host    all             all             10.0.0.0/24             md5

Не забудь настроить firewall:

sudo firewall-cmd --permanent --add-port=5432/tcp
sudo firewall-cmd --reload

Полезные команды для администрирования

Вот набор команд, которые пригодятся в повседневной работе:

# Подключение к PostgreSQL
psql -U username -d database -h hostname

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

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

# Информация о таблице
\d table_name

# Создание бэкапа
pg_dump -U username -d database > backup.sql

# Восстановление из бэкапа
psql -U username -d database < backup.sql

# Просмотр активных соединений
SELECT * FROM pg_stat_activity;

# Размер баз данных
SELECT pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size 
FROM pg_database;

Автоматизация и скрипты

Создай скрипт для автоматического бэкапа:

#!/bin/bash
# backup_postgres.sh

DB_NAME="myproject"
DB_USER="myprojectuser"
BACKUP_DIR="/var/backups/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)

mkdir -p $BACKUP_DIR

pg_dump -U $DB_USER -d $DB_NAME > $BACKUP_DIR/backup_$DATE.sql

# Удаляем бэкапы старше 7 дней
find $BACKUP_DIR -name "backup_*.sql" -mtime +7 -delete

echo "Backup completed: backup_$DATE.sql"

Добавь скрипт в cron для ежедневного выполнения:

0 2 * * * /path/to/backup_postgres.sh

Мониторинг и логирование

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

# В postgresql.conf
log_min_duration_statement = 1000  # Логировать запросы дольше 1 секунды
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_statement = 'mod'  # Логировать все модифицирующие операции

Логи можно найти в `/var/lib/pgsql/15/data/log/`.

Сравнение с альтернативами

Критерий	PostgreSQL	MySQL	MariaDB
Соответствие SQL	Отличное	Хорошее	Хорошее
Производительность	Высокая	Высокая	Высокая
Расширяемость	Отличная	Средняя	Хорошая
Поддержка JSON	Нативная	Базовая	Базовая
Репликация	Встроенная	Встроенная	Встроенная

Интересные возможности PostgreSQL

PostgreSQL предоставляет множество продвинутых функций:

Полнотекстовый поиск — встроенная поддержка без внешних инструментов
Работа с JSON/JSONB — можно использовать как NoSQL-хранилище
Расширения — PostGIS для геоданных, pg_stat_statements для анализа производительности
Trigger'ы и stored procedures — на различных языках (PL/pgSQL, Python, JavaScript)
Partitioning — разделение больших таблиц на части

Пример использования JSON:

-- Создание таблицы с JSON-полем
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    attributes JSONB
);

-- Вставка данных
INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "ram": "16GB", "storage": "512GB SSD"}');

-- Поиск по JSON-полю
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';

Решение типичных проблем

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

Решение: Проверь, была ли выполнена инициализация базы данных:

sudo /usr/pgsql-15/bin/postgresql-15-setup initdb

Проблема: Не могу подключиться с паролем

Решение: Проверь настройки аутентификации в `pg_hba.conf` и перезапусти сервис.

Проблема: Медленные запросы

Решение: Используй `EXPLAIN ANALYZE` для анализа планов выполнения:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

Заключение и рекомендации

PostgreSQL на CentOS — это отличная комбинация для серьёзных проектов. Эта СУБД подойдёт, если тебе нужна:

Высокая надёжность и соответствие стандартам SQL
Работа с геоданными через PostGIS
Сложные аналитические запросы
Гибкая система типов данных
Возможность горизонтального масштабирования

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

Больше информации можно найти в официальной документации PostgreSQL.

В этой статье собрана информация и материалы из различных интернет-источников. Мы признаем и ценим работу всех оригинальных авторов, издателей и веб-сайтов. Несмотря на то, что были приложены все усилия для надлежащего указания исходного материала, любая непреднамеренная оплошность или упущение не являются нарушением авторских прав. Все упомянутые товарные знаки, логотипы и изображения являются собственностью соответствующих владельцев. Если вы считаете, что какой-либо контент, использованный в этой статье, нарушает ваши авторские права, немедленно свяжитесь с нами для рассмотрения и принятия оперативных мер.

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