Набор скриптов для знакомства с PostgreSQL

Публикация № 1148863

Администрирование - Администрирование СУБД

скрипты PostgreSQL PG Postgres диагностика анализ оптимизация администрирование базы данных

115
Немного скриптов для PostgreSQL, позволяющих познакомиться с состоянием сервера.

Снова за свое

В одной из прошлых статей был предложен небольшой набор скриптов для SQL Server, позволяющий оценить текущее состояние сервера, узнать какие базы на нем расположены и другую полезную информацию. Сегодня мы попытаемся сделать то же самое для PostgreSQL. Ведь все любят PostgreSQL, не так ли?

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

Здесь Вы не найдете продвинутых скриптов, ведь это лишь для знакомство с новым серверов PostgreSQL. Но обо всем далее.

Это не руководство

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

Все скрипты можно запускать с помощью терминального клиента psqlс помощью графической утилиты pgAdmin или же с помощью другого графического инструмента Azure Data Studio (поддержка PostgreSQL реализовано через расширение, не забудьте его установить). Это прямо "золотой век" инструментария для работы с базами данных!

Начнем с простых скриптов и постепенно перейдем к некоторым вопросам производительности.

Поехали!

Перейдем уже непосредственно к скриптам. Все они были проверены на PostgreSQL версии 10, но абсолютное большинство скриптов можно запускать и на более ранних версиях.

Первое знакомство

Как только Вы запустили свое клиентское приложение, то в первую очередь стоило бы узнать следующую информацию

 
 Базовая информация о сервере
 
 Время работы с момента запуска
 
 Количество активных соединений
 
 Просмотр конфигурации сервера

Общую информацию мы получили, пойдемте дальше.

О базах данных

Следующее, что следует изучить - это список баз данных и их размер.

 
 Список баз

Не думаю, что эта информация может быть полезна сама по себе. Теперь узнаем размер всех баз.

 
 Размер всех баз

На следующем шаге уже может потребоваться посмотреть почему эта база такая большая.

 
 Размер таблиц

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

И снова индексы

Индексы являются одними из самых важных объектов любой базы данных, обеспечивающих производительность запросов и клиентских приложений для базы (в нашем случае это платформы 1С). Узнаем список индексов, который у нас есть.

 
 Список индексов

Список индексов - это хорошо, но нам нужно больше. Индексы нужны, но они могут и быть избыточными. Получим статистику использования индексов.

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

Попробуем определить недостающие индексы.

 
 Таблицы с отсутствующими индексами

Также стоит держать под контролем показатели фрагментации индексов, или bloat ("раздутия") как это обычно еще называют в PostgreSQL.

 
 Информация о фрагментации (раздутии) индексов

На этом с индексами пока все. Давайте посмотрим на статистику.

Статистика в порядке?

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

 
 Информация о статистике

Теперь давайте поговорим о производительности.

Производительность

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

 
 Активные запросы

Теперь мы можем получить план запроса.

 
 Получение плана запроса

Может быть полезным получить информацию о выполняемых транзакциях.

 
 Информация о транзакциях

Можно проверить эффективность работы кэша.

 
 Использования кэша

И под конец попробуем получить длительные запросы.

 
 Длительные запросы

Вот и все, со скриптами пока все.

Любите ли Вы PostgreSQL?

Никаких готовых рецептов в статье нет, также как и нет информации о настройке операционной системы для оптимальной работы СУБД (не важно Windows это или *.nix) или настройке мониторинга. Лишь скрипты для получения общей информации.

Однако, теперь у Вас может появиться интерес и направление для изучения этой популярной и эффективной СУБД.

Есть чем дополнить? Добро пожаловать в комментарии!

Или есть интересные вопросы или опыт по PostgreSQL? Не стесняйтесь, пишите!

Другие ссылки

Другие полезные материалы

Авторские разработки

115

Специальные предложения

Комментарии
Избранное Подписка Сортировка: Древо
1. 3vs 05.11.19 06:11 Сейчас в теме
Юрий, подскажите правильную на Ваш взгляд методологию обновления конфигураций, к примеру Бухгалтерия 3 и Зарплата 3.1, работающих на PostgreSQL.

Я делаю так - обновляю конфигурацию конфигуратором, запускаю базу, чтобы всё принялось и обновилось, потом закрываю базу и останавливаю сервер предприятия и запускаю pgAdmin3 и в нём последовательно делаю "Обслуживание", сначала VACUUM с включенными флажками "FULL" и "ANALYZE", вопрос, для чего нужен флажок "FREEZE" и нужно ли его включать? Потом делаю "ANALYZE", потом "REINDEX", потом закрываю pgAdmin3, запускаю сервер предприятия и отдаю в работу.
Это правильно, или надо обслуживать базу как-то по другому?

Ещё вопрос, в пункте "Обслуживание" есть ещё ключ "CLUSTER" зачем он нужен и надо ли его запускать, если сервер в одном числе и лице и как сервер базы данных и как сервер предприятия?
2. YPermitin 5739 05.11.19 07:10 Сейчас в теме
(1) я бы не хотел касаться темы обслуживания, особенно здесь, в комментариях.

Тем более Ваши вопросы решаются чтением стандартной документации. Но пока сложилось впечатление, что Вы делайте это на всякий случай.
3. 3vs 05.11.19 07:25 Сейчас в теме
(2)Хотелось услышать мнение профессионалов в плане обслуживания баз 1С, работающих на PostgreSQL.
Но пока сложилось впечатление, что Вы делайте это на всякий случай.

Не, мнение сложилось правильное! :-)
Но, после этих телодвижений на старой железяке Бухгалтерия 3 заметно прибавляет в скорости работы!
4. 🅵🅾️🆇 435 05.11.19 22:52 Сейчас в теме
Здорово.
Попробуйте dBeaver или DataGrip

pgAdmin эт прям совсем ниачем.
5. YPermitin 5739 06.11.19 05:35 Сейчас в теме
(4) Спасибо! А Azure Data Studio пробовали?
9. 🅵🅾️🆇 435 06.11.19 09:01 Сейчас в теме
(5) Неа.
Был опыт только с dBeaver и DataGrip.
dBeaver - бесплатен и умеет огромное количество различных СУБД.

DataGrip - платен, а также встроен в продукты JetBrains и поставляется отдельной софтиной.
Былоб довольно удобно, еслиб пользовался средами разработки наших соотечественников.


Остановился на dBeaver, не охото заморачиваться с кряками, функционала хватает за глаза, а также:
choco install dbeaver
YPermitin; Fox-trot; +2 Ответить
10. YPermitin 5739 06.11.19 09:10 Сейчас в теме
(9) спасибо за развернутый ответ.

Попробую оба инструмента.
6. 3vs 06.11.19 05:43 Сейчас в теме
(4)У меня железо старое и PostgreSQL 9.4, pgAdmin хватает
для обслуживания.
Хотелось просто методологию правильного обновления базы 1С, работающей на PostgreSQL, чтобы производительность не снижалась.
YPermitin; +1 Ответить
7. 3vs 06.11.19 05:45 Сейчас в теме
(6)Извиняюсь, вопрос, видимо был Юрию, влез. :-)
8. 3vs 06.11.19 05:47 Сейчас в теме
Юрий, а может Вы дадите какую-нибудь статью по обслуживанию баз 1С,
работающих на PostgreSQL?
YPermitin; +1 Ответить
11. YPermitin 5739 06.11.19 09:26 Сейчас в теме
(8) я бы просто начал отсюда: https://postgrespro.ru/docs/postgrespro/9.5/maintenance

Можно и отдельную публикацию сделать на этот счет. :)
letarch; 3vs; +2 Ответить
12. 3vs 06.11.19 10:42 Сейчас в теме
(11)Да, можно и оттуда! :-)
Хочется рекомендаций профессионалов по обслуживанию PostgreSQL именно в связке с 1С.
Можно ли обойтись просто встроенными в платформу 1С средствами проверки и исправления базы, или оптимальней отключить сервер предприятия, чтобы не мешал и запустить обслуживание PostgreSQL своими средствами PostgreSQL, этапы обслуживания в этом случае.
Архивы у меня делаются скриптом средствами PostgreSQL, но пишут, что не факт, что то, что выгрузилось в архив, корректно загрузится обратно.
Вопрос восстановления базы из архивной копии PostgreSQL тоже бы можно осветить, как лучше это делать, грохнуть старую базу и восстановить на новое место или можно восстанавливать из архива прямо в существующую базу, тьф-тьфу, пока всё работает, из архивов базу восстанавливать пока не приходилось.
Я, правда, ещё делаю контрольный выстрел - перед обновлением ещё базу и в DT выгружаю руками средствами конфигуратора.

Будет время и желание на этот счёт, черкните для крестьянских детей вроде меня статейку! :-)
15. letarch 06.11.19 15:35 Сейчас в теме
(11) да, было бы очень интересно почитать всем, а то сейчас никак не победим "тормоза" 1с в крохотной 70+Гб базе :-(
13. Gorus 44 06.11.19 11:48 Сейчас в теме
Дополню скриптами по управлению соединениями:

1. Закрытие всех активных подключений к базе DBName:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'DBName' AND pid <> pg_backend_pid();


2. Закрываем определенное соединение (pid берем из списка соединений):
SELECT pg_terminate_backend(pid);


3. Запрещаем новые соединения к базе DBName
UPDATE pg_database SET datallowconn = 'false' WHERE datname = 'DBName';


4. Разрешаем новые соединения к базе DBName
UPDATE pg_database SET datallowconn = 'true' WHERE datname = 'DBName';
3vs; YPermitin; +2 Ответить
14. YPermitin 5739 06.11.19 11:51 Сейчас в теме
(13) спасибо!

Сохраню в свою коллекцию.
Оставьте свое сообщение

См. также

Сюрприз fsync() PostgreSQL 6

Статья Системный администратор Программист Нет файла PostgreSQL Бесплатно (free) Администрирование СУБД

Предлагаю вашему вниманию продолжение перевода статьи Jonathan Corbet "PostgreSQL's fsync() surprise". Оригинал доступен по ссылке https://lwn.net/Articles/752063/

24.10.2019    1013    w.r.    0       

Обозреватель криптографии 40

Инструменты и обработки Системный администратор Программист Внешний отчет (ert,erf) v8 Windows Абонемент ($m) Инструментарий разработчика Защита ПО

Отчет для просмотра доступных провайдеров и сертификатов криптографии на сервере и клиенте.

2 стартмани

21.10.2019    2275    4    YPermitin    10       

Набор скриптов для знакомства с SQL Server 216

Статья Системный администратор Программист Нет файла Бесплатно (free) Производительность и оптимизация (HighLoad) Администрирование СУБД

Поговорим о скриптах, которые помогут быстро ознакомиться с состоянием SQL Server, в том числе с вопросами производительности.

30.09.2019    10872    YPermitin    13       

Анализ производительности APDEX 65

Отчеты и формы Системный администратор Программист Внешний отчет (ert,erf) v8 1cv8.cf Бесплатно (free) Производительность и оптимизация (HighLoad)

Отчет для просмотра и анализа замеров производительности в конфигурациях на базе БСП.

31.08.2019    3285    93    YPermitin    7       

Путеводитель по истории релизов 36

Отчеты и формы Системный администратор Программист Внешний отчет (ert,erf) v8 1cv8.cf Абонемент ($m) WEB

Отчет по истории выпуска релизов и анализа информации по обновлениям.

2 стартмани

13.08.2019    3239    7    YPermitin    15       

Просмотр и анализ структуры базы данных (отчет на СКД) 102

Отчеты и формы Системный администратор Программист Внешний отчет (ert,erf) v8 v8::СКД 1cv8.cf Windows Абонемент ($m) Инструментарий разработчика

Отчет для просмотра и анализа структуры базы данных с поддержкой файловых баз (ограниченный режим), а также баз на SQL Server и PostgreSQL.

2 стартмани

24.07.2019    6086    80    YPermitin    25       

Практика перехода на Linux и Postgres в небольшой компании (10 пользователей) 100

Статья Системный администратор Программист Нет файла v8 1cv8.cf Ubuntu Бесплатно (free) Администрирование данных 1С

Почему я решил поставить давнему клиенту Linux + Postgres вместо Windows + MS SQL? Что меня останавливало раньше?

22.04.2019    20259    starik-2005    158       

1C÷Linux÷PostgreSQL÷Apache 90

Статья Системный администратор Нет файла v8 Linux Бесплатно (free) Администрирование данных 1С Apache

Дружим 1С с Линуксом ИЛИ Установка окружения для работы с 1С на Линуксе под Постгресом и Апачем.

26.03.2018    30123    SerVer1C    43       

Потоковая репликация и непрерывное архивирование базы данных PostgreSQL - делюсь небольшим опытом 54

Статья Системный администратор Нет файла Россия Бесплатно (free) Архивирование (backup)

Постарался кратко описать опыт настройки потоковой репликации и непрерывного архивирования в PostgreSQL.

27.10.2017    16321    KRIHA    9       

PostgreSQL на Windows – реальная альтернатива для высоконагруженных систем на базе 1С 159

Статья Системный администратор Программист Нет файла v8 Бесплатно (free) Производительность и оптимизация (HighLoad)

Многие интересуются PostgreSQL, но не знают, насколько хорошо будет она работать с уже существующими системами. «Инфософт» - одна из первых компаний, кто опробовал PostgreSQL на Windows. О своем опыте перехода рассказывает руководитель отдела информационных технологий компании.      

23.06.2017    37912    a.doroshkevich    113       

Настройка PostgreSQL для работы в связке с 1С 8.х на платформе Windows Server 2012, объём БД более 200 Гб 201

Инструменты и обработки Системный администратор Архив с данными v8 Россия Windows Абонемент ($m) Производительность и оптимизация (HighLoad) Администрирование данных 1С

Настройка бесплатной СУБД PostgreSQL для работы в связке с 1С 8.х на платформе Windows Server 2012 х64. Объём БД более 380 Гб для мощного сервака. Конфигурация КА 1.1.108.2, 50 пользователей. Более 1 млн. проводок при закрытии месяца. Время закрытия месяца сравнимо с MSSQL и составляет в среднем 2 часа. Время отмены закрытия месяца - всего 10 минут! Ликвидированы зависания PostgreSQL. Всё за счет настроек файла postgesql.conf.

1 стартмани

11.10.2016    62077    59    vsasav    40       

Просмотр и анализ журнала регистрации (отчет на СКД) 90

Отчеты и формы Системный администратор Программист Внешний отчет (ert,erf) v8::СКД 1cv8.cf Абонемент ($m) Журнал регистрации

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

2 стартмани

25.07.2013    52631    885    YPermitin    50