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

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

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

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

126
Немного скриптов для 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? Не стесняйтесь, пишите!

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

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

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

126

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

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

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

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

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

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

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

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


Остановился на dBeaver, не охото заморачиваться с кряками, функционала хватает за глаза, а также:
choco install dbeaver
YPermitin; Fox-trot; +2 Ответить
10. YPermitin 6366 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 6366 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 6366 06.11.19 11:51 Сейчас в теме
(13) спасибо!

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

См. также

Самые распространенные заблуждения об индексах в мире 1С 214

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

"Магия" индексов привела к множеству заблуждений об их работе. Попробуем развеять некоторые из них в контексте 1С.

28.11.2019    7200    YPermitin    44       

Перенос данных УТ 10.3 => УТ 11 / КА 2 / ERP 2 (ЕРП 2) (документы, остатки и справочная информация из "1С:Управление торговлей, ред. 10.3" в УТ 11 / КА 2 / ERP 2). Обновлен до УТ 10.3.56.х, УТ 11.4.10.х, КА 2.4.10.х и ERP 2.4.10.х! Промо

Уже более 100 компаний приобрели перенос и выполнили переход на УТ 11 / КА 2 / ERP 2 с помощью нашей разработки! Обработка перехода с УТ 10.3 на УТ 11 / КА 2 / ERP 2 позволяет перенести не только остатки на указанную дату (как типовой перенос), но и все возможные документы за выбранный период. При выходе новых релизов этих программ оперативно выпускаем обновление обработки. Предоставляем техническую поддержку. Можем сделать бесплатный тестовый перенос!

29700 руб.

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

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

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

24.10.2019    1290    w.r.    0       

Перенос данных БП 2.0 => УТ 11 / КА 2 / ERP 2 (перенос остатков, документов и справочников из "1С:Бухгалтерия предприятия 8", ред. 2.0 в "1С:Управление торговлей 8", ред.11 / КА 2 / ERP 2). Обновлено до УТ 11.4.10.х, КА 2.4.10.х, ERP 2.4.10.х! Промо

Перенос позволяет загрузить в УТ 11 / КА 2 / ERP 2 документы за выбранный период, справочную информацию и остатки по счетам бух. учета. Переносятся остатки денежных средств, взаиморасчетов, остатки товаров и материалов на складах. Переносятся девятнадцать основных видов документов за выбранный период и вся нормативно-справочная информация. Есть фильтр по организации. Если нужно переносить что-то дополнительно, то обычно бесплатно дорабатываю правила (перед покупкой согласуйте необходимые доработки).

29700 руб.

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

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

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

2 стартмани

21.10.2019    3592    5    YPermitin    10       

1СПАРК РИСКИ. Сервис оценки благонадежности контрагентов. Промо

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

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

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

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

30.09.2019    12153    YPermitin    14       

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

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

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

31.08.2019    4289    93    YPermitin    7       

Подборка решений для взаимодействия со ФГИС «Меркурий» Промо

С 1 июля 2019 года все компании, участвующие в обороте товаров животного происхождения, должны перейти на электронную ветеринарную сертификацию (ЭВС) через ФГИС «Меркурий». Инфостарт предлагает подборку программ, связанных с этим изменением.

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

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

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

2 стартмани

13.08.2019    4595    7    YPermitin    15       

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

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

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

5 стартмани

24.07.2019    8753    93    YPermitin    25       

С 2020 года сервис «Продление поддержки конфигурации 1С:УПП» подорожает вдвое Промо

Успейте продлить поддержку УПП до повышения цен! Фирма «1С» предупредила об изменении цен на сервис «Продление поддержки конфигурации "1С:Управление производственным предприятием"». С 1 января 2020 года сервис подорожает в два раза.

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

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

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

22.04.2019    21003    starik-2005    158       

Новый раздел на Инфостарте - Electronic Software Distribution Промо

Инфостарт напоминает: на нашем сайте можно купить не только ПО, связанное с 1С. В нашем арсенале – ESD-лицензии на ПО от ведущих вендоров: Microsoft, Kaspersky, ESET, Dr.Web, Аскон и другие.

  • Низкие цены, без скрытых платежей и наценок
  • Оперативная отгрузка
  • Возможность оплаты с личного счета (кешбек, обмен стартмани на рубли и т.п.)
  • Покупки идут в накопления для получения скидочных карт лояльности Silver (5%) и Gold (10%)

1C÷Linux÷PostgreSQL÷Apache 91

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

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

26.03.2018    31896    SerVer1C    43       

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

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

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

27.10.2017    16829    KRIHA    9       

1С:Предприятие через Интернет. 1С:Fresh Промо

Ведение бухгалтерского и налогового учет, сдача отчетности, управление бизнесом из любой точки мира. Привычные программы «1С» через Интернет без приобретения коробочных программ.

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

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

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

23.06.2017    38534    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    63701    59    vsasav    40       

Перенос данных КА 1.1 / УПП 1.3 => БП 3.0 (перенос остатков, документов и справочников из "1С:Комплексная автоматизация 1.1" / УПП 1.3 в "1С:Бухгалтерия 3.0"). Обновлен до версий КА 1.1.115.х, УПП 1.3.127.х! Промо

Разработка позволяет перенести остатки по всем счетам бух.учета в программу "1С:Бухгалтерия предприятия 8", ред. 3.0 на выбранную дату начала ведения учета. Также переносятся документы за период и вся необходимая справочная информация. Правила оперативно обновляю при выходе новых релизов. Рассылка обновлений правил бесплатно в течение 12 месяцев. Есть видеодемонстрация проведения переноса данных. Конфигурации при использовании обмена остаются полностью типовыми. Перенос данных возможен в Бухгалтерию 3.0 версии ПРОФ, КОРП или базовую.

24700 руб.

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

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

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

5 стартмани

25.07.2013    53917    897    YPermitin    50