Настройка базы данных PostgreSQL: от установки до переноса данных в IntelliJ IDEA

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

# Устанавливаем PostgreSQL и дополнительные утилиты
sudo apt install postgresql postgresql-contrib -y

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

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

# Заходим в интерактивную консоль psql
psql

# В консоли создаем своего пользователя и базу данных
CREATE USER myuser WITH PASSWORD 'StrongPassword123!';
CREATE DATABASE myapp_db OWNER myuser;
\q

# Запускаем контейнер с PostgreSQL 15
# Важно: монтируем volume для сохранения данных
# и задаем пароль через переменную окружения
docker run -d \
  --name postgres-dev \
  -e POSTGRES_PASSWORD=mysecretpassword \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  -v postgres_data:/var/lib/postgresql/data \
  postgres:15-alpine

# Проверяем, что контейнер запущен
docker ps | grep postgres

# Подключаемся к базе внутри контейнера
docker exec -it postgres-dev psql -U myuser -d myapp

# Находим расположение конфига
sudo -u postgres psql -c "SHOW config_file;"

# Обычный путь в Ubuntu:
# /etc/postgresql/15/main/postgresql.conf

# Редактируем файл (используем nano или vim)
sudo nano /etc/postgresql/15/main/postgresql.conf

# СЕТЬ И ПОДКЛЮЧЕНИЯ
# Слушаем все интерфейсы (для Docker или удаленного доступа)
listen_addresses = '*'          # для локальной разработки можно 'localhost'
port = 5432
max_connections = 100           # макс. число одновременных подключений

# ПАМЯТЬ
shared_buffers = 2GB            # ~25% от доступной ОЗУ (8GB * 0.25 = 2GB)
effective_cache_size = 6GB      # ~75% от ОЗУ
work_mem = 10MB                 # память на операцию сортировки/хеширования
maintenance_work_mem = 512MB    # для операций обслуживания (VACUUM, INDEX)

# ЖУРНАЛ ТРАНЗАКЦИЙ (WAL) И ПРОИЗВОДИТЕЛЬНОСТЬ
wal_level = replica             # для репликации и point-in-time recovery
synchronous_commit = on         # гарантия сохранности, можно off для скорости
max_wal_size = 2GB
min_wal_size = 1GB

# ПЛАНИРОВЩИК ЗАПРОСОВ И АНАЛИЗ
random_page_cost = 1.1          # для SSD дисков (для HDD = 4.0)
effective_io_concurrency = 200  # для SSD
shared_preload_libraries = 'pg_stat_statements'  # для мониторинга запросов

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# Разрешаем локальным подключениям от пользователя postgres доверительную аутентификацию
local   all             postgres                                peer

# Разрешаем всем локальным пользователям подключаться ко всем БД по паролю
local   all             all                                     md5

# Разрешаем удаленные подключения с определенной подсети (например, офис)
host    all             all             192.168.1.0/24          md5

# Разрешаем подключение конкретному пользователю к конкретной БД с любого IP (для приложения)
host    myapp_db        myuser          0.0.0.0/0               scram-sha-256

# Отключаем доверительную аутентификацию извне (опасно!)
# host    all             all             0.0.0.0/0               trust

# После изменения конфигов перезагружаем PostgreSQL
# (перечитывает конфиги без остановки сервиса)
sudo systemctl reload postgresql

# Или полная перезагрузка, если менялись критические параметры
sudo systemctl restart postgresql

# Проверяем, что настройки применились
sudo -u postgres psql -c "SELECT name, setting FROM pg_settings WHERE name IN ('listen_addresses', 'shared_buffers', 'max_connections');"

# 1. Создаем дамп (бэкап) старой базы в кастомном формате (сжатый)
pg_dump -U old_user -h old_host -d old_database -F c -b -v -f /backup/old_db.dump

# 2. Создаем новую пустую базу на целевом сервере (если нужно)
sudo -u postgres psql -c "CREATE DATABASE new_app_db OWNER myuser;"

# 3. Восстанавливаем дамп в новую базу
pg_restore -U myuser -h localhost -d new_app_db -v /backup/old_db.dump

# 4. Проверяем восстановление, подключаясь через IDEA
# или командой:
psql -U myuser -d new_app_db -c "SELECT COUNT(*) FROM your_main_table;"