Александр Краснов — Страница 16 — Записки архитектора

Поиск медленных запросов

Задача

Необходимо найти долговыполняющиеся запросы к базе данных.

Решение

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

Обслуживание PostgreSql

Examples

In the examples below, [tablename] is optional. Without a table specified, VACUUM will be run on available tables in the current schema that the user has access to.

Plain VACUUM: Frees up space for re-use

VACUUM [tablename]

Full VACUUM: Locks the database table, and reclaims more space than a plain VACUUM

/* Before Postgres 9.0: */ 
VACUUM FULL 
/* Postgres 9.0+: */ 
VACUUM(FULL) [tablename]

Full VACUUM and ANALYZE: Performs a Full VACUUM and gathers new statistics on query executions paths using ANALYZE

/* Before Postgres 9.0: */ 
VACUUM FULL ANALYZE [tablename] 
/* Postgres 9.0+: */ 
VACUUM(FULL, ANALYZE) [tablename]

Verbose Full VACUUM and ANALYZE: Same as #3, but with verbose progress output

/* Before Postgres 9.0: */ 
VACUUM FULL VERBOSE ANALYZE [tablename] 
/* Postgres 9.0+: */ 
VACUUM(FULL, ANALYZE, VERBOSE) [tablename]

Работа с часовыми поясами

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

Далее в nodejs нужно добавить код для преобразования даты:

var moment = require('moment');
...
Date.prototype.toJSON = function () { return moment(this).format('YYYY-MM-DDTHH:mm:ss.SSSZZ'); }

Вся правда об индексах в PostgreSQL / Олег Бартунов, Александр Коротков (PostgreSQL)

https://www.youtube.com/watch?v=aaecM4wKdhY&t=211s

Удаление postgresql

sudo apt-get --purge remove postgresql
dpkg -l | grep postgres
sudo apt-get --purge remove postgresql postgresql-doc postgresql-client-common

https://askubuntu.com/questions/32730/how-to-remove-postgres-from-my-installation

Настройка временной зоны для БД postgres

Подключаемся под postgres

sudo su - postgres

Настройка временной зоны на Ubuntu 18

В systemd есть своя утилита для настройки даты и часового пояса. Чтобы узнать текущее состояние выполните:

timedatectl status

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

Автоматическое документирование базы данных

Для документирование базы данных применяется открытое решение Autodoc.

Инструкция по установке и настройке на сайте appcode.pw

Основной скрипт для запуска генерации документации.

cd autodoc
postgresql_autodoc -d cic-dev-db -h 192.168.17.111 -u postgres --password=<pw> -t html -s "core|dbo" -f /var/www/html/cic-dev-db

Демонстрационный вариант документации можно посмотреть на сайте http://cic.it-serv.ru/cic-dev-db.html

Autodoc для postgresql

Ставим приложение autodoc

$ sudo apt install libdbi-perl libhtml-template-perl libterm-readkey-perl libdbd-pg-perl
$ git clone https://github.com/cbbrowne/autodoc.git
$ cd autodoc
$ sudo make install

Дальше запускаем скрипт

postgresql_autodoc -d cic-dev-db -h 192.168.17.111 -u postgres --password -t html -s core -f cic-dev-db-core

Примечание: схемы можно передать как

-s "core|dbo"

Исходный код: https://github.com/cbbrowne/autodoc

Обновление строки в postgresql

UPDATE dummy 
SET customer=subquery.customer, 
address=subquery.address, 
partn=subquery.partn 
FROM (SELECT address_id, customer, address, partn FROM /* big hairy SQL */ ...) AS subquery 
WHERE dummy.address_id=subquery.address_id;

UPDATE table T1 
SET column1 = T2.column1 
FROM 
table T2 
INNER JOIN table T3 USING (column2) 
WHERE T1.column2 = T2.column2;

https://stackoverflow.com/questions/6256610/updating-table-rows-in-postgres-using-subquery