Установка и конфигурация PostgreSQL

Установка и конфигурация PostgreSQL

1. Подготовка системы

• Войдите в систему под учетной записью с правами root или пользователя с правами sudo.
• Убедитесь, что у вас есть доступ к серверу через SSH или консоль.

2. Установка PostgreSQL

Обновление системы

1. Обновите систему:

sudo apt update

2. Установите необходимые пакеты:

sudo apt install -y wget ca-certificates

Установка PostgreSQL

1. Добавьте официальный репозиторий PostgreSQL:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

2. Добавьте репозиторий в источники:

echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list

3. Обновите список пакетов:

sudo apt update

4. Установите PostgreSQL 15:

sudo apt install -y postgresql-15 postgresql-client-15 postgresql-contrib-15

5. Проверьте версию:

psql --version

3. Первоначальная настройка

Запуск PostgreSQL

1. Запустите и включите автозапуск PostgreSQL:

sudo systemctl start postgresql

sudo systemctl enable postgresql

2. Проверьте статус:

sudo systemctl status postgresql

Настройка пользователя postgres

1. Переключитесь на пользователя postgres:

sudo -u postgres psql

2. Установите пароль для пользователя postgres:

ALTER USER postgres PASSWORD 'your_strong_password';

3. Выйдите из psql:

\q

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

1. Создайте нового пользователя:

sudo -u postgres createuser --interactive

2. Создайте базу данных:

sudo -u postgres createdb mydatabase

3. Подключитесь к базе данных:

sudo -u postgres psql mydatabase

4. Конфигурация PostgreSQL

Основные настройки

1. Найдите файл конфигурации:

sudo find /etc -name "postgresql.conf" 2>/dev/null

2. Отредактируйте основной конфигурационный файл:

sudo nano /etc/postgresql/15/main/postgresql.conf

3. Настройте основные параметры:

\# Сетевые настройки
listen_addresses = '*'
port = 5432

\# Настройки памяти
shared_buffers = 256MB
effective_cache_size = 1GB
work_mem = 4MB
maintenance_work_mem = 64MB

\# Настройки логирования
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
log_min_duration_statement = 1000
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on

\# Настройки безопасности
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'

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

1. Отредактируйте файл pg_hba.conf:

sudo nano /etc/postgresql/15/main/pg_hba.conf

2. Настройте методы аутентификации:

\# TYPE  DATABASE        USER            ADDRESS                 METHOD

\# "local" is for Unix domain socket connections only
local   all             postgres                                peer
local   all             all                                     md5

\# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
host    all             all             0.0.0.0/0               md5

\# IPv6 local connections:
host    all             all             ::1/128                 md5

Настройка SSL

1. Создайте SSL сертификаты:

sudo -u postgres openssl req -new -x509 -days 365 -nodes -text -out /var/lib/postgresql/15/main/server.crt -keyout /var/lib/postgresql/15/main/server.key -subj "/CN=postgresql-server"

2. Установите права на сертификаты:

sudo chmod 600 /var/lib/postgresql/15/main/server.key

sudo chown postgres:postgres /var/lib/postgresql/15/main/server.*

5. Управление PostgreSQL

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

1. Запуск PostgreSQL:

sudo systemctl start postgresql

2. Остановка PostgreSQL:

sudo systemctl stop postgresql

3. Перезапуск PostgreSQL:

sudo systemctl restart postgresql

4. Проверка статуса:

sudo systemctl status postgresql

5. Подключение к базе данных:

psql -h localhost -U username -d database_name

Управление базами данных

1. Создание базы данных:

sudo -u postgres createdb mydatabase

2. Удаление базы данных:

sudo -u postgres dropdb mydatabase

3. Создание пользователя:

sudo -u postgres createuser --interactive

4. Удаление пользователя:

sudo -u postgres dropuser username

5. Изменение пароля пользователя:

sudo -u postgres psql -c "ALTER USER username PASSWORD 'new_password';"

6. Резервное копирование

Создание резервной копии

1. Полное резервное копирование:

sudo -u postgres pg_dumpall > backup_all.sql

2. Резервное копирование конкретной базы данных:

sudo -u postgres pg_dump mydatabase > mydatabase_backup.sql

3. Сжатое резервное копирование:

sudo -u postgres pg_dump mydatabase | gzip > mydatabase_backup.sql.gz

4. Резервное копирование с настройками:

sudo -u postgres pg_dump -h localhost -U postgres -Fc mydatabase > mydatabase_backup.dump

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

1. Восстановление из SQL файла:

sudo -u postgres psql mydatabase < mydatabase_backup.sql

2. Восстановление из сжатого файла:

gunzip -c mydatabase_backup.sql.gz | sudo -u postgres psql mydatabase

3. Восстановление из dump файла:

sudo -u postgres pg_restore -d mydatabase mydatabase_backup.dump

7. Мониторинг и оптимизация

Мониторинг производительности

1. Просмотр активных подключений:

sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"

2. Просмотр размера баз данных:

sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"

3. Просмотр медленных запросов:

sudo -u postgres psql -c "SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"

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

1. Анализ таблиц:

sudo -u postgres psql -d mydatabase -c "ANALYZE;"

2. Переиндексация таблиц:

sudo -u postgres psql -d mydatabase -c "REINDEX DATABASE mydatabase;"

3. Очистка базы данных:

sudo -u postgres psql -d mydatabase -c "VACUUM FULL;"

8. Настройка репликации

Настройка мастер-сервера

1. Отредактируйте postgresql.conf:

sudo nano /etc/postgresql/15/main/postgresql.conf

2. Добавьте настройки репликации:

wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
hot_standby = on

3. Настройте pg_hba.conf:

host replication replicator 192.168.1.0/24 md5

Настройка слейв-сервера

1. Создайте пользователя для репликации:

sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'replicator_password';"

2. Создайте базовую резервную копию:

sudo -u postgres pg_basebackup -h master_server -D /var/lib/postgresql/15/main -U replicator -v -P -W

9. Безопасность

Настройка файрвола

1. Разрешите порт PostgreSQL:

sudo ufw allow 5432/tcp

2. Ограничьте доступ по IP:

sudo ufw allow from 192.168.1.0/24 to any port 5432

Настройка SSL

1. Проверьте SSL соединения:

sudo -u postgres psql -c "SHOW ssl;"

2. Проверьте SSL сертификаты:

sudo -u postgres psql -c "SELECT * FROM pg_stat_ssl;"

10. Устранение неполадок

Частые проблемы

1. PostgreSQL не запускается:

• Проверьте логи:

  sudo journalctl -u postgresql

• Проверьте конфигурацию:

  sudo -u postgres postgres --check-config

• Проверьте права доступа:

  ls -la /var/lib/postgresql/15/main/

2. Ошибки подключения:

• Проверьте pg_hba.conf
• Проверьте сетевые настройки
• Проверьте файрвол

3. Проблемы с производительностью:

• Проверьте логи медленных запросов
• Оптимизируйте индексы
• Настройте параметры памяти

Диагностика

1. Проверка статуса:

sudo systemctl status postgresql

2. Проверка подключений:

sudo netstat -tlnp | grep :5432

3. Проверка логов:

sudo tail -f /var/log/postgresql/postgresql-15-main.log

11. Полезные команды

Администрирование

1. Просмотр всех баз данных:

sudo -u postgres psql -l

2. Просмотр всех пользователей:

sudo -u postgres psql -c "\du"

3. Просмотр таблиц в базе данных:

sudo -u postgres psql -d mydatabase -c "\dt"

4. Просмотр схемы таблицы:

sudo -u postgres psql -d mydatabase -c "\d table_name"

Мониторинг

1. Просмотр статистики:

sudo -u postgres psql -c "SELECT * FROM pg_stat_database;"

2. Просмотр блокировок:

sudo -u postgres psql -c "SELECT * FROM pg_locks;"

3. Просмотр активных запросов:

sudo -u postgres psql -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';"

Заключение

PostgreSQL успешно установлен и настроен! Теперь у вас есть мощная система управления базами данных с возможностями репликации, мониторинга и резервного копирования.

Для размещения ваших баз данных на надежном хостинге рекомендуем воспользоваться услугами [ARK-HOSTER.RU](https://ark-hoster.ru):

• [Выделенные серверы](https://ark-hoster.ru/dedicated/) - для высоконагруженных баз данных
• [VPS/VDS серверы](https://ark-hoster.ru/vpsgame/) - для небольших и средних проектов

Наши специалисты помогут с настройкой PostgreSQL и оптимизацией производительности ваших баз данных!