Как вывести все таблицы postgres

Как вывести список баз данных и таблиц PostgreSQL с помощью psql

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

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

В этом руководстве объясняется, как отображать базы данных и таблицы на сервере PostgreSQL с помощью psql .

Листинг баз данных

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

При установке пакета PostgreSQL создается административный пользователь с именем «postgres». По умолчанию этот пользователь может подключаться к локальному серверу PostgreSQL без пароля.

Чтобы получить доступ к терминалу psql от имени пользователя postgres, запустите:

В терминале psql выполните мета-команду l или list вывести список всех баз данных:

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

На сервере PostgreSQL по умолчанию созданы три базы данных: template0, template1 и postgres. Первые два — это шаблоны, которые используются при создании новых баз данных.

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

Чтобы получить список всех баз данных без доступа к оболочке psql, используйте переключатель -c как показано ниже:

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

В отличие от мета-команды l приведенный выше запрос покажет только имена баз данных:

Листинговые таблицы

Чтобы сначала вывести список всех таблиц конкретной базы данных, вам необходимо подключиться к ней с помощью c или connect . Пользователь, в который вы вошли в терминал psql, должен иметь возможность подключаться к базе данных.

Например, чтобы подключиться к базе данных с именем «odoo», введите:

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

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

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

Чтобы получить информацию о размерах таблиц и описаниях, используйте dt+ .

Выводы

Вы узнали, как составить список баз данных и таблиц PostgreSQL с помощью команды psql .

Не стесняйтесь оставлять комментарии, если у вас есть вопросы.

Источник

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 по умолчанию скрыто, но оно есть в каждой таблице.

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

Sportmaster Lab , Санкт-Петербург, Москва, Краснодар, можно удалённо , От 100 000 до 350 000 ₽

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

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

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

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

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

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

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

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

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

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. Но это далеко не все возможные приёмы. Если вы сталкивались с интересными задачами, напишите о них в комментариях. Поделимся полезным опытом!

Источник

Список таблиц в схеме PostgreSQL

когда я делаю dt в psql я получаю только список таблиц в текущей схеме ( public по умолчанию).

Как я могу получить список всех таблиц во всех схемах или конкретной схеме?

4 ответов

в определенной схеме:

продвинутые пользователи могут использовать обозначения регулярных выражений, такие как классы символов, например 9, чтобы соответствовать любой цифре. Все специальные символы регулярных выражений работы, как указано в разделе 9.7.3, за исключением . который берется как разделитель, как упоминалось выше, * который переводится на нотацию регулярного выражения ., ? что переводится как ., и$, который соответствует буквально. Вы можете эмулировать эти символы шаблона при необходимости, написав ? для., (Р+|) для R, или (R|) для R?. $ не требуется в качестве символа регулярного выражения, поскольку шаблон должен соответствовать всему имени, в отличие от обычной интерпретации регулярных выражений (другими словами, $ автоматически добавляется к вашему шаблону). Напишите * в начале и / или в конце, если вы не хотите шаблон для фиксации. Обратите внимание, что в двойных кавычках все специальные символы регулярного выражения теряют свои специальные значения и сопоставляются буквально. Кроме того, специальные символы регулярного выражения сопоставляются буквально в шаблонах имен операторов (т. е. аргументе \do).

Вы можете выбрать столы из information_schema

вместо information_schema можно использовать pg_tables :

для тех, кто сталкивается с этим в будущем:

Если вы хотите увидеть список отношений Для несколько схем:

Источник

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

Я пытаюсь изучить администрирование PostgreSQL и начал изучать, как использовать psql инструмент командной строки.

Когда я вхожу в систему psql —username=postgres , как мне перечислить все базы данных и таблицы?

Я попробовал \d , d и , dS+ но ничего не перечислено. Я создал две базы данных и несколько таблиц с помощью pgAdmin III, поэтому я знаю, что они должны быть перечислены.

Обратите внимание на следующие команды:

• \list или \l : список всех баз данных
• \dt : список всех таблиц в текущей базе данных

Вы никогда не увидите таблицы в других базах данных, эти таблицы не видны. Вы должны подключиться к правильной базе данных, чтобы увидеть ее таблицы (и другие объекты).

Для переключения баз данных:

\connect database_name или же \c database_name

Смотрите руководство о PSQL .

Это списки баз данных:

Это список таблиц в текущей базе данных

В Postgresql эти команды терминала выводят список доступных баз данных.

Или команда гласит:

Эти команды выводят это на терминал:

Это доступные базы данных.

В PSQL эти команды выводят список доступных таблиц

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

Это приведет вас к psql-терминалу:

Используйте команду, \d означающую показать все таблицы, представления и последовательности

Затем, чтобы выйти из терминала psql, введите \q и нажмите ввод. Или Ctrl-D делает то же самое. Это таблицы в этой базе данных.

\l это также сокращение для \list . Существует довольно много команд слеша, которые вы можете перечислить в psql, используя \? .

Чтобы получить больше информации о базе данных и списке таблиц, вы можете сделать:

\l+ перечислить базы данных

\d+ перечислить все таблицы в текущей схеме search_path в текущей базе данных.

Из pg_Admin вы можете просто запустить следующее в вашей текущей базе данных, и он получит все таблицы для указанной схемы:

Это даст вам список всех постоянных таблиц (как правило, таблиц, которые вы ищете). Вы можете получить только имена таблиц, если измените * подстановочный знак на просто table_name . Общедоступная table_schema является схемой по умолчанию для большинства баз данных, если только администратор не установил новую схему.

Возможно, вы вставили таблицы в схему, которой нет в пути поиска, или по умолчанию, то есть в public, и поэтому таблицы не будут отображаться с помощью \ dt. Если вы используете схему, скажем, data, вы можете исправить это, запустив

alter database set search_path=data, public;

Выйдите и снова введите psql, и теперь \ dt также покажет вам таблицы в данных схемы.

Источник