• SQL запросы для анализа PostgreSQL
• Tuning PostgreSQL PostgreSql postgresql.conf
• Резервное копирование и восстановление PostgreSQL. WAL. Как бэкапить данные в PostgreSQL?
• Репликация PostgreSQL: Bucardo, Репликации баз данных PostgreSQL при помощи SkyTools Londiste: Londiste и т.д.
• Индекс в базах данных Postgresql: INDEX, REINDEX. HOT.
• Типы данных PostgreSQL. CONSTRAINT Ограничения целостности.
• Хранимые процедуры, функции и триггеры
• EXPLAIN
• Табличные пространства PostgreSQL
• Интеграция Внешних Данных с PostgreSQL: Руководство по Foreign-Data Wrappers

• pg_hba.conf - настройка политики доступа к базам данных и идентификации пользователей
• pg_config - эта утилита выводит параметры (например, с какими параметрами скомпилирован PostgreSQL) конфигурации текущей установленной версии PostgreSQL. Находится в пакете postgresql-devel (для ОС Fedora Linux) postgresql-server-dev-8.3(для ОС Debian 5).
• pg_ctl является утилитой для запуска, остановки, перезапуска, перезагрузки конфигурационных файлов, информирования о состоянии сервера PostgreSQL, или отправки сигналов PostgreSQL- процессу. Показать статус севера:

  # sudo -u postgres pg_ctl status -D /var/lib/pgsql/data

• PostgreSql postgresql.conf - настройки сервера.

PostgreSQL (произносится «Постгре-Эс-Кю-Эль», коротко называется «Постгрес») - свободная объектно-реляционная система управления базами данных (12 правил Кодда: что такое система управления базами данных). Использует порт 5432/tcp/udp. PostgreSQL использует только один механизм хранения данных под названием Postgres storage system (система хранения Postgres), в котором транзакции и внешние ключи полностью функциональны, в отличии от Движок БД MySQL, в котором InnoDB и BDB являются единственными типами таблиц, которые поддерживают транзакции.

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

MVCC - одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.

• xmin - хранит номер транзакции, в которой запись была создана.
• xmax - хранит номер транзакции, в которой запись была удалена или изменена.

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

Список всех версий PostgreSQL, с датами их выпуска и примечаниями к релизам, можно найти на официальном сайте. Текущая главная версия: PostgreSQL 16.2 (выпущена 2024-03-28). Выбор версии PostgreSQL зависит от ваших конкретных потребностей и требований. Рекомендую использовать последнюю стабильную главную версию для новых проектов.

pg_hba.conf - настройка политики доступа к базам данных и идентификации пользователей сервера Использование и настройка PostgreSQL.

В этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к SQL серверу Использование и настройка PostgreSQL, а также методы идентификации клиентов. Этот файл может содержать два вида записей:

1. Запись вида "host". host <имя базы данных> <маска адреса> <способ авторизации доступа> [аргумент для авторизации]
2. Запись типа "local". Эта запись определяет авторизацию доступа к базе данных локальных пользователей. Идентично "host", за исключением того, что IP- адрес и маска адреса опущены за ненадобностью.

Параметр <способ авторизации доступа>:

• trust в этом режиме авторизации доступа не производится. Соединение считается доверительным.

Примеры записей pg_hba.conf:

• Разрешить всем пользователям доступ с любого хоста к всем базам данных по логину и паролю

  # TYPE  DATABASE        USER            ADDRESS                 METHOD
  host    all             all             all                     md5

• Разрешить пользователю(dbuser) доступ к базе данных(mother) с любого хоста по логину и паролю

  host   mother    dbuser           all                   md5

PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера - соответственно он не может создать две базы в разных кодировках - кодировка всегда одна для всего сервера и всех его БД.

Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):

postgres=# show server_encoding;
 server_encoding 
-----------------
 UTF8
(1 row)

Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.

Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.

• pgAdmin - GUI для PostgreSQL. Лучший графический клиент из существующих.
• DBeaver Community Free Universal Database Tool - бесплатный многоплатформенный инструмент для работы с базами данных для разработчиков, администраторов баз данных, аналитиков и всех, кому необходимо работать с базами данных. Поддерживает все популярные базы данных: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Apache Hive, Phoenix, Presto и др.
• phpPgAdmin - веб- приложение с открытым кодом, написанное на языке PHP и представляющее собой веб- интерфейс для администрирования СУБД PostgreSQL.
• OpenOffice и SDBC. Для прямого доступа из OpenOffice к PostgreSQL без промежуточного уровня в виде ODBC/JDBC драйверов разрабатывается postgresql-sdbc драйвер.

  # apt install openoffice.org-sdbc-postgresql

• DbVisualizer в бесплатной версии ограничен набор функций.

psql - PostgreSQL interactive terminal.

• psql интерактивный терминал PostgreSQL
• PostgreSQL: создать БД, пользователя, таблицу, установить права
• PL/pgSQL ( (Procedural Language/PostGres Structured Query Language) - процедурное расширение языка SQL, используемое в СУБД PostgreSQL.
• Как в PostgreSQL сохранить данные в формате csv??

В директории /usr/share/doc/postgresql* можно найти дополнительную информацию по запуску.

• Подключиться к локальному серверу.

  $ sudo -u postgres psql
  psql (8.4.5)
  Type "help" for help.
  
  postgres=#

• Подключиться к удаленному серверу.

  $ psql -h 192.168.1.20 -U postgres

• В psql узнать подробности об определённом SQL команде можно при помощи \h (ключ \? выводит помощь по мета-командам), например для drop table:

  postgres=# \h drop table
  Command:     DROP TABLE
  Description: remove a table
  Syntax:
  DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

• Пример выполнения SQL запроса из командой строки

  # sudo -u postgres psql -d mybd --command="select * from pg_stat_user_indexes where schemaname='public';"

• Как в PostgreSQL посмотреть список пользователей?

     postgres=# select * from pg_shadow;
     postgres=# \du

• Вывести список баз данных:

     postgres=# select * from pg_database;
  или
     postgres=# \l
  Из командной строки:
     # psql -U postgres -A -q -t -c "select datname from pg_database"
  или:
     # psql -l

• Вывести название текущей базы данных:

  SELECT current_database();

• Вывести имя (идентификатор) текущего пользователя:

  SELECT current_user;

• Вывести список таблиц активной базы данных (если после ключа \d указать имя таблицы - будет выведена структура таблицы):

  postgres=# \dt
  или
  select * from pg_tables; # будут выведены все таблицы, в том числе и системные
  select tablename from pg_tables where schemaname='public';

• Удалить БД testbd777

  # sudo -u postgres dropdb testbd777

• Залить(восстановить) данные в БД c именем test777

  # sudo -u postgres psql test777 < lost_cdr.sql

• regexp_split_to_table. В текстовом поле ipaddr хранятся IP адреса разделенные ; т.е. 1 строка - много IP. Запрос выводит все IP в столбик т.н. 1 строка - 1 IP

  SELECT regexp_split_to_table(ipaddr, E';') as ip FROM peers;

• Вывести версию Postgresql:

  postgres=# SELECT version();
   PostgreSQL 8.4.8 on x86_64-redhat-linux-gnu, 
  compiled by GCC gcc (GCC) 4.4.5 20101112 (Red Hat 4.4.5-2), 64-bit

• Пример экранирования одинарной кавычки в слове Кот-д'Ивуар. В PHP для экранирования спецсимволов используется функция pg_escape_string().

  $ psql -U postgres -d mydb --command="INSERT INTO countries (english,russian) 
  VALUES ('Ivory Coast', 'Кот-д''Ивуар');"

• Для смены активной базы данных в psql, применяется ключ \с
• Вывести время запуска сервера и uptime

  SELECT pg_postmaster_start_time();
     pg_postmaster_start_time    
  -------------------------------
   2013-08-21 14:20:45.451467+00
  
  SELECT current_timestamp - pg_postmaster_start_time() AS uptime;

Если запрос запущен из интерфейса pgsql, то завершение работы сервера не поможет - запрос все равно продолжит свое выполнение, необходимо вызывать функцию pg_cancel_backend.

select * from pg_stat_activity; # посмотреть все запросы
select * from pg_stat_activity WHERE current_query like 'SELECT%'; # посмотреть все SELECT запросы
select * from pg_stat_activity WHERE current_query like 'INSERT%';

# снять все активные select запросы
SELECT pg_cancel_backend(procpid) as x FROM pg_stat_activity WHERE current_query like 'SELECT%';

# снять запрос VACUUM
SELECT pg_cancel_backend(procpid) as x FROM  pg_stat_activity WHERE current_query like 'VACUUM%';

SELECT запросы можно снимать из ОС командой kill

# ps auxww | grep ^postgres
...
postgres 15724 97.7 11.3 2332996 1871476 ?     Rs   07:50   1:53 postgres: postgres mybd 127.0.0.1(53624) SELECT
...
# kill 15724

select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

В PostgreSQL Транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.

PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.

Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с id=2

# nano delprices.sql
BEGIN;
DELETE FROM prices WHERE ratesheet_id=2;
DELETE FROM ratesheets WHERE id=2;
COMMIT;

Выполним транзакцию для test777:

# sudo -u postgres psql -l
# sudo -u postgres psql test777 < delprices.sql

• Размер базы данных PostgreSQL, таблицы, столбца, количество строк. Мониторинг использования диска.

Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.

• Zabbix и PostgreSql
• Monitoring Database Activity
• pgFouine - a PostgreSQL log analyzer
• bucardo.org check_postgres - Perl cкрипт для мониторинга более 20 параметров, определяющих состояние СУБД PostgreSQL.

Текущую активность базы данных легко оценить с помощью команды ps, для вывода в реальном времени (с задержкой 1 секунда) можно использовать утилиту Команда watch с практическим примерами:

# watch -n 1 'ps auxww | grep ^postgres'
postgres 14164  0.0  0.0 188492  5296 ?        S    Dec13   0:46 /usr/bin/postmaster -p 5432 -D /var/lib/pgsql/data
postgres 14166  0.0  0.0 159904  1264 ?        Ss   Dec13   0:05 postgres: logger process                          
postgres 14168  0.0  0.1 188636 27208 ?        Ss   Dec13   0:49 postgres: writer process                          
postgres 14169  0.0  0.0 188492  1348 ?        Ss   Dec13   0:23 postgres: wal writer process                      
postgres 14170  0.0  0.0 188804  1752 ?        Ss   Dec13   0:17 postgres: autovacuum launcher process             
postgres 14171  0.0  0.0 160176  1468 ?        Ss   Dec13   0:45 postgres: stats collector process                 
postgres 21596  0.0  0.1 190228 30476 ?        Ss   Dec27   0:58 postgres: postgres mbill 127.0.0.1(37047) idle    
postgres 21597  0.0  0.0 189716  5672 ?        Ss   Dec27   0:00 postgres: postgres mbill 127.0.0.1(37048) idle

Так как для каждого клиента создаётся своя копия процесса postmaster, то это позволяет подсчитать число активных клиентов. Статусная строка даёт информацию о состоянии клиента. Фразы writer process, stats buffer process и stats collector process соответствуют системным процессам, запущенным самим PostgreSQL при старте. Пользовательские процессы имеют статусную строку вида:

postgres: «пользователь» «база» «хост» «статус»

«пользователь», «база» и «хост» соответствуют имени пользователя «пользователь» подсоединявшегося к базе «база» с компьютера «хост». «статус» может принимать следующие параметры:

• idle - ожидание команды от клиента,
• idle in transaction - ожидание команды от клиента внутри транзакции (между BEGIN и окончанием транзакции),
• SQL- команда - выполняется эта команда, например, SELECT,
• waiting - ждём когда разблокируется занятая другим процессом таблица. Для уточнения из-за чего возникла блокировка, нужно анализировать представление pg_locks.

Представления (Views) сборщика статистики.

• Statistics Collector (Standard Statistics Views): описание системных таблицах, собирающих информацию об активности базы данных.

Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.

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

• Для каких таблиц стоит создать новые индексы (индикатором служит большое количество полных просмотров и большое количество прочитанных блоков).
• Какие индексы вообще не используются в запросах. Их имеет смысл удалить, если, конечно, речь не идёт об индексах, обеспечивающих выполнение ограничений PRIMARY KEY и UNIQUE.
• Достаточен ли объём буфера сервера.
• Возможен "дедуктивный" подход, при котором сначала создаётся большое количество индексов, а затем неиспользуемые индексы удаляются.

select pg_stat_reset();

Стандартные Statistics Views. Вывести все представления каталога

select schemaname,viewname,viewowner from pg_views where schemaname='pg_catalog';

• pg_stat_activity - Каждая строка показывает: процесс сервера, OID базы данных, имя базы данных, ID процесса, OID пользователя, имя пользователя, имя приложения, адрес клиента и порт,время, текущею транзакцию, текущий запрос, статус процесса, текст запроса. Колонки показывающие данные текущего запроса доступны если параметр track_activities включен. Эти колонки доступны только для суперпользователя или пользователя владельца процесса.

  select * from pg_stat_activity;
  # Вывести запросы отсортированные по длительности выполнения
  select datname,client_addr,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;

• pg_stat_bgwriter
• pg_stat_database - Одна строка на кажду БД. Выводит OID базы данных, имя базы данных, количество процессов подключенных к базе(numbackends), кол-во транзакций примененных(xact_commit) и отмененных(xact_rollback), количество прочитанных блоков(blks_read), количество попаданий в буфер(blks_hit), количество выбранных(tup_returned), переданных(tup_fetched), добавленных(tup_inserted), обновленных(tup_updated) и удаленных строк(tup_deleted).

  select * from pg_stat_database;

  Вывести соотношение hit/read. При выполнении запроса PostgreSQL сначала смотрит, есть ли нужные в запросе данные в разделяемой памяти (shared buffers). Если они найдены, засчитывается hit, если нет - делается сравнительно медленный системный вызов fread для поднятия данных с диска или из дискового кеша операционной системы и засчитывается read. В среднем, верно правило: чем больше отношение hit/read, тем лучше настроен PostgreSQL, так как он очень мало читает с диска, в основном извлекая данные из разделяемой памяти. Для большинства не очень больших баз это отношение должно лежать в пределах 5000-10000. Не стремитесь, однако, искусственно завысить настройку shared_buffers, которая прямо определяет hit/read: слишком большие размеры разделяемой памяти ведут к потере производительности в базах с интенсивной записью. Также стоит помнить, что fread может быть довольно быстрым, если данные находятся в дисковом кеше ОС.

  select datname, case when blks_read = 0 then 0 else blks_hit/blks_read end as ratio from pg_stat_database;

• pg_stat_all_tables - Для каждой таблицы в текущей базе данных (включая TOAST таблицы): OID таблицы(relid), схема(schemaname) и имя таблицы(relname), количество последовательны просмотров(seq_scan), количество строк выбранных запросами(seq_tup_read), количество просмотров индексов (все индексы данной таблицы)(idx_scan), количество строк выбранных через сканирование индексов(idx_tup_fetch), количество: пересечений строк(n_tup_ins), обновленных(n_tup_upd), удаленных строк(n_tup_del), количество обновленных HOT строк(n_tup_hot_upd), количество живых(n_live_tup) и мертвых строк(n_dead_tup), время последнего ручного vacuum, время последнего автоматического vacuum, время последнего ручного analyze, время последнего автоматического analyze. Запросы для активной БД:

  select * from pg_stat_all_tables where schemaname='public';
  select relname,last_vacuum,last_autovacuum,last_analyze,last_autoanalyze from pg_stat_all_tables where schemaname='public';

• pg_stat_sys_tables - То же что и pg_stat_all_tables, только системные таблицы.
• pg_stat_user_tables - То же что и pg_stat_all_tables, только пользовательские таблицы. Статистика seq scan/index scan. Список по таблицам: какое количество запросов к ним было выполнено посредством последовательного просмотра; какое количество запросов было выполнено с использованием индексов; а также отношение этих двух чисел. Позволяет оценить, все ли нужные индексы созданы в данной таблице. Если ваши таблицы содержат более нескольких тысяч рядов, последовательный просмотр будет выполняться медленнее просмотра индекса, поэтому в идеальном случае seqscan-ов в таких таблицах быть не должно. Если у вас они все же есть, анализируйте запросы к таким таблицам и создавайте соответствующие индексы. При этом важно не перестараться: чем больше индексов по колонкам таблицы, тем дороже становятся операции обновления данных. Также не забывайте, что после создания индекса таблице нужно делать ANALYZE, иначе планировщик запросов не заметит изменений в структуре таблицы.

  select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio from pg_stat_user_tables order by ratio desc;
  select relname,seq_scan,idx_scan, case when idx_scan = 0 then 100 else seq_scan/idx_scan end as ratio,n_live_tup,n_dead_tup from pg_stat_user_tables order by ratio desc;

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

  select relname,n_tup_ins,n_tup_upd,n_tup_del from pg_stat_user_tables order by n_tup_ins desc;

• pg_stat_all_indexes - Поможет оценить эффективность и частоту использования индексов при реальной работе. Для каждого индекса текущей базы: OID таблицы и OID, схема, имя таблица и индекса, количество просмотров индекса(idx_scan), количество записей возвращенных при сканировании индекса(idx_tup_read), количество живых строк(idx_tup_fetch) таблицы полученных простым сканированием индексов используя этот индекс.

  select * from pg_stat_all_indexes;

• pg_stat_sys_indexes - То же что и pg_stat_all_indexes, только системные таблицы.
• pg_stat_user_indexes - То же что и pg_stat_all_indexes, только пользовательские таблицы.

  select * from pg_stat_user_indexes where schemaname='public';

  Статистика по индексам. Список по индексам: сколько записей из индекса были использованы в запросах по этому индексу; сколько рядов при этом получилось достать из родительской таблицы; разность этих двух чисел. Суть данной статистики проста: если у вас большая разница read-ов и fetch-ей, значит индекс устарел и ссылается на уже несуществующие данные, т.е. не всякий просмотр индекса и чтение из него соответствующего указателя на данные из таблицы (read) вызывает чтение самих данных из таблицы (fetch). В этом случае необходимо перестроить данный индекс, чтобы он соответствовал реальным данным в таблице.

  select relname,indexrelname,idx_tup_read,idx_tup_fetch,(idx_tup_read-idx_tup_fetch) as diff, CASE WHEN idx_tup_read=0 THEN 0 ELSE (idx_tup_read::float4-idx_tup_fetch)/idx_tup_read END as r FROM pg_stat_user_indexes ORDER BY r desc;

• pg_statio_all_tables - Для каждой таблицы текущей базы данных (включая TOAST таблицы), OID таблицы, схема и имя таблицы, количество блоков прочитанных с диска, количество попаданий в буфер, количество блоков прочитанных с диска и попавших в буфер для всех индексов таблицы, количество блоков прочитанных с диска и попавших в буфер that table’s auxiliary TOAST table (if any), количество блоков прочитанных с диска и попавших в буфер для индекса TOAST таблиц.
• pg_statio_sys_tables - То же что и pg_statio_all_tables, только системные таблицы.
• pg_statio_user_tables - То же что и pg_statio_all_tables, только пользовательские таблицы.
• pg_statio_all_indexes - Для каждого индекса текущей базы данных: OID таблицы и индекса, имя таблицы и индекса, количество блоков прочитанных с диска и попаданий в буфер.
• pg_statio_sys_indexes - То же что и pg_statio_all_indexes, только системные таблицы.
• pg_statio_user_indexes - То же что и pg_statio_all_indexes, только пользовательские таблицы
• pg_statio_all_sequences - Для каждой последовательности в текущей базе данных: OID последовательности, схема и имя последовательности, количество прочитанных блоков с диска и попаданий в буфер.
• pg_statio_sys_sequences - То же что и pg_statio_all_sequences, только системные таблицы.
• pg_statio_user_sequences - То же что и pg_statio_all_sequences, только пользовательские таблицы.
• pg_stat_user_functions - Значения времени указано в миллисекундах.

• pg_locks блокировки в PostgreSQL - информация о блокировках в базе данных.

• Explicit Locking - документация о типах блокировок в PostgreSQL.

Команда LOCK TABLE предназначена для блокировки таблиц на время транзакции. Блокировкой называется временное ограничение доступа к таблице (в зависимости от выбранного режима). Сеанс, заблокировавший таблицу, пользуется нормальным доступом; последствия блокировки распространяются только на других пользователей, пытающихся получить доступ к заблокированной таблице.

Некоторые команды SQL автоматически устанавливают блокировку для выполнения своих функций; в таких случаях PostgreSQL всегда выбирает минимально необходимый уровень блокировки. После завершения транзакции блокировка немедленно снимается.

Команда LOCK TABLE без параметра устанавливает максимально жесткий режим блокировки (ACCESS EXCLUSIVE). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.

Ситуация взаимной блокировки (deadlock) возникает в там случае, когда каждая из двух транзакций ожидает снятия блокировки другой транзакцией. Хотя PostgreSQL распознает взаимные блокировки и завершает их командой ROLLBACK, это все равно причиняет определенные неудобства. Приложения не должны сталкиваться с проблемой взаимных блокировок, поэтому проектируйте их так, чтобы объекты всегда блокировались в одинаковом порядке.

• ACCESS SHARE MODE. Устанавливается автоматически командой SELECT для таблиц, из которых производится выборка данных. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровня ACCESS EXCLUSIVE MODE.
• ROW SHARE MODE. Устанавливается автоматически командами SELECT, содержащими секцию FOR UPDATE или FOR SHARE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ROW EXCLUSIVE MODE. Устанавливается автоматически командами UPDATE, INSERT и DELETE. В заблокированных таблицах запрещается выполнение команд ALTER TABLE, DROP TABLE и CREATE INDEX. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней SHARE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE UPDATE EXCLUSIVE MODE . Устанавливается автоматически командами VACUUM (без FULL), ANALYZE и CREATE INDEX CONCURRENTLY.
• SHARE MODE. Устанавливается автоматически командами CREATE INDEX (без CONCURRENTLY). В заблокированных таблицах запрещается выполнение команд INSERT, UPDATE, DELETE, ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW EXCLUSIVE MODE, SHARE ROW EXCLUSIVE MODE, EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• SHARE ROW EXCLUSIVE MOOE. Специальный режим блокировки, практически идентичный режиму EXCLUSIVE MODE, но допускающий установку параллельных блокировок уровня ROW SHARE MODE.
• EXCLUSIVE MODE. Запрещает выполнение команд INSERT, UPDATE, DELETE, CREATE INDEX, ALTER TABLE, DROP TABLE и VACUUM, а также команд SELECT с секцией FOR UPDATE. В этом режиме для заблокированных таблиц также запрещаются параллельные блокировки уровней ROW SHARE MODE, ROW EXCLUSIVE MODE, SHARE MODE, SHARE ROW EXCLUSIVE MODE и ACCESS EXCLUSIVE MODE.
• ACCESS EXCLUSIVE MODE. Устанавливается автоматически командами ALTER TABLE, DROP TABLE и VACUUM. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.

• VACUUM -- garbage-collect and optionally analyze a database
• vacuumdb -- garbage-collect and analyze a PostgreSQL database
• ANALYZE -- collect statistics about a database
• Vacuum Full без полного лока таблицы

Автоматическая сборка мусора (Automatic Vacuuming).

Синтаксис VACUUM:

VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

Синтаксис ANALYZE:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Кроме сборки мусора (VACUUM) производится ещё и анализ (ANALYZE). Периодическое выполнение команды ANALYZE необходимо для нормального функционирования планировщика. Собранная с помощью этой команды статистика позволяет значительно ускорить выполнение SQL- запросов. То есть, если не хочется настраивать автоматическую сборку мусора, то в любом случае её придётся делать только теперь в ручную. Процесс обычной сборки мусора в PostgreSQL (VACUUM без приставки FULL) не блокирует таблиц и может выполняться в фоне, не мешая выполнению запросов. Регулярное исполнение команд VACUUM и ANALYZE обязательно. Это необходимо по той причине, что иначе не получится заново использовать дисковое пространство, которое занимают ранее удалённые или изменённые строки и не удастся обновить статистику для планировщика запросов. И то и другое отрицательно сказывается на эффективности использования ресурсов и производительности запросов. Начиная с версии PostgreSQL 8.1 сервер может самостоятельно автоматически запускать ещё один системный процесс, который, соответственно, так и называется autovacuum daemon. Все настройки для этого процесса хранятся в PostgreSql postgresql.conf. К значениям этих параметров следует отнестись крайне внимательно. Если по каким-то причинам демон было решено не запускать, то в любом случае необходимо производить сборку мусора и набор статистики в ручную.

Основным средством физического и аналитического сопровождения баз данных в PostgreSQL является команда SQL VACUUM и ее аналог — сценарий vacuumdb. Оба средства выполняют две общие функции:

1. удаление всех данных, оставшихся в результате отмены транзакций и других операций, оставляющих временные данные;
2. анализ операций с базами данных, по результатам которого PostgreSQL конструирует более эффективные запросы.

Синтаксис VACUUM:

=# \h VACUUM
Command:     VACUUM
Description: garbage-collect and optionally analyze a database
Syntax:
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]

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

# sudo -u postgres psql mybd
mybd=# VACUUM ANALYZE VERBOSE;

Практика показала, что без более-менее регулярных запусков vacuum full analyze производительность системы постепенно падает, причем чем дальше, тем больше. Разница между vacuum и vacuum full заключается в том, что full физически переписывает на диске всю таблицу таким образом, чтобы в ней не оставалось "дырок" от удаленных или обновленных записей. Но его недостаток в том, что во время работы таблица полностью блокируется(включая и select запросы), что может привести к проблемам на популярном сервере - начнет скапливаться очередь запросов, ожидающих доступа к базе, каждый запрос требует памяти, память кончается, начинается запись в Как посмотреть информацию о swap? Создание SWAP раздела, из-за отсутствия памяти сам vacuum тоже начинает использовать swap и все начинает работать очень-очень медленно. Простой VACUUM (Без FULL) просто восстанавливает пространство и делает его доступным для повторного использования. Эта форма команды умеет работать параллельно с обычными чтение и запись таблицы, без монопольной блокировки.

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

# vacuumdb -U postgres --verbose --analyze --full --dbname=mydb
# crontab
# Запуск "vacuumdb" каждый день в 22:01
1 22 * * *  root vacuumdb -U postgres --quiet --analyze --dbname=mydb &

lcr_id - колонка автоинкрементная (lcr_id | integer | not null default nextval('df_lcr_list_lcr_id_seq'::regclass)). Запрос возвращает значение колонки lcr_id для вставленной записи, в этом случае id записи.

INSERT INTO df_lcr_list (datestart,dateend) VALUES (20120429,20120429) RETURNING lcr_id;

• PostgreSQL 8.4 System Catalogs
• Системные колонки: oid, tableoid, xmin, cmin, xmax, cmax, ctid

Системные таблицы(System Catalogs) PostgreSQL начинаются с префикса pg_.

pgSqlBlocks - это standalone приложение, написанное на языке программирования Java, которое позволяет легко ориентироваться среди процессов и получать информацию о блокировках и ожидающих запросов в СУБД PostgreSQL. Отображается информация о состоянии подключения к БД, а также информация о процессах в БД. Скачать pgSqlBlocks https://pgcodekeeper.org/pgsqlblocks.html.

Для запуска pgSqlBlocks требуется Java JRE версии 1.8 и выше для вашей платформы. Для запуска pgSqlBlocks в Ubuntu установите как описано в этой статье Как установить Java на Ubuntu

• Bucardo репликация PostgreSQL