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

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

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

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

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

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

1. Обновите систему:
<pre>sudo apt update</pre>

2. Установите необходимые пакеты:
<pre>sudo apt install -y wget ca-certificates</pre>

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

1. Добавьте официальный репозиторий PostgreSQL:
<pre>wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -</pre>

2. Добавьте репозиторий в источники:
<pre>echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list</pre>

3. Обновите список пакетов:
<pre>sudo apt update</pre>

4. Установите PostgreSQL 15:
<pre>sudo apt install -y postgresql-15 postgresql-client-15 postgresql-contrib-15</pre>

5. Проверьте версию:
<pre>psql --version</pre>

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

=== Запуск PostgreSQL ===

1. Запустите и включите автозапуск PostgreSQL:
<pre>sudo systemctl start postgresql</pre>
<pre>sudo systemctl enable postgresql</pre>

2. Проверьте статус:
<pre>sudo systemctl status postgresql</pre>

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

1. Переключитесь на пользователя postgres:
<pre>sudo -u postgres psql</pre>

2. Установите пароль для пользователя postgres:
<pre>ALTER USER postgres PASSWORD 'your_strong_password';</pre>

3. Выйдите из psql:
<pre>\q</pre>

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

1. Создайте нового пользователя:
<pre>sudo -u postgres createuser --interactive</pre>

2. Создайте базу данных:
<pre>sudo -u postgres createdb mydatabase</pre>

3. Подключитесь к базе данных:
<pre>sudo -u postgres psql mydatabase</pre>

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

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

1. Найдите файл конфигурации:
<pre>sudo find /etc -name "postgresql.conf" 2>/dev/null</pre>

2. Отредактируйте основной конфигурационный файл:
<pre>sudo nano /etc/postgresql/15/main/postgresql.conf</pre>

3. Настройте основные параметры:
<pre>\# Сетевые настройки
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'
</pre>

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

1. Отредактируйте файл pg_hba.conf:
<pre>sudo nano /etc/postgresql/15/main/pg_hba.conf</pre>

2. Настройте методы аутентификации:
<pre>\# 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
</pre>

=== Настройка SSL ===

1. Создайте SSL сертификаты:
<pre>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"</pre>

2. Установите права на сертификаты:
<pre>sudo chmod 600 /var/lib/postgresql/15/main/server.key</pre>
<pre>sudo chown postgres:postgres /var/lib/postgresql/15/main/server.*</pre>

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

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

1. '''Запуск PostgreSQL''':
<pre>sudo systemctl start postgresql</pre>

2. '''Остановка PostgreSQL''':
<pre>sudo systemctl stop postgresql</pre>

3. '''Перезапуск PostgreSQL''':
<pre>sudo systemctl restart postgresql</pre>

4. '''Проверка статуса''':
<pre>sudo systemctl status postgresql</pre>

5. '''Подключение к базе данных''':
<pre>psql -h localhost -U username -d database_name</pre>

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

1. '''Создание базы данных''':
<pre>sudo -u postgres createdb mydatabase</pre>

2. '''Удаление базы данных''':
<pre>sudo -u postgres dropdb mydatabase</pre>

3. '''Создание пользователя''':
<pre>sudo -u postgres createuser --interactive</pre>

4. '''Удаление пользователя''':
<pre>sudo -u postgres dropuser username</pre>

5. '''Изменение пароля пользователя''':
<pre>sudo -u postgres psql -c "ALTER USER username PASSWORD 'new_password';"</pre>

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

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

1. '''Полное резервное копирование''':
<pre>sudo -u postgres pg_dumpall > backup_all.sql</pre>

2. '''Резервное копирование конкретной базы данных''':
<pre>sudo -u postgres pg_dump mydatabase > mydatabase_backup.sql</pre>

3. '''Сжатое резервное копирование''':
<pre>sudo -u postgres pg_dump mydatabase | gzip > mydatabase_backup.sql.gz</pre>

4. '''Резервное копирование с настройками''':
<pre>sudo -u postgres pg_dump -h localhost -U postgres -Fc mydatabase > mydatabase_backup.dump</pre>

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

1. '''Восстановление из SQL файла''':
<pre>sudo -u postgres psql mydatabase < mydatabase_backup.sql</pre>

2. '''Восстановление из сжатого файла''':
<pre>gunzip -c mydatabase_backup.sql.gz | sudo -u postgres psql mydatabase</pre>

3. '''Восстановление из dump файла''':
<pre>sudo -u postgres pg_restore -d mydatabase mydatabase_backup.dump</pre>

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

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

1. '''Просмотр активных подключений''':
<pre>sudo -u postgres psql -c "SELECT * FROM pg_stat_activity;"</pre>

2. '''Просмотр размера баз данных''':
<pre>sudo -u postgres psql -c "SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;"</pre>

3. '''Просмотр медленных запросов''':
<pre>sudo -u postgres psql -c "SELECT query, mean_time, calls FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 10;"</pre>

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

1. '''Анализ таблиц''':
<pre>sudo -u postgres psql -d mydatabase -c "ANALYZE;"</pre>

2. '''Переиндексация таблиц''':
<pre>sudo -u postgres psql -d mydatabase -c "REINDEX DATABASE mydatabase;"</pre>

3. '''Очистка базы данных''':
<pre>sudo -u postgres psql -d mydatabase -c "VACUUM FULL;"</pre>

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

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

1. Отредактируйте postgresql.conf:
<pre>sudo nano /etc/postgresql/15/main/postgresql.conf</pre>

2. Добавьте настройки репликации:
<pre>wal_level = replica
max_wal_senders = 3
max_replication_slots = 3
hot_standby = on
</pre>

3. Настройте pg_hba.conf:
<pre>host replication replicator 192.168.1.0/24 md5</pre>

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

1. Создайте пользователя для репликации:
<pre>sudo -u postgres psql -c "CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'replicator_password';"</pre>

2. Создайте базовую резервную копию:
<pre>sudo -u postgres pg_basebackup -h master_server -D /var/lib/postgresql/15/main -U replicator -v -P -W</pre>

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

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

1. '''Разрешите порт PostgreSQL''':
<pre>sudo ufw allow 5432/tcp</pre>

2. '''Ограничьте доступ по IP''':
<pre>sudo ufw allow from 192.168.1.0/24 to any port 5432</pre>

=== Настройка SSL ===

1. '''Проверьте SSL соединения''':
<pre>sudo -u postgres psql -c "SHOW ssl;"</pre>

2. '''Проверьте SSL сертификаты''':
<pre>sudo -u postgres psql -c "SELECT * FROM pg_stat_ssl;"</pre>

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

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

1. '''PostgreSQL не запускается''':
* Проверьте логи: <pre>sudo journalctl -u postgresql</pre>
* Проверьте конфигурацию: <pre>sudo -u postgres postgres --check-config</pre>
* Проверьте права доступа: <pre>ls -la /var/lib/postgresql/15/main/</pre>

2. '''Ошибки подключения''':
* Проверьте pg_hba.conf
* Проверьте сетевые настройки
* Проверьте файрвол

3. '''Проблемы с производительностью''':
* Проверьте логи медленных запросов
* Оптимизируйте индексы
* Настройте параметры памяти

=== Диагностика ===

1. '''Проверка статуса''':
<pre>sudo systemctl status postgresql</pre>

2. '''Проверка подключений''':
<pre>sudo netstat -tlnp | grep :5432</pre>

3. '''Проверка логов''':
<pre>sudo tail -f /var/log/postgresql/postgresql-15-main.log</pre>

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

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

1. '''Просмотр всех баз данных''':
<pre>sudo -u postgres psql -l</pre>

2. '''Просмотр всех пользователей''':
<pre>sudo -u postgres psql -c "\du"</pre>

3. '''Просмотр таблиц в базе данных''':
<pre>sudo -u postgres psql -d mydatabase -c "\dt"</pre>

4. '''Просмотр схемы таблицы''':
<pre>sudo -u postgres psql -d mydatabase -c "\d table_name"</pre>

=== Мониторинг ===

1. '''Просмотр статистики''':
<pre>sudo -u postgres psql -c "SELECT * FROM pg_stat_database;"</pre>

2. '''Просмотр блокировок''':
<pre>sudo -u postgres psql -c "SELECT * FROM pg_locks;"</pre>

3. '''Просмотр активных запросов''':
<pre>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';"</pre>

== Заключение ==

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

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

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

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