Шпаргалка PostgreSQL

7 февраля 2023 г. server linux postgres cheatsheet

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

Базовые команды

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

$psql -U postgres -h localhost

Список баз:

\l

Подключиться к базе:

\c dbname

Cписок таблиц в базе:

\dt

Cписок таблиц в базе, в названии которых есть mytable:

\dt *mytable*

Cписок индексов:

\di

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

\x

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

postgres=# select * from pg_user;
     usename     | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
-----------------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres        |       10 | t           | t        | t       | t            | ******** |          |
 testuser        |    16321 | f           | f        | f       | f            | ******** |          |
(2 rows)

postgres=# \x
Expanded display is on.
postgres=# select * from pg_user;
-[ RECORD 1 ]+----------------
usename      | postgres
usesysid     | 10
usecreatedb  | t
usesuper     | t
userepl      | t
usebypassrls | t
passwd       | ********
valuntil     |
useconfig    |
-[ RECORD 2 ]+----------------
usename      | testuser
usesysid     | 16321
usecreatedb  | f
usesuper     | f
userepl      | f
usebypassrls | f
passwd       | ********
valuntil     |
useconfig    |

Работа с пользователями

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

\du

Создать пользователя:

CREATE USER username WITH PASSWORD 'password';

Выдать права на базу:

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

Изменить пароль пользователя:

ALTER USER username WITH PASSWORD 'new_password';

Удалить пользователя:

DROP USER IF EXISTS username;

Обслуживание

Посмотреть размер всех баз:

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

Размер таблиц и индексов:

SELECT
    TABLE_NAME,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        TABLE_NAME,
        pg_table_size(TABLE_NAME) AS table_size,
        pg_indexes_size(TABLE_NAME) AS indexes_size,
        pg_total_relation_size(TABLE_NAME) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || TABLE_NAME || '"') AS TABLE_NAME
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes;

Список всех работающих запросов:

SELECT 
  pid,
  age(clock_timestamp(), query_start),
  usename,
  query 
FROM pg_stat_activity 
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%' 
ORDER BY query_start desc;

Список запросов работающих дольше 5 минут:

SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Остановить запрос по pid:

SELECT pg_cancel_backend(pid);

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

SELECT pg_terminate_backend(pid);

Остановить запросы работающие дольше 5 минут:

SELECT
  pg_terminate_backend(pid),
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes';

Остановить все запросы:

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database()  
  AND pid <> pg_backend_pid();

Бэкапы

Для бэкапов есть консольные утилиты pg_dump и pg_dumpall. Первая делает sql-дамп отдельно взятой базы, а вторая, как следует из названия, делает то же самое для всех баз (+бэкапит пользователей). Есть еще утилита pg_basebackup, она делает полный бэкап инстанса на уровне файлов.

Дамп базы в сжатый файл (опция -C добавляет команду CREATE DATABASE в дамп):

$pg_dump -C -U username -h hostname dbname | gzip > dump.sql.gz

Полный дамп всех баз и пользователей в сжатый файл:

$pg_dumpall -U username -h hostname | gzip > dump.sql.gz

Восстановление базы (или полного дампа, но тогда не нужно указывать dbname) из сжатого файла:

$zcat dump.sql.gz | psql -U username -h hostname dbname