Home » Быстрый старт: установка PostgreSQL на Ubuntu 24

Быстрый старт: установка PostgreSQL на Ubuntu 24

Продолжаем разбираться с основами серверной инфраструктуры. Сегодня на повестке дня — установка PostgreSQL на свежую Ubuntu 24. Если вы только что развернули новый сервер или планируете мигрировать базы данных, это руководство поможет вам быстро и без лишних телодвижений поднять PostgreSQL в боевой готовности.

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

Зачем PostgreSQL и что нового в Ubuntu 24

Ubuntu 24.04 LTS поставляется с PostgreSQL 16 в репозиториях — это значительный апгрейд по сравнению с предыдущими версиями. Новые фичи включают улучшенную производительность запросов, лучшую поддержку параллельных операций и обновленный планировщик запросов.

Основные преимущества PostgreSQL перед конкурентами:

ACID-совместимость — полная поддержка транзакций
Расширяемость — можно создавать собственные функции и типы данных
JSON и NoSQL возможности — гибридный подход к хранению данных
Репликация и кластеризация — встроенная поддержка высокой доступности
Полнотекстовый поиск — без необходимости внешних решений

Быстрая установка: три способа

Способ 1: Установка из стандартных репозиториев (рекомендуется для начинающих)

sudo apt update
sudo apt install postgresql postgresql-contrib

Этот способ установит PostgreSQL 16 со всеми базовыми расширениями. Простой, быстрый, но версия может быть не самой свежей.

Способ 2: Установка из официального репозитория PostgreSQL (для продакшена)

# Добавляем официальный репозиторий PostgreSQL
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

# Импортируем GPG ключ
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Обновляем списки пакетов
sudo apt update

# Устанавливаем PostgreSQL
sudo apt install postgresql-16 postgresql-contrib-16

Этот способ даст вам самую свежую версию с последними патчами безопасности.

Способ 3: Установка через Docker (для разработки)

docker run --name postgres-dev \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -p 5432:5432 \
  -d postgres:16

Подходит для быстрого тестирования и разработки, но не для продакшена.

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

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

# Переключаемся на пользователя postgres
sudo -i -u postgres

# Запускаем psql
psql

# Устанавливаем пароль для пользователя postgres
\password postgres

# Выходим из psql
\q

# Возвращаемся к обычному пользователю
exit

Теперь создадим базу данных и пользователя для вашего приложения:

# Создаем базу данных
sudo -u postgres createdb myapp_db

# Создаем пользователя
sudo -u postgres createuser --interactive myapp_user

# Подключаемся к psql для настройки прав
sudo -u postgres psql

# Устанавливаем пароль для нового пользователя
ALTER USER myapp_user WITH PASSWORD 'secure_password';

# Даем права на базу данных
GRANT ALL PRIVILEGES ON DATABASE myapp_db TO myapp_user;

# Выходим
\q

Настройка конфигурации

Основные конфигурационные файлы находятся в /etc/postgresql/16/main/:

postgresql.conf — основная конфигурация
pg_hba.conf — настройки аутентификации
pg_ident.conf — маппинг пользователей

Оптимизация производительности

Откройте /etc/postgresql/16/main/postgresql.conf и настройте следующие параметры:

# Для VPS с 4GB RAM
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200

# Включаем логирование медленных запросов
log_min_duration_statement = 1000
log_statement = 'all'
log_destination = 'stderr'
logging_collector = on
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

Если вы используете VPS с SSD, установите random_page_cost = 1.1. Для выделенного сервера с NVMe можно снизить до 1.0.

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

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

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

# В pg_hba.conf добавляем строку для удаленного доступа
host    all             all             0.0.0.0/0                md5

Важно: Это открывает доступ для всех IP. В продакшене обязательно укажите конкретные адреса или подсети.

Перезапускаем PostgreSQL:

sudo systemctl restart postgresql
sudo systemctl enable postgresql

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

Работа с psql

# Подключение к базе данных
psql -h localhost -U myapp_user -d myapp_db

# Основные команды в psql
\l          # Список баз данных
\dt         # Список таблиц
\du         # Список пользователей
\q          # Выход
\?          # Справка по командам
\h SELECT   # Справка по SQL команде

# Выполнение SQL файла
\i /path/to/script.sql

# Экспорт результата в файл
\o output.txt
SELECT * FROM users;
\o

Бэкап и восстановление

# Создание бэкапа
pg_dump -h localhost -U myapp_user -d myapp_db > backup.sql

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

# Бэкап всех баз данных
pg_dumpall -h localhost -U postgres > all_databases.sql

# Бэкап в сжатом формате
pg_dump -h localhost -U myapp_user -d myapp_db -Fc > backup.dump

# Восстановление из сжатого бэкапа
pg_restore -h localhost -U myapp_user -d myapp_db backup.dump

Мониторинг и диагностика

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

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

# Статистика по базам данных
SELECT * FROM pg_stat_database;

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

# Медленные запросы (требует включения log_min_duration_statement)
SELECT query, mean_time, calls 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

Системные команды

# Статус службы
sudo systemctl status postgresql

# Просмотр логов
sudo tail -f /var/log/postgresql/postgresql-16-main.log

# Перезапуск без потери соединений
sudo systemctl reload postgresql

# Проверка конфигурации
sudo -u postgres postgres --check

# Информация о кластере
sudo -u postgres pg_lsclusters

Расширения и дополнительные возможности

PostgreSQL славится своей расширяемостью. Вот несколько полезных расширений:

Установка популярных расширений

# UUID для генерации уникальных идентификаторов
sudo apt install postgresql-16-uuid-ossp

# PostGIS для работы с геоданными
sudo apt install postgresql-16-postgis-3

# pg_stat_statements для анализа производительности
sudo apt install postgresql-16-pg-stat-statements

# Подключение расширений в базе данных
psql -U postgres -d myapp_db -c "CREATE EXTENSION uuid-ossp;"
psql -U postgres -d myapp_db -c "CREATE EXTENSION postgis;"
psql -U postgres -d myapp_db -c "CREATE EXTENSION pg_stat_statements;"

Работа с JSON

PostgreSQL отлично работает с JSON данными:

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

# Вставка JSON данных
INSERT INTO products (name, attributes) VALUES 
('Laptop', '{"brand": "Dell", "ram": "16GB", "cpu": "Intel i7"}'),
('Phone', '{"brand": "Apple", "model": "iPhone 15", "storage": "256GB"}');

# Запросы к JSON данным
SELECT * FROM products WHERE attributes->>'brand' = 'Dell';
SELECT * FROM products WHERE attributes @> '{"ram": "16GB"}';

# Индексы для JSON
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

Сравнение с другими СУБД

Критерий	PostgreSQL	MySQL	MongoDB
ACID-совместимость	✅ Полная	✅ Полная (InnoDB)	⚠️ Ограниченная
JSON поддержка	✅ Нативная JSONB	✅ JSON тип	✅ Нативная
Полнотекстовый поиск	✅ Встроенный	✅ Встроенный	✅ Встроенный
Производительность	⚡ Высокая	⚡ Очень высокая	⚡ Высокая
Сложность настройки	🔧 Средняя	🔧 Простая	🔧 Простая
Репликация	✅ Встроенная	✅ Встроенная	✅ Встроенная

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

PostgreSQL отлично интегрируется с системами автоматизации. Вот несколько примеров:

Автоматический бэкап через cron

# Создаем скрипт бэкапа
cat > /home/user/backup_postgres.sh << 'EOF'
#!/bin/bash
BACKUP_DIR="/home/user/backups"
DATE=$(date +%Y%m%d_%H%M%S)
mkdir -p $BACKUP_DIR

# Бэкап всех баз
pg_dumpall -h localhost -U postgres > $BACKUP_DIR/all_databases_$DATE.sql

# Сжимаем
gzip $BACKUP_DIR/all_databases_$DATE.sql

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

echo "Backup completed: $BACKUP_DIR/all_databases_$DATE.sql.gz"
EOF

chmod +x /home/user/backup_postgres.sh

# Добавляем в cron для выполнения каждый день в 2:00
echo "0 2 * * * /home/user/backup_postgres.sh" | crontab -

Мониторинг через systemd

# Создаем сервис для мониторинга
cat > /etc/systemd/system/postgres-monitor.service << 'EOF'
[Unit]
Description=PostgreSQL Monitor
After=postgresql.service

[Service]
Type=simple
ExecStart=/home/user/monitor_postgres.sh
Restart=always
RestartSec=60

[Install]
WantedBy=multi-user.target
EOF

# Скрипт мониторинга
cat > /home/user/monitor_postgres.sh << 'EOF'
#!/bin/bash
while true; do
    if ! pg_isready -h localhost -p 5432; then
        echo "PostgreSQL is down! $(date)" | mail -s "PostgreSQL Alert" admin@domain.com
        systemctl restart postgresql
    fi
    sleep 30
done
EOF

chmod +x /home/user/monitor_postgres.sh
systemctl enable postgres-monitor
systemctl start postgres-monitor

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

Проблема: "peer authentication failed"

Это означает, что PostgreSQL пытается использовать системную аутентификацию. Решение:

# Редактируем pg_hba.conf
sudo nano /etc/postgresql/16/main/pg_hba.conf

# Заменяем строку
local   all             all                                     peer

# На
local   all             all                                     md5

# Перезапускаем PostgreSQL
sudo systemctl restart postgresql

Проблема: "could not connect to server"

Проверяем статус службы и настройки:

# Проверяем статус
sudo systemctl status postgresql

# Проверяем, слушает ли PostgreSQL нужный порт
sudo netstat -tlnp | grep :5432

# Проверяем настройки подключения
sudo -u postgres psql -c "SHOW listen_addresses;"

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

# Включаем логирование медленных запросов
sudo -u postgres psql -c "ALTER SYSTEM SET log_min_duration_statement = 1000;"
sudo -u postgres psql -c "SELECT pg_reload_conf();"

# Анализируем план выполнения
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user@example.com';

# Создаем индекс для ускорения
CREATE INDEX idx_users_email ON users(email);

Интеграция с приложениями

PostgreSQL отлично работает с современными фреймворками:

Подключение из Python (Django/Flask)

# Для Django в settings.py
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myapp_db',
        'USER': 'myapp_user',
        'PASSWORD': 'secure_password',
        'HOST': 'localhost',
        'PORT': '5432',
    }
}

# Для Flask с SQLAlchemy
from sqlalchemy import create_engine
engine = create_engine('postgresql://myapp_user:secure_password@localhost/myapp_db')

Подключение из Node.js

# package.json
{
  "dependencies": {
    "pg": "^8.11.3"
  }
}

# app.js
const { Pool } = require('pg');
const pool = new Pool({
  user: 'myapp_user',
  host: 'localhost',
  database: 'myapp_db',
  password: 'secure_password',
  port: 5432,
});

Безопасность в продакшене

Несколько важных настроек безопасности:

# Ограничиваем соединения
max_connections = 100

# Настройка SSL
ssl = on
ssl_cert_file = '/etc/ssl/certs/server.crt'
ssl_key_file = '/etc/ssl/private/server.key'

# Ограничиваем привилегии пользователей
CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE myapp_db TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Полезные ресурсы

Заключение

PostgreSQL на Ubuntu 24 — это мощная и надежная связка для любых задач, от простых веб-приложений до сложных аналитических систем. Главные преимущества:

Простота установки — буквально две команды для базовой настройки
Гибкость — поддержка SQL и NoSQL паттернов в одной системе
Производительность — отличная оптимизация для современного железа
Надежность — полная ACID-совместимость и проверенная репутация
Масштабируемость — от небольших проектов до enterprise-решений

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

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

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

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