Где хранится бд которая создана в psql-треминале?
Где, в свою очередь, хранит данные сам PostgreSQL можно посмотреть командой:
show data_directory; Вот эта вся директория (так же известная как PGDATA ) — и есть данные СУБД, включая все созданные базы в этом кластере. Обязательно включая абсолютно всё содержимое по всем симлинкам внутри этой директории! Отдельное представление созданной create datebase вы найти можете внутри каталога PGDATA , но вы обязаны понимать, что без всей целиком директории PGDATA отдельные её части — лишь куча бесполезного бинарного мусора. Для каких-то осмысленных манипуляций с данными PostgreSQL обязательно необходим весь PGDATA со всем содержимым по всем симлинкам.
Отслеживать
ответ дан 30 ноя 2018 в 18:03
21.5k 3 3 золотых знака 27 27 серебряных знаков 53 53 бронзовых знака
В PGDATA лежат простые файлы. Это и есть бд? Я думал у них будет какое-то расширение.
1 дек 2018 в 4:54
Эта директория и есть все данные этого экземпляра postgresql. Расширения файлов? А зачем они нужны? В PGDATA за исключением recovery.conf (либо пара signal файлов начиная с PG12 вместо него, «спасибо» мне), либо, в некоторых инсталляциях, pg_hba.conf и postgresql.conf — вообще руками лезть не надо. Ну и зачем усложнять именования бесполезными надуманными расширениями?
1 дек 2018 в 14:41
data_directory может быть пустая?
3 дек 2018 в 23:24
@Hellseher смотря откуда смотреть. По пути из show data_directory; — не может. В конфигах такая настройка может отсутствовать.
3 дек 2018 в 23:53
В терминологии PostgreSQL это называется — кластером, другими словами директория, где будут хранится ВСЕ создаваемые базы данных, это может быть отдельный диск, NFS, DAS, SAN и т.д.
Все возможные конфигурации хранятся в postgresql.conf файле:
postgres:~$ grep data_directory /etc/postgresql/*/main/postgresql.conf data_directory = '/var/lib/postgresql/9.6/main' # use data in another directory postgres@:~$ ls -la /var/lib/postgresql/*/main total 92 drwx------ 19 postgres postgres 4096 Dec 3 01:40 . drwxr-xr-x 3 postgres postgres 4096 Nov 29 22:43 .. drwx------ 6 postgres postgres 4096 Nov 29 22:43 base drwx------ 2 postgres postgres 4096 Dec 1 00:12 global drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_clog drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_commit_ts drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_dynshmem drwx------ 4 postgres postgres 4096 Nov 29 22:43 pg_logical drwx------ 4 postgres postgres 4096 Nov 29 22:43 pg_multixact drwx------ 2 postgres postgres 4096 Dec 1 00:12 pg_notify drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_replslot drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_serial drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_snapshots drwx------ 2 postgres postgres 4096 Dec 1 00:12 pg_stat drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_stat_tmp drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_subtrans drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_tblspc drwx------ 2 postgres postgres 4096 Nov 29 22:43 pg_twophase -rw------- 1 postgres postgres 4 Nov 29 22:43 PG_VERSION drwx------ 3 postgres postgres 4096 Nov 29 22:43 pg_xlog -rw------- 1 postgres postgres 88 Nov 29 22:43 postgresql.auto.conf -rw------- 1 postgres postgres 133 Dec 1 00:12 postmaster.opts -rw------- 1 postgres postgres 91 Dec 1 00:12 postmaster.pid Ссылки
Структура каталогов и файлов PostgreSQL¶
СУБД PostgreSQL в разных операционных системах имеет практически идентичную структуру каталогов. В данной статье рассматривается стандартная структура каталогов для ОС Ubuntu 12.04.
По умолчанию PostgreSQL устанавливается в папку /var/lib/postgresql//main. Основной каталог СУБД содержит подкаталоги с пользовательскими данными и служебной информацией.
-
postmaster.opts — файл, в котором сдержится командная строка с параметрами, с помощью которой была запущена СУБД. На моем компьютере в этом файле хранится следующая строка
/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"
Имена подкаталогов соответствуют OID’ам баз данных.
root@server: /var/lib/postgresql/9.3/main/base# ls 1/ 12065/ 12070/ 16384/ 24580/
denis=# select oid, datname from pg_database; oid | datname -------+----------- 1 | template1 12065 | template0 12070 | postgres 16384 | denis 24580 | t1 (5 rows)
- транзакция стартовала,
- транзакция успешно завершена,
- транзакция отменена,
- подтранзакция успешно завершена.
Количество хранимых транзакций ограничено параметром autovacuum_freeze_max_age (максимальное значение ~ 2 миллиарда), который в свою очередь регулирует работу автовакума. Поэтому максимальный размер файла может быть ~ 0.5 Гб максимум.
Файлы создаются при вызове процедуры pg_export_snapshot() и существуют до окончания транзакции. Ниже представлена последовательность действий, позволяющая понять, как появляются и исчезают файлы в этом каталоге. Экспорт снапшота в транзакции
BEGIN denis=# SELECT pg_export_snapshot(); pg_export_snapshot -------------------- 00000312-1 (1 row)
В каталоге появился файл, в котором содержится информация о снапшоте
root@server: /var/lib/postgresql/9.3/main/pg_snapshots# ls 00000312-1 root@server: /var/lib/postgresql/9.3/main/pg_snapshots# cat 00000312-1 xid:786 dbid:16384 iso:1 ro:0 xmin:786 xmax:786 xcnt:0 sof:0 sxcnt:0 rec:0
Отмена транзакции
denis=# rollback; ROLLBACK
Файл удален
root@server: /var/lib/postgresql/9.3/main/pg_snapshots# l ----
Ниже показана последовательность действий, демонстрирующая, как в этом каталоге появляются файлы. Создается табличное пространство
denis=# CREATE TABLESPACE tmp LOCATION '/home/denis/1'; CREATE TABLESPACE
Получается список табличных пространств
denis=# select oid, spcname from pg_tablespace; oid | spcname -------+------------ 1663 | pg_default 1664 | pg_global 27936 | tmp (3 rows)
Для каждого пользовательского табличного пространства в каталоге создается символьная ссылка на заданный каталог, именем этой ссылки является OID этого табличного пространства.
/var/lib/postgresql/9.3/main/pg_tblspc# ll total 8 drwx------ 2 postgres postgres 4096 Nov 4 09:51 ./ drwx------ 15 postgres postgres 4096 Nov 4 07:05 ../ lrwxrwxrwx 1 postgres postgres 13 Nov 4 09:51 27936 -> /home/denis/1/
Использование и настройка PostgreSQL
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 настроен так, что каждый локальный пользователь может подсоединиться к базе совпадающей по названию с регистрационным именем клиента, при условии что такая база данных уже создана.
Все объекты (таблицы, индексы …) базы данных в PostgreSQL хранятся в каталоге data/base/OID, т.е. названием каталога содержащего БД, будет не имя БД (как в Движок БД MySQL), а номер (OID) БД.
MVCC — одна из ключевых технологий доступа к данным, которая используется в PostgreSQL. Она позволяет осуществлять параллельное чтение и изменение записей (tuples) одних и тех же таблиц без блокировки этих таблиц. Чтобы иметь такую возможность, данные из таблицы сразу не удаляются, а лишь помечаются как удаленные. Изменение записей осуществляется путем маркировки этих записей как удаленных, и созданием новых записей с измененными полями. Таким образом, история изменений одной записи сохраняется в базе данных и доступна для чтения другими транзакциями. Этот способ хранения записей позволяет параллельным процессам иметь неблокирующий доступ к записям, которые были удалены или изменены в параллельных незакрытых транзакциях. Техника, используемая в этом подходе, относительно простая. У каждой записи в таблицы есть системные скрытые поля xmin, xmax.
xmin — хранит номер транзакции, в которой запись была создана.
xmax — хранит номер транзакции, в которой запись была удалена или изменена.
Перед началом выборки данных PostgreSQL сохраняет снапшот текущего состояния БД. На основании данных снапшота, полей xmin, xmax осуществляется фильтрация записей.
pg_hba.conf идентификация пользователей
pg_hba.conf — настройка политики доступа к базам данных и идентификации пользователей сервера Использование и настройка PostgreSQL.
В этом файле описываются клиентские компьютеры сети, с которых разрешен доступ к SQL серверу Использование и настройка PostgreSQL, а также методы идентификации клиентов. Этот файл может содержать два вида записей:
Запись вида «host». host <имя базы данных> [аргумент для авторизации]
Запись типа «local». Эта запись определяет авторизацию доступа к базе данных локальных пользователей. Идентично «host», за исключением того, что IP- адрес и маска адреса опущены за ненадобностью.
trust в этом режиме авторизации доступа не производится. Соединение считается доверительным.
Каждая запись в файле pg_hba.conf должна полностью умещаться в одной строке. Перенос записей на другую строку запрещен.
Примеры записей pg_hba.conf:
Разрешить всем пользователям доступ с любого хоста к всем базам данных по логину и паролю
# TYPE DATABASE USER ADDRESS METHOD host all all all md5
Разрешить пользователю(dbuser) доступ к базе данных(mother) с любого хоста по логину и паролю
host mother dbuser all md5
Кодировка БД PostgreSQL и locale
PostgreSQL поддерживает только общую для всех баз кластера кодировку, которая должна совпадать с локальной кодировкой (Настройка переменных локализации в Linux), иначе не будут работать строковые функции сортировки, upper/lower и т.п. Локаль общая для всех процессов сервера — соответственно он не может создать две базы в разных кодировках — кодировка всегда одна для всего сервера и всех его БД.
Посмотреть кодировку сервера (show server_encoding) и клиента(show client_encoding):
postgres=# show server_encoding; server_encoding ----------------- UTF8 (1 row)
Т.е. создать базу в другой кодировке можно, но тогда в ней будут неправильно работать функции обработки строк и сортировка строк.
Указывать список кодировок нужно не для createdb (create database), а для подключения клиента к серверу (client_encoding), если кодировка символов которую ожидает программа-клиент не совпадает с её (программы-клиента) текущей системной локалью, с которой она была запущена.
Клиенты администрирования PostgreSQL
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
psql — PostgreSQL interactive terminal.
PL/pgSQL ( (Procedural Language/PostGres Structured Query Language) — процедурное расширение языка SQL, используемое в СУБД PostgreSQL.
В директории /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
procpid содержит PID процесса, которому можно сделать kill при необходимости. Например PID можно узнать запросом(отсортируем по длительности выполнения)
select datname,procpid,now()-query_start as duration,current_query from pg_stat_activity order by duration DESC;
Транзакции в PostgreSQL
В PostgreSQL Транзакция - это список команд SQL, которые находятся внутри блока, начинающегося командой BEGIN и заканчивающегося командой COMMIT.
PostgreSQL фактически считает каждый оператор SQL запущенным в транзакции. Если вы не указываете команду BEGIN, то каждый отдельный оператор имеет неявную команду BEGIN перед оператором и (при успешной отработке оператора) команду COMMIT после оператора. Группа операторов заключаемая в блок между BEGIN и COMMIT иногда называется транзакционным блоком.
Пример запуска транзакции из файла delprices.sql, которая удаляет в БД test777 из таблиц prices и ratesheets строки с >
# nano delprices.sql BEGIN; DELETE FROM prices WHERE ratesheet_id=2; DELETE FROM ratesheets WHERE > Выполним транзакцию для test777:
# sudo -u postgres psql -l # sudo -u postgres psql test777 < delprices.sql
Мониторинг, логи, размер БД PostgreSQL
Размер базы данных PostgreSQL, таблицы, столбца, количество строк. Мониторинг использования диска.
Лог файлы
Лог файлы PostgreSQL находятся в директории pg_log, для Fedora полный путь /var/lib/pgsql/data/pg_log. Детализация лог файлов настраивается в postgresql.conf.
Мониторинг
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 сборщик статистики
Представления (Views) сборщика статистики.
Statistics Collector (Standard Statistics Views): описание системных таблицах, собирающих информацию об активности базы данных.
Если в PostgreSql postgresql.conf разрешён сбор статистики (logging_collector = on), то информация об активности базы данных собирается в специальных системных таблицах.
Информация собранная "статистическим сборником" может оказаться полезной для оценки эффективности базы данных и запросов. Из этих представлений можно узнать, например
Для каких таблиц стоит создать новые индексы (индикатором служит большое количество полных просмотров и большое количество прочитанных блоков).
Какие индексы вообще не используются в запросах. Их имеет смысл удалить, если, конечно, речь не идёт об индексах, обеспечивающих выполнение ограничений PRIMARY KEY и UNIQUE.
Достаточен ли объём буфера сервера.
Возможен "дедуктивный" подход, при котором сначала создаётся большое количество индексов, а затем неиспользуемые индексы удаляются.
Для сброса системной статистики(файл pgstat.stat) в ноль применяется команда
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). Чтобы ограничения были менее жесткими, следует явно задать нужный режим.
Блокировка таблиц возможна только в транзакциях. Выполнение команды LOCK TABLE вне транзакционного блока не приводит к ошибке, но установленная блокировка немедленно снимается. Транзакция создается командой BEGIN; команда COMMIT фиксирует изменения в базе данных и снимает блокировку.
Ситуация взаимной блокировки (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. В этом режиме для заблокированных таблиц запрещаются любые команды или параллельные блокировки любого уровня.
Postgresql где лежат базы
Собссно сабж. Столкнулся с задачей переноса базы на другой раздел диска. Ваще непонятно как указать PostGRE где должна лежать база. Или это нужно указывать серверу 1С 8.1? ХЕЛП!
Версии:
Postgre - win 8.1.5-11.1C
1С - 8.1.6.
В Postgre есть в настройках переменная data_directory. Может это оно?
А в 8-ке тоже лежат файлики в папке установки 1С/Server.
Так где же все ж лежит база и как указать к ней другой путь?
Если у тебя винда, то база хранится
C:/Program Files/PostgreSQL//data/
в linux /var/lib/pgsql/data/
В винде другой путь для хранения баз можно указать при установке PostgreSQL.
Тут на сколько я знаю есть один ньанс: База и сами файлы постгри хранятся в одной папке.
Глупец, лишенный способности посмеяться над собой вместе с другими, не сможет долго выносить программирование. Фредерик Брукс-младший