Postgresql отобразить все таблицы

Как вывести список баз данных и таблиц 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 .

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

Источник

PostgreSQL. 10 продвинутых команд для DBA с примерами

// 25 октября, 2013 | 62704 просмотров

Топ 10 самих популярных команд для управления сервером PostgreSQL для настоящих администраторов баз данных (DBA).
Большинство команд подходят как для консольной утилиты psql , так и для запуска через ваш клиент.

1. Как найти самую большую таблицу в базе данных PostgreSQL?

Результатом будет самая большая таблица (в примере testtable1 ) в страницах. Размер одной страницы равен 8KB (т.е. размер таблицы в примере — 2,3GB)

2. Как узнать размер всей базы данных PostgreSQL?

Результатом будет размер базы данных в байтах:

Если вы хотите получить размер в более читаемом («человеческом») формате — «оберните» результат в функцию pg_size_pretty() :

Ну и сразу логичным будет показать все базы данных в читаемом («человеческом») виде, отсортированные от более больших к меньшим

3. Как узнать размер таблицы в базе данных PostgreSQL?

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

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

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

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

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

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

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

6. Как отобразить список всех баз данных сервера PostgreSQL?

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

7. Как отобразить список всех таблиц в базе данных PostgreSQL?

Для данной цели существует специальная команда в консольной утилите что покажет список таблиц в текущей БД.

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

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

Где testtable1 — имя таблицы

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

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

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

Это наверное самый важный пункт, т.к. любой DBA должен знать как вызвать эту справку 🙂

Источник

Show Tables PostgreSQL

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

Различные методы отображения таблиц PostgreSQL

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

• С помощью оболочки SQL (psql).
• Автор pgAdmin4.

Показать таблицы с помощью SQL Shell (psql) в PostgreSQL

Оболочка SQL psql — это терминал, в котором вы можете запускать запросы, и они направляются в PostgreSQL. Это происходит с установкой PostgreSQL, когда вы ее загружаете. Чтобы открыть оболочку SQL, найдите в своей системе «SQL Shell (psql)». После этого на вашем устройстве появится следующий экран:

После того, как вы вошли в оболочку SQL, переходите к следующему шагу. По умолчанию выбран сервер, т.е. localhost. Первоначально я выбрал базу данных по умолчанию, но мы можем изменить ее позже в командной строке. Порт по умолчанию — 5432, и я выбрал имя пользователя в качестве пользователя по умолчанию. Введите пароль, который вы создали при установке PostgreSQL. Когда мы вводим пароль, появится следующее сообщение, и вы будете введены в выбранную вами базу данных, то есть «Postgres».

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

Обратите внимание: вы можете ввести имя базы данных по вашему выбору вместо «Testdb». Например, «\ c имя базы данных».

Мы занесены в нашу базу данных; теперь мы хотим узнать, сколько таблиц содержит база данных Testdb; выполните следующую команду, чтобы отобразить таблицы в базе данных «Testdb»:

Приведенная выше команда отобразит все таблицы, содержащиеся в базе данных Testdb, как:

Мы видим, что он показывает все таблицы в «Testdb» и те, которые мы создали для примеров в предыдущих статьях.

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

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

Команда «\ dt +» покажет таблицы со всей информацией, включая имя таблицы, тип, владельца, сохраняемость, метод доступа, размер и описание.

Показать таблицы с помощью pgAdmin4 в PostgreSQL

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

# SELECT * FROM pg_catalog . pg_tables

AND schemaname! = ‘pg_catalog’

AND schemaname! = ‘information_schema’ ;

Здесь «pg_tables» используется для извлечения информации из таблиц, которые мы ищем в базе данных. Предложение «Где» фильтрует условие, которое мы установили для таблиц отображения. В «schemaname! = ’Pg_temp_4’» указано, что не выбирается «schemaname» в таблице, где оно равно «pg_temp_4». Условие «И» возвращает значения, если оба условия справа и слева определены как истинные.

Общий запрос будет выполняться следующим образом: сначала выберите из «pg_catalog.pg_tables» и не включайте «имя схемы» в таблицу как «pg_temp_4», «pg_catalog» и «information_schema». Вышеупомянутый запрос даст следующие результаты:

Все таблицы отображаются в выводе с использованием pgAdmin4, как и при использовании оболочки SQL (psql). Большинство результатов отфильтровываются из-за условия предложения «Где».

Если вы хотите отобразить все результаты, избегайте использования предложения «Где» в своем операторе.

Вы также можете изменить условие в соответствии с тем, что вы хотите отобразить в выводе. Например, я хочу отображать только таблицы, у которых «schemaname» равно «pg_temp_4», запустите этот запрос:

SELECT * FROM pg_catalog . pg_tables

Это выберет только «pg_temp_4» из таблицы в базе данных «Testdb», которая отобразит этот вывод на экране:

Все таблицы с «schemaname» «pg_temp_4» показаны в приведенных выше результатах вывода.

Заключение

В этом руководстве мы нашли методы отображения таблиц в PostgreSQL с использованием двух разных методов. Сначала мы обсудили, как отображать таблицы с помощью инструмента оболочки SQL (psql); мы вошли в базу данных «Testdb», в которой мы использовали команду «\ dt» для отображения таблиц в этой базе данных. Для получения подробной информации мы использовали команду «\ dt +», чтобы получить размер и описание таблиц в «Testdb». Во-вторых, мы использовали pgAdmin4 для отображения таблиц в PostgreSQL. Вы можете использовать оператор Select для отображения таблиц в PostgreSQL с предложением Where. Предложение «Где» используется для указания условия для отображения определенных таблиц; если вы хотите отобразить все таблицы в своем выводе, тогда нет необходимости использовать предложение «Где».

Оба метода, которые мы использовали в этой статье, эффективны и просты в использовании. но первый метод отображения таблиц с использованием оболочки SQL (psql) позволяет сэкономить время, поскольку вам нужно написать команду меньшего размера. Второй метод является сравнительно масштабируемым, так как вы также можете указать условия в инструменте запросов для отображения отфильтрованных таблиц.

Источник

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

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

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

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

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

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

Источник