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

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

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

psql -U postgres -p SecretPassword

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

postgres=# \l

или

postgres=# \list

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

postgres=# \connect testdb
testdb=# \dt

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

SELECT relname AS "table_name", relpages AS "size_in_pages" FROM pg_class ORDER BY relapses DESC LIMIT 1;

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

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

SELECT pg_database_size( 'testdb' );

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

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

SELECT pg_size_pretty( pg_database_size( 'testdb' ) );

Результат:

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

SELECT pg_database.datname as "database_name", pg_size_pretty(pg_database_size(pg_database.datname)) as size FROM pg_database ORDER by pg_database_size(pg_database.datname) DESC;

Результат:

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

SELECT pg_size_pretty( pg_total_relation_size( 'testtb1' ) );

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

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

SELECT pg_size_pretty( pg_relation_size( 'testtb1' ) );

Результат:

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

SELECT version();

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

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

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

\i /path/to/file.sql

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

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

\d testtb1

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

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

\timing

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

\timing

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

\?

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

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

SELECT relname AS name, relfilenode AS oid, (relpages * 8192 / (1024*1024))::int as size_mb, reltuples as count FROM pg_class WHERE relname NOT LIKE 'pg%' ORDER BY relpages DESC;

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

#!/bin/sh
oid2name=/usr/local/pgsql/bin/oid2name
pg_data_path=/usr/local/pgsql/data/base
{
    $oid2name| grep '='| while read oid delim name; do
        size=`du -s $pg_data_path/$oid|cut -f1`
        echo "$size $name"
    done
}|sort -rn

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

SELECT pg_size_pretty( pg_relation_size( 'table' ) );

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

SELECT pg_total_relation_size('table_name');

Размер столбцов:

SELECT pg_column_size('column_name') FROM 'testtb1';

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

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

Если у тебя есть вопросы, комментарии и/или замечания – заходи в чат, а так же подписывайся на канал.