Меню

Postgres посмотреть размер таблиц



15 полезных команд PostgreSQL

Обложка: 15 полезных команд PostgreSQL

В сети много руководств по PostgreSQL, которые описывают основные команды. Но при погружении глубже в работу возникают такие практические вопросы, для которых требуются продвинутые команды.

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

Получение информации о базе данных

Размер базы данных

Чтобы получить физический размер файлов (хранилища) базы данных, используем следующий запрос:

Результат будет представлен как число вида 41809016 .

current_database() — функция, которая возвращает имя текущей базы данных. Вместо неё можно ввести имя текстом:

Для того, чтобы получить информацию в человекочитаемом виде, используем функцию pg_size_pretty :

В результате получим информацию вида 40 Mb .

Перечень таблиц

Иногда требуется получить перечень таблиц базы данных. Для этого используем следующий запрос:

information_schema — стандартная схема базы данных, которая содержит коллекции представлений (views), таких как таблицы, поля и т.д. Представления таблиц содержат информацию обо всех таблицах баз данных.

Запрос, описанный ниже, выберет все таблицы из указанной схемы текущей базы данных:

В последнем условии IN можно указать имя определенной схемы.

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

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

Функция pg_relation_size возвращает объём, который занимает на диске указанный слой заданной таблицы или индекса.

Имя самой большой таблицы

Для того, чтобы вывести список таблиц текущей базы данных, отсортированный по размеру таблицы, выполним следующий запрос:

Для того, чтобы вывести информацию о самой большой таблице, ограничим запрос с помощью LIMIT :

relname — имя таблицы, индекса, представления и т.п.
relpages — размер представления этой таблицы на диске в количествах страниц (по умолчанию одна страницы равна 8 Кб).
pg_class — системная таблица, которая содержит информацию о связях таблиц базы данных.

Перечень подключенных пользователей

Чтобы узнать имя, IP и используемый порт подключенных пользователей, выполним следующий запрос:

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

Чтобы узнать активность соединения конкретного пользователя, используем следующий запрос:

Работа с данными и полями таблиц

Удаление одинаковых строк

Если так получилось, что в таблице нет первичного ключа (primary key), то наверняка среди записей найдутся дубликаты. Если для такой таблицы, особенно большого размера, необходимо поставить ограничения (constraint) для проверки целостности, то удалим следующие элементы:

  • дублирующиеся строки,
  • ситуации, когда одна или более колонок дублируются (если эти колонки предполагается использовать в качестве первичного ключа).

Рассмотрим таблицу с данными покупателей, где задублирована целая строка (вторая по счёту).

Удалить все дубликаты поможет следующий запрос:

Уникальное для каждой записи поле ctid по умолчанию скрыто, но оно есть в каждой таблице.

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

Usetech , Удалённо , По итогам собеседования

Теперь рассмотрим случай, когда повторяются значения полей.

Если допустимо удаление дубликатов без сохранения всех данных, выполним такой запрос:

Если данные важны, то сначала нужно найти записи с дубликатами:

Перед удалением такие записи можно перенести во временную таблицу или заменить в них значение customer_id на другое.

Общая форма запроса на удаление описанных выше записей выглядит следующим образом:

Безопасное изменение типа поля

Может возникнуть вопрос о включении в этот список такой задачи. Ведь в PostgreSQL изменить тип поля очень просто с помощью команды ALTER . Давайте для примера снова рассмотрим таблицу с покупателями.

Для поля customer_id используется строковый тип данных varchar . Это ошибка, так как в этом поле предполагается хранить идентификаторы покупателей, которые имеют целочисленный формат integer . Использование varchar неоправданно. Попробуем исправить это недоразумение с помощью команды ALTER :

Читайте также:  04 Таблицы Эволюция Эволюция нервной системы организмов

Но в результате выполнения получим ошибку:

ERROR: column “customer_id” cannot be cast automatically to type integer
SQL state: 42804
Hint: Specify a USING expression to perform the conversion.

Это значит, что нельзя просто так взять и изменить тип поля при наличии данных в таблице. Так как использовался тип varchar , СУБД не может определить принадлежность значения к integer . Хотя данные соответствуют именно этому типу. Для того, чтобы уточнить этот момент, в сообщении об ошибке предлагается использовать выражение USING , чтобы корректно преобразовать наши данные в integer :

В результате всё прошло без ошибок:

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

Например, преобразуем поле customer_id обратно в varchar , но с преобразованием формата данных:

В результате таблица примет следующий вид:

Поиск «потерянных» значений

Будьте внимательны при использовании последовательностей (sequence) в качестве первичного ключа (primary key): при назначении некоторые элементы последовательности случайно пропускаются, в результате работы с таблицей некоторые записи удаляются. Такие значения можно использовать снова, но найти их в больших таблицах сложно.

Рассмотрим два варианта поиска.

Первый способ
Выполним следующий запрос, чтобы найти начало интервала с «потерянным» значением:

В результате получим значения: 5 , 9 и 11 .

Если нужно найти не только первое вхождение, а все пропущенные значения, используем следующий (ресурсоёмкий!) запрос:

В результате видим следующий результат: 5 , 9 и 6 .

Второй способ
Получаем имя последовательности, связанной с customer_id :

И находим все пропущенные идентификаторы:

Подсчёт количества строк в таблице

Количество строк вычисляется стандартной функцией count , но её можно использовать с дополнительными условиями.

Общее количество строк в таблице:

Количество строк при условии, что указанное поле не содержит NULL :

Количество уникальных строк по указанному полю:

Использование транзакций

Транзакция объединяет последовательность действий в одну операцию. Её особенность в том, что при ошибке в выполнении транзакции ни один из результатов действий не сохранится в базе данных.

Начнём транзакцию с помощью команды BEGIN .

Для того, чтобы откатить все операции, расположенные после BEGIN , используем команду ROLLBACK .

А чтобы применить — команду COMMIT .

Просмотр и завершение исполняемых запросов

Для того, чтобы получить информацию о запросах, выполним следующую команду:

Для того, чтобы остановить конкретный запрос, выполним следующую команду, с указанием id процесса (pid):

Для того, чтобы прекратить работу запроса, выполним:

Работа с конфигурацией

Поиск и изменение расположения экземпляра кластера

Возможна ситуация, когда на одной операционной системе настроено несколько экземпляров PostgreSQL, которые «сидят» на различных портах. В этом случае поиск пути к физическому размещению каждого экземпляра — достаточно нервная задача. Для того, чтобы получить эту информацию, выполним следующий запрос для любой базы данных интересующего кластера:

Изменим расположение на другое с помощью команды:

Но для того, чтобы изменения вступили в силу, требуется перезагрузка.

Получение перечня доступных типов данных

Получим перечень доступных типов данных с помощью команды:

typname — имя типа данных.
typlen — размер типа данных.

Изменение настроек СУБД без перезагрузки

Настройки PostgreSQL находятся в специальных файлах вроде postgresql.conf и pg_hba.conf . После изменения этих файлов нужно, чтобы СУБД снова получила настройки. Для этого производится перезагрузка сервера баз данных. Понятно, что приходится это делать, но на продакшн-версии проекта, которым пользуются тысячи пользователей, это очень нежелательно. Поэтому в PostgreSQL есть функция, с помощью которой можно применить изменения без перезагрузки сервера:

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

Мы рассмотрели команды, которые помогут упростить работу разработчикам и администраторам баз данных, использующим PostgreSQL. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

Читайте также:  Тсуеки таблица соответствия оригиналам воблеры

Источник

Postgres посмотреть размер таблиц

  • Главная
  • Мои увлечения
  • О блоге
  • Путешествия
  • Работа

Свежие записи

  • Как посмотреть список репозиториев доступных на сервере gitolite?
  • Перенес сайт на хостинг tobeto.biz
  • Как проверить DKIM ?
  • Как найти самые большие базы и таблицы PostgreSQL?
  • Как создать бэкап «схемы» RabbitMQ
  • Рубрики

    • Мои увлечения
    • О блоге
    • Путешествия
    • Работа
    • Разное
  • Облако тегов

    Архивы

    • Февраль 2018
    • Ноябрь 2015
    • Октябрь 2015
    • Июль 2015
    • Май 2015
    • Декабрь 2014
    • Август 2014
    • Июнь 2014
    • Май 2014
    • Апрель 2014
    • Февраль 2014
    • Ноябрь 2013
    • Октябрь 2013
    • Август 2013
    • Июнь 2013
    • Май 2013
    • Март 2013
    • Февраль 2013
    • Январь 2013
    • Декабрь 2012
    • Ноябрь 2012
    • Октябрь 2012
    • Сентябрь 2012
    • Июль 2012
    • Май 2012
    • Апрель 2012
    • Март 2012
    • Февраль 2012
    • Январь 2012
    • Декабрь 2011
    • Ноябрь 2011
    • Октябрь 2011
    • Сентябрь 2011
    • Август 2011
    • Июль 2011
    • Июнь 2011
    • Войти
    • RSS записей
    • RSS комментариев
    • WordPress.org
  • Как найти самые большие базы и таблицы PostgreSQL?

    Февраль 6th, 2018 firefly

    В MySQL, при правильной настройке, размеры баз, примерно, равны размеру каталога в котором она размещается. В PostgreSQL такого простого способа посмотреть объем базы нет. Но можно посмотреть его с помощью запросов:

    Отображаем 10 самых больших БД(подключаться к PostgreSQL надо без указания имени базы):

    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE ‘No Access’
    END AS SIZE
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    END DESC — nulls first
    LIMIT 20

    Отображаем размер таблиц в базе(подключаться к PostgreSQL надо указав имя базы):

    SELECT
    relname as «Table»,
    pg_size_pretty(pg_total_relation_size(relid)) As «Size»,
    pg_size_pretty(pg_total_relation_size(relid) — pg_relation_size(relid)) as «External Size»
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

    Опубликовано в рубрике Работа Метки: mysql, PostgreSQL, СУБД

    Источник

    Postgres посмотреть размер таблиц

    • Главная
    • Мои увлечения
    • О блоге
    • Путешествия
    • Работа

    Свежие записи

    • Как посмотреть список репозиториев доступных на сервере gitolite?
    • Перенес сайт на хостинг tobeto.biz
    • Как проверить DKIM ?
    • Как найти самые большие базы и таблицы PostgreSQL?
    • Как создать бэкап «схемы» RabbitMQ
  • Рубрики

    • Мои увлечения
    • О блоге
    • Путешествия
    • Работа
    • Разное
  • Облако тегов

    Архивы

    • Февраль 2018
    • Ноябрь 2015
    • Октябрь 2015
    • Июль 2015
    • Май 2015
    • Декабрь 2014
    • Август 2014
    • Июнь 2014
    • Май 2014
    • Апрель 2014
    • Февраль 2014
    • Ноябрь 2013
    • Октябрь 2013
    • Август 2013
    • Июнь 2013
    • Май 2013
    • Март 2013
    • Февраль 2013
    • Январь 2013
    • Декабрь 2012
    • Ноябрь 2012
    • Октябрь 2012
    • Сентябрь 2012
    • Июль 2012
    • Май 2012
    • Апрель 2012
    • Март 2012
    • Февраль 2012
    • Январь 2012
    • Декабрь 2011
    • Ноябрь 2011
    • Октябрь 2011
    • Сентябрь 2011
    • Август 2011
    • Июль 2011
    • Июнь 2011
    • Войти
    • RSS записей
    • RSS комментариев
    • WordPress.org
  • Как найти самые большие базы и таблицы PostgreSQL?

    Февраль 6th, 2018 firefly

    В MySQL, при правильной настройке, размеры баз, примерно, равны размеру каталога в котором она размещается. В PostgreSQL такого простого способа посмотреть объем базы нет. Но можно посмотреть его с помощью запросов:

    Отображаем 10 самых больших БД(подключаться к PostgreSQL надо без указания имени базы):

    SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
    CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
    ELSE ‘No Access’
    END AS SIZE
    FROM pg_catalog.pg_database d
    ORDER BY
    CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT’)
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL
    END DESC — nulls first
    LIMIT 20

    Отображаем размер таблиц в базе(подключаться к PostgreSQL надо указав имя базы):

    SELECT
    relname as «Table»,
    pg_size_pretty(pg_total_relation_size(relid)) As «Size»,
    pg_size_pretty(pg_total_relation_size(relid) — pg_relation_size(relid)) as «External Size»
    FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

    Опубликовано в рубрике Работа Метки: mysql, PostgreSQL, СУБД

    Читайте также:  Гипоплазия зубов симптомы и лечение

    Источник

    [PostgreSQL] Работаем руками

    Привет, %username% ! Иногда бывает необходимо поработать ручками с базой данных PostgreSQL, но не для всех бывает очевидно что и как там делать. Поэтому ниже будет список из нескольких полезных примеров команд, которые помогу быстро разобраться и начать. Главное помнить: не тестируйте на production-серверах!

    Начнем с простого, а именно коннекта к базе данных PostgreSQL под самым главным пользователем postgres :

    После этой команды нас встретит приветствие PostgreSQL. Далее мы вольны делать абсолютно всё (в пределах разумного). Для начала посмотрим список всех баз данных которые у нас крутятся на сервере. Делается это следующим образом:

    На вывод нам поступит список всех БД которые созданы на сервере, после чего мы можем подключиться к нужной нам базе и работать с ней — для примера посмотрим все таблицы в ней:

    Как можно догадаться из примера — до знака решетки ( # ) у нас будет наименование базы данных с которой мы сейчас работаем. Подключившись к базе testdb мы посмотрели все таблицы в этой БД. Далее мы можем посмотреть самую большую таблицу в БД:

    В результате нам будет показана самая большая таблица (размер указывается в страницах):

    table_name size_in_pages
    testtb1 299211

    Следующий вопрос: как посмотреть размер все базы данных. Ответ — легко:

    В результате нам покажется размер всей БД:

    И снова размер нам представлен в непонятном виде, но мы можем легко это исправить следующим образом:

    Логичным продолжением будет просмотр всех БД в таком нормально виде:

    database_name size
    sampledb 45 GB
    loremdb_001 21 GB
    ipsumdb 3358 MB

    Посмотрим размер таблицы в базе данных:

    Результатом будет размер таблицы testtable1 , включая индексы. Результат будет отображен сразу в удобном для чтения формате, а не в байтах.

    Если вам нужно узнать размер таблицы без индексов, тогда следует выполнить такой запрос:

    Как узнать текущую версию сервера PostgreSQL?

    Результат будет подобным этому:

    version
    PostgreSQL 9.3.1 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.7.2-5) 4.7.2, 64-bit

    Как выполнить SQL-файл в PostgreSQL?

    Для данной цели существует специальная команда в консольной утилите:

    Где /path/to/file.sql — это путь к вашему SQL-файлу. Обратите внимание, что он должен лежать в доступной для чтения пользователя postgres директории.

    Как показать структуру, индексы и прочие элементы выбранной таблицы в PostgreSQL?
    Для данной цели существует специальная команда в консольной утилите:

    Где testtb1 — имя таблицы
    Результат:

    Table «public.testtb1»
    Column Type Modifiers
    begin_ip ip4 not null
    end_ip ip4 not null
    begin_num bigint not null
    end_num bigint not null
    country_code character(2) not null
    country_name character varying(255) not null
    ip_range ip4r
    Indexes:
    «testtable1_iprange_index» gist (ip_range) WITH (fillfactor=100)

    Как отобразить время выполнения запроса в консольной утилите PostgreSQL?

    После чего все запросы станут отображаться в консольной утилите со временем выполнения.
    Отключаются эти уведомления точно так же, как и включаются — вызовом:

    Как отобразить все команды консольной утилиты PostgreSQL?

    Это наверное самый важный пункт, т.к. любой DBA должен знать как вызвать эту справку! Далее будет несколько примеров более сложных запросов, которые так же могу предоставить ту или иную информацию. Например для сопоставления OID номеров и имен баз и таблиц в contrib есть утилита oid2name .

    Для просмотра размера таблиц для текущей базы:

    Для просмотра общего размера баз можно использовать скрипт:

    Если нужно без индексов, тогда запрос другой:

    Полный размер таблицы и сопутствующих индексов:

    Состояние всех настроек можно посмотреть через функцию pg_show_all_settings() .

    Думаю на этом можно пока притормозить. На первое время хватит и этих данных. На этом всё!

    Источник