PostgreSQL - шпаргалка по наиболее часто используемым командам

В последнее время всё чаще приходится сталкиваться с PostgreSQL в своей работе. И сравнивая данную БД с другими, приходишь к выводу, что синтаксис SQL немного отличается от общепринятых стандартов. Столкнувшись с такой проблемой, решил написать мини-шпаргалку по наиболее частым командам, которые понадобятся любому разработчику.

Так же, как и в Oracle, все манипуляции с базой (DML) делаются через системные таблицы и обвязывающие их процедуры и view. Тут нет ни show databases, ни show create table. Но и DML, при этом, транзакционный - можно откатить транзакцию, создающую базу, например.

Установка Postgres

Ищем докер-контейнеры для PostgreSql:

docker search postgres

Устанавливаем Postgre:

docker run --rm --name postgres -e POSTGRES_PASSWORD=my_pass -d -p 5432:5432 -v $HOME/docker/volumes/postgres:/var/lib/postgresql/data  postgres

Соединяемся с базой данных от имени пользователя homestead

psql -U homestead -h localhost

Соединяемся с базой данных test_db

\c test_db;

Выход из базы данных:

\q

Вывести список всех баз данных:

\l

Вывести список всех схем:

\dn

Показать список всех хранимых процедур и функций:

\df

Показать список представлений:

\dv

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

\dt

Список таблиц с более подробной информацией о них:

\dt+

Подробная информация о конкретной таблице:

\d+ table_name

Вывести информацию о функции:

\df+ function_name

Вывести запрос в красивом формате:

\x

Показать список пользователей:

\du

Помощь по командам:

\?

Показать список табличных пространств:

\db

Табличное пространство - позволяют организовать логику размещения файлов объектов базы данных в файловой системе.

По умолчанию создаются:

• pg_default - используется по умолчанию для баз данных template1 и template0

• pg_global - используется для общих системных каталогов.

• pg_tblspc - содержит ссылки на новые табличные пространства.

Показать текущую версию БД:

select version();

Вывести дополнительные аттрибуты строки:

select xmin,xmax,cmin,cmax,ctid from tbl;

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

Полная заморозка

VACUUM FREEZE

VACUUM FULL

vacuumdb --all --freeze

Показать все системные файлики:

\dS+

Мониторинг:

\d pg_stat_activity

SELECT * FROM pg_stat_activity;

\d pg_database

SELECT * FROM pg_database;

\d pg_class

SELECT * FROM pg_class;

CREATE EXTENSION pg_visibility;

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

SELECT txid_current();

Вывести все блокировки трансакций:

SELECT * FROM pg_locks;

Файл конфигурации находится в папке /var/lib/postgresql/data/postgresql.conf