Эксплуатация БД. Linux

Запуск

Запуск СУБД осуществляется посредством запуска соответствующего сервиса.

Ubuntu:

sudo systemctl start postgresql

Oracle Linux:

sudo systemctl start postgresql-${pg_version}

где $pg_version — версия PostgreSQL.

Остановка

Остановка СУБД осуществляется посредством остановки соответствующего сервиса.

Ubuntu:

sudo systemctl stop postgresql

Oracle Linux:

sudo systemctl stop postgresql-${pg_version}

где $pg_version — версия PostgreSQL.

Изменение параметров СУБД

Изменять параметры СУБД рекомендуется с помощью команды ALTER SYSTEM <имя параметра> <значение>, выполняя ее в интерактивном терминале PostgreSQL psql.

Пример:

ALTER SYSTEM SET max_connections = 100;

При таком способе значение параметра сохраняется в файл postgresql.auto.conf, который находится в каталоге данных СУБД. При запуске СУБД этот файл считывается после основного конфигурационного файла postgresql.conf и параметры, заданные в нем, переопределяют одноименные параметры, заданные postgresql.conf.

Часть параметров применяются только с перезапуском СУБД. Для таких параметров в основном конфигурационном файле postgresql.conf в строке параметра добавлен комментарий # (change requires restart).

Для остальных параметров применить изменения можно без перезапуска СУБД. В интерактивном терминале PostgreSQL psql выполните:

select pg_reload_conf();

Узнать текущее значение параметра можно выполнив в интерактивном терминале PostgreSQL psql команду show <имя параметра>.

Пример:

SHOW max_connections;

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

В настоящем руководстве приводится самый простой способ создания резервной копии базы данных. Он заключается в создании дампа базы данных. Дамп базы данных можно использовать в качестве резервной копии для небольших объемов базы данных. При больших объемах (сотни гигабайт и выше) создание дампа базы данных не дает гарантии возможности восстановления рабочей базы из него. Кроме того, создание дампа в таких случаях занимает продолжительное время и затрудняет применение такого способа в промышленной эксплуатации системы.
Для подобных случаев рекомендуем воспользоваться специализированными средствами создания резервных копий, рекомендованными производителями СУБД.

Для создания резервной копии базы данных предлагается использовать утилиту pg_dump. Рекомендуем создавать резервную копию в формате directory (ключ -Fd) - в этом режиме ее создание происходит быстрее, а также поддерживается работа в несколько потоков.

Создайте каталог для размещения резервной копии, например:

mkdir -p ~/backup/$(date +'%F') && cd $_
Запустите создание резервной копии:

pg_dump -h <DB_SERVER_IP> -p <DB_PORT> -v -Fd -b -U <DB_USER_NAME> -f <DB_NAME>_$(date +'%F-%H-%M-%S').bak <DB_NAME>

где:
- <DB_SERVER_IP> — IP-адрес сервера БД
- <DB_PORT> — порт СУБД (как правило 5432)
- <DB_USER_NAME> — имя пользователя БД, от имени которого приложение подключается к базе
- <DB_NAME> — имя базы данных

Для ускорения создания резервной копии можно осуществить выгрузку резервной копии в несколько потоков. Для этого добавьте в команду ключ -j число_заданий. При этом учитывайте особенности создания резервной копии в данном режиме (ссылка на документацию pg_dump).

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

Восстановление базы данных из резервной копии, созданной при помощи утилиты pg_dump необходимо осуществлять с помощью pg_restore.

Восстановление необходимо выполнять в пустую базу данных (пересоздайте ее перед запуском восстановления). Все приложения, использующие базу данных должны быть выключены.

Запустите восстановление БД с помощью следующей команды:

pg_restore -h <DB_SERVER_IP> -p <DB_PORT> -v -Fd -O -x -U <DB_USER_NAME> -d <DB_NAME> <DB_BACKUP_FILE_NAME>

где:

<DB_SERVER_IP> — IP-адрес сервера базы данных;
<DB_PORT> — порт СУБД (как правило 5432);
<DB_USER_NAME> — имя пользователя, от имени которого приложение подключается к базе данных;
<DB_NAME> — имя базы данных;
<DB_BACKUP_FILE_NAME> — файл (каталог) с резервной копией базы данных.

Для ускорения восстановления из резервной копии можно запустить процесс в несколько потоков. Для этого добавьте в команду ключ -j число_заданий. При этом учитывайте особенности работы в данном режиме (ссылка на документацию pg_restore).

Сбор информации по текущим активным сессиям

В процессе разбора инцидентов в работе системы возникает необходимость получения информации о выполняющихся в СУБД запросах, активных сессиях, блокировках. Сбор данной информации осуществляется из представления pg_stat_activity.

Выполнять команды необходимо от имени пользователя postgres.

Создайте каталог для сбора диагностической информации:

mkdir -p ~/mon/$(date +'%F') && cd $_
Получите информацию из pg_stat_activity. Файл с информацией будет сохранен в домашнем каталоге пользователя postgres в ./mon/$(date +'%F')/:

psql -c "select * from pg_stat_activity where state != 'idle' order by xact_start;" > ./pgstat_$(date +'%F-%H-%M-%S').txt

Сбор информации по блокировкам

Наличие блокировок можно посмотреть следующим запросом, выполнив его в интерактивном терминале PostgreSQL psql:

SELECT
  COALESCE(l1.relation::regclass::text,l1.locktype) as locked_item,
  w.wait_event_type, w.wait_event, w.query as waiting_query, w.application_name as application_name, w.client_addr as client_addr,
  l1.mode as waiting_mode,
  (select now() - xact_start as waiting_xact_duration from pg_stat_activity where pid = w.pid),
  (select now() - query_start as waiting_query_duration from pg_stat_activity where pid = w.pid),
  w.pid as waiting_pid, w.usename as waiting_user, w.state as waiting_state,
  l.wait_event_type, l.wait_event, l.query as locking_query, l2.mode as locking_mode, l.application_name as application_name, l.client_addr as client_addr,
  (select now() - xact_start as locking_xact_duration from pg_stat_activity where pid = l.pid),
  (select now() - query_start as locking_query_duration from pg_stat_activity where pid = l.pid),
  l.pid as locking_pid, l.usename as locking_user, l.state as locking_state
FROM pg_stat_activity w
JOIN pg_locks l1 ON w.pid = l1.pid AND NOT l1.granted
JOIN pg_locks l2 ON (l1.transactionid = l2.transactionid AND l1.pid != l2.pid)
    OR (l1.database = l2.database AND l1.relation = l2.relation and l1.pid != l2.pid)
JOIN pg_stat_activity l ON l2.pid = l.pid
WHERE w.wait_event is not null
ORDER BY l.query_start,w.query_start;

Показывает заблокированные и блокирующие процессы в порядке возникновения блокировок.

Периодический сбор информации по текущим активным сессиям и блокировкам

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

В первую очередь важно настроить сбор данной информации на основном сервере СУБД. При наличии реплик СУБД, использующихся для обработки запросов на чтение данных, сбор такой информации рекомендуется выполнять так же и на этих репликах.

Все команды, не требующие привилегий root рекомендуем выполнять от имени пользователя postgres.

На сервере СУБД выполните следующие настройки:

Создайте каталоги для сохранения рабочих файлов

mkdir -p ~/deploy/$(date +'%F')/{update,backup}
Создайте каталог для сохранения диагностической информации. Владельцем каталога должен быть пользователь postgres.

mkdir -p ~/stat
Скачайте с сайта NAUMEN (ссылка для скачивания) файлы и поместите их в созданный каталог update:
- pgstat.sh - скрипт сбора статистики
- pgstat.service - файл описания systemd-юнита
- pgstat.timer - файл описания systemd-таймера
Отредактируйте скрипт pgstat.sh, указав в параметре DAYS_TO_STORE количество дней хранения файлов со статистикой.
Отредактируйте pgstat.timer, указав в параметре OnCalendar периодичность сбора статистики (ссылка на документацию systemd.time).

Настроенная в шаблоне периодичность сбора статистики составляет 1 минуту и соответствует нашим рекомендациям.
Поместите скрипт pgstat.sh в домашний каталог пользователя postgres и сделайте его исполняемым:
- Скопируйте скрипт
  
  cp ~/deploy/$(date +'%F')/update/pgstat.sh ~/
- Сделайте его исполняемым
  
  chmod +x ~/pgstat.sh
Поместите файлы pgstat.service и pgstat.timer в /etc/systemd/system и включите таймер.
- Скопируйте файл сервиса сбора статистики
  
  sudo cp ~/deploy/$(date +'%F')/update/pgstat.service /etc/systemd/system/
- Скопируйте файл таймера сбора статистики
  
  sudo cp ~/deploy/$(date +'%F')/update/pgstat.timer /etc/systemd/system/
- Перечитайте файлы systemdsudo systemctl daemon-reload
- Включите автозапуск таймера при загрузке сервера
  
  sudo systemctl enable pgstat.timer
- Включите таймер
  
  sudo systemctl start pgstat.timer

Периодическое обслуживание базы данных

Рекомендуем настроить регулярный сбор статистики в базе данных, а также очистку от неиспользуемых больших объектов (large objects).

Все команды, не требующие привилегий root рекомендуем выполнять от имени пользователя postgres.

Создайте каталоги для сохранения рабочих файлов

mkdir -p ~/deploy/$(date +'%F')/{update,backup}
Скачайте с сайта NAUMEN (ссылка для скачивания) файлы и поместите их в созданный каталог update:
- cleaning.sh — скрипт сбора статистики;
- cleaning.service — файл описания systemd-юнита;
- cleaning.timer — файл описания systemd-таймера.
Отредактируйте скрипт cleaning.sh, указав в нем каталог размещения исполняемых файлов Postgres - параметр BINDIR.
В этом же файле вы также можете включить или выключить выполнение регламентных процедур (текущие настройки соответствуют нашим рекомендациям. Без особой на то необходимости, не рекомендуем их изменять):
- DO_VACUUMLO=1 # делать ли vacuumlo;
- DO_ANALIZE=1 # делать ли analize (пересбор статистики для планировщика);
- DO_VACUUMFULL=0 # делать ли vacuum full;
- DO_REINDEX=0 # делать ли reindex (пересбор индексов).
Выполнение скрипта обслуживания с включенными параметрами DO_VACUUMFULL=1 и DO_REINDEX=1 влияет на производительность, т.к. накладывает блокировки на целые таблицы.
Отредактируйте cleaning.timer, указав в параметре OnCalendar расписание запуска (ссылка на описание systemd.time).

Настроенная в шаблоне периодичность выполнения данной процедуры составляет 1 раз в сутки и соответствует нашим рекомендациям. Вы можете задать желаемое время запуска. Рекомендуем производить эти операции в часы наименьшей пользовательской активности
Поместите скрипт cleaning.sh в домашний каталог пользователя postgres и сделайте его исполняемым
- Скопируйте скрипт
  
  cp ~/deploy/$(date +'%F')/update/cleaning.sh ~/
- Сделайте его исполняемым
  
  chmod +x ~/cleaning.sh
Поместите файлы cleaning.service и cleaning.timer в /etc/systemd/system и включите таймер.
- Скопируйте файл сервиса чистки базы данных
  
  sudo cp ~/deploy/$(date +'%F')/update/cleaning.service /etc/systemd/system/
- Скопируйте файл таймера чистки базы данных
  
  sudo cp ~/deploy/$(date +'%F')/update/cleaning.timer /etc/systemd/system/
- Перечитайте файлы systemd
  
  sudo systemctl daemon-reload
- Включите автозапуск таймера при загрузке сервера
  
  sudo systemctl enable cleaning.timer
- Включите таймер
  
  sudo systemctl start cleaning.timer

Периодическая очистка архива wal-логов

При настроенном хранении архива wal-логов (обычно настраивается при подключении реплики СУБД) необходимо периодически удалять старые файлы, чтобы они не заняли все свободное дисковое пространство. Данную очистку необходимо выполнять на всех серверах, на которых настроено архивирование wal-логов.

Все команды, не требующие привилегий root рекомендуем выполнять от имени пользователя postgres.

Создайте каталоги для сохранения рабочих файлов

mkdir -p ~/deploy/$(date +'%F')/{update,backup}
Скачайте с сайта NAUMEN (ссылка для скачивания) файлы и поместите их в созданный каталог update:
- wal_cleaning.sh — скрипт сбора статистики;
- wal_cleaning.service — файл описания systemd-юнита;
- wal_cleaning.timer — файл описания systemd-таймера.
Отредактируйте скрипт wal_cleaning.sh, указав в нем:
- walpath — путь к каталогу с архивом wal-логов;
- limit — лимит на размер этого каталога (от количества хранимых wal-логов зависит то, на какое время может отстать реплика с сохранением возможности догнать мастер);
- batchsize — количество файлов, удаляемых за один раз.
Отредактируйте wal_cleaning.timer, указав в параметре OnCalendar расписание запуска (ссылка на описание systemd.time).

Настроенная в шаблоне периодичность очистки составляет 1 раз в час и соответствует нашим рекомендациям. Вы можете задать желаемое время запуска. Рекомендуем производить эти операции в часы наименьшей пользовательской активности.
Поместите скрипт wal_cleaning.sh в домашний каталог пользователя postgres и сделайте его исполняемым
- Скопируйте скрипт:
  
  cp ~/deploy/$(date +'%F')/update/wal_cleaning.sh ~/
- Сделайте его исполняемым:
  
  chmod +x ~/wal_cleaning.sh
Поместите файлы wal_cleaning.service и wal_cleaning.timer в /etc/systemd/system и включите таймер.
- Скопируйте файл сервиса очистки архива wal-логов:
  
  sudo cp ~/deploy/$(date +'%F')/update/wal_cleaning.service /etc/systemd/system/
- Скопируйте файл таймера очистки архива wal-логов:
  
  sudo cp ~/deploy/$(date +'%F')/update/wal_cleaning.timer /etc/systemd/system/
- Перечитайте файлы systemd:
  
  sudo systemctl daemon-reload
- Включите автозапуск таймера при загрузке сервера:
  
  sudo systemctl enable wal_cleaning.timer
- Включите таймер:
  
  sudo systemctl start wal_cleaning.timer

Postgres. pg_profile - периодический сбор наиболее ресурсоемких действий/операции в базах данных

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

Все команды, не требующие привилегий root рекомендуем выполнять от имени пользователя postgres.

Создайте каталоги для сохранения рабочих файлов

mkdir -p ~/deploy/$(date +'%F')/{update,backup}
Скачайте pg_profile. В качестве примера приводится процедура установки версии pg_profile 4.6. Поместите ее в созданный каталог update

wget -P ~/deploy/$(date +'%F')/update/ https://github.com/zubkov-andrei/pg_profile/releases/download/4.6/pg_profile--4.6.tar.gz
Распакуйте архив в папку расширений postgres

tar xzf pg_profile--4.6.tar.gz --directory $(pg_config --sharedir)/extension
Скачайте с сайта NAUMEN (ссылка для скачивания) файлы и поместите их в созданный каталог update:
- pg_profile.sh — скрипт сбора статистики
- pg_profile.service — файл описания systemd-юнита
- pg_profile.timer — файл описания systemd-таймера
Отредактируйте скрипт pg_profile.sh, если необходимо указав в нем:
- Путь к каталогу где хранятся выборки;
- Время хранения выборок;
Отредактируйте pg_profile.timer, указав в параметре OnCalendar расписание запуска. Настроенная в шаблоне периодичность очистки составляет 1 раз в 30 минут и соответствует нашим рекомендациям.
Поместите скрипт pg_profile.sh в домашний каталог пользователя postgres и сделайте его исполняемым
- Скопируйте скрипт:
  
  cp ~/deploy/$(date +'%F')/update/pg_profile.sh ~/
- Сделайте его исполняемым:
  
  chmod +x ~/pg_profile.sh
Поместите файлы pg_profile.service и pg_profile.timer в /etc/systemd/system и включите таймер.
- Скопируйте файл сервиса pg_profile:
  
  sudo cp ~/deploy/$(date +'%F')/update/pg_profile.service /etc/systemd/system/
- Скопируйте файл таймера pg_profile:
  
  sudo cp ~/deploy/$(date +'%F')/update/pg_profile.timer /etc/systemd/system/
- Перечитайте файлы systemd:
  
  sudo systemctl daemon-reload
- Включите автозапуск таймера при загрузке сервера:
  
  sudo systemctl enable pg_profile.timer
- Включите таймер:
  
  sudo systemctl start pg_profile.timer

Настройки параметров СУБД

Настройки рекомендуем выполнять с помощью команды ALTER SYSTEM в интерактивном терминале PostgreSQL psql.

Настройки параметров на мастере базы данных:

CREATE EXTENSION dblink;
CREATE EXTENSION pg_stat_statements;
CREATE SCHEMA profile;
CREATE EXTENSION pg_profile SCHEMA profile;
ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = '5000';
ALTER SYSTEM SET pg_stat_statements.track = 'top';
ALTER SYSTEM SET pg_stat_statements.save = 'off';
ALTER SYSTEM SET track_activities = on;
ALTER SYSTEM SET track_counts = on;
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET track_wal_io_timing = on;
ALTER SYSTEM SET track_functions = "all";

В случае наличия реплики выполните команды на реплике:

ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM SET pg_stat_statements.max = '5000';
ALTER SYSTEM SET pg_stat_statements.track = 'top';
ALTER SYSTEM SET pg_stat_statements.save = 'off';
ALTER SYSTEM SET track_activities = on;
ALTER SYSTEM SET track_counts = on;
ALTER SYSTEM SET track_io_timing = on;
ALTER SYSTEM SET track_wal_io_timing = on;
ALTER SYSTEM SET track_functions = "all";

Перечитайте конфигурацию. В интерактивном терминале psql выполните:

select pg_reload_conf();

Посмотреть созданные таблицы для сбора статистики в схеме profile.

Переключение на схему:

SET search_path TO profile;

Посмотреть вновь созданные таблицы для мониторинга:

\dt+

Для подключения статистики с реплики выполните на мастере:

SELECT profile.create_server('replica','host=<ip address или имя хоста реплики> dbname=postgres port=5432');

Необходимо так же разрешить подключение к реплике в файле pg_hba.conf с мастера для пользователя postgres.

Проверить с каких серверов БД снимается статистика можно командой:

select profile.show_servers();

После всех вышеперечисленных настроек в домашней директории по пути, указанному в файле pg_profile.sh, начнут появляться выборки с базы данных Мастер и базы данных Реплики, которые могут быть использованы для анализа работы базы данных. Выборки будут появляться каждые 30 минут - как указано в файле pg_profile.timer.

Если необходимо построить отчет, к примеру за сутки, можно использовать команды:

psql -Aqtc "select profile.get_report(tstzrange(now() - interval '1 day',now()))" -o /path/to/last24h_report.html

psql -Aqtc "select profile.get_report('replica', tstzrange(now() - interval '1 day',now()))" -o /path/to/last24h_report.html