Администрирование PostgreSQL. Несколько полезных запросов и команд

Создание и удаление базы данных.

createdb dbname

dropdb dbname

Дамп базы данных.

pg_dump dbname > dbdump.sql

С удаленного хоста:

pg_dump -h hostname dbname > dbdump.sql

Импорт данных из дампа в существующую базу данных.

psql -f dump.sql -d dbname

или

psql -f dump.sql dbname

Для удаленного хоста:

psql -h hostname -f dump.sql -d dbname

Сборка мусора и анализ базы данных.

VACUUM (VERBOSE, ANALYZE);

Для одной таблицы:

VACUUM (VERBOSE, ANALYZE) tablename;

Статистика использования VACUUM и ANALYZE.

SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze 
FROM pg_stat_user_tables;

Список таблиц с наличием «мёртвых» строк.

SELECT relname, n_dead_tup, last_vacuum, last_autovacuum
FROM pg_catalog.pg_stat_all_tables
WHERE n_dead_tup>0
ORDER BY n_dead_tup DESC;

Просмотр всех подключений к базе данных.

SELECT * FROM pg_stat_activity WHERE datname='DBNAME';

См. также Как удалить базу данных PostgreSQL, если есть активные подключения к ней?

Просмотр выполняющихся запросов.

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

Для версий 9.1 и ниже:

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

Отменить текущий запрос в обслуживающем процессе.

SELECT pg_cancel_backend(pid);

Код активного процесса (pid) можно получить из столбца pid представления pg_stat_activity (см. выше информацию по просмотру выполняющихся запросов).

Завершить обслуживающий процесс.

SELECT pg_terminate_backend(procpid);

Код активного процесса (pid) можно получить из столбца pid представления pg_stat_activity (см. выше информацию по просмотру всех подключений к базе данных).

Список запросов выполняющихся более чем.

SELECT now()-query_start as runtime, usename, datname, waiting, state, query
FROM  pg_stat_activity
WHERE now()-query_start > '1 minutes'::interval
ORDER BY runtime DESC;

Для версий 9.1 и ниже:

SELECT now()-query_start as runtime, usename, datname, waiting, current_query
FROM  pg_stat_activity
WHERE now()-query_start > '10 second'::interval AND current_query!='<IDLE>'
ORDER BY runtime DESC;

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

SELECT * FROM pg_user;

Список баз данных и их размеры

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

Более сложный вариант запроса, учитывающий права доступа к БД:

SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner,
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
    THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) 
    ELSE 'No Access' 
  END AS SIZE 
FROM pg_catalog.pg_database d 
ORDER BY 
  CASE WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT') 
    THEN pg_catalog.pg_database_size(d.datname)
    ELSE NULL 
  END DESC;

Размер таблиц базы данных.

SELECT nspname || '.' || relname AS "relation",
   pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size"
 FROM pg_class C
 LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
 WHERE nspname NOT IN ('pg_catalog', 'information_schema')
   AND C.relkind <> 'i'
   AND nspname !~ '^pg_toast'
 ORDER BY pg_total_relation_size(C.oid) DESC;

Частота использования кеша для данных

SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit)  as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
FROM pg_statio_user_tables;

Частота использования кеша для индексов

SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit)  as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
FROM pg_statio_user_indexes;

Частота использования индексов таблиц.

SELECT relname,                                                                                             
  CASE WHEN (seq_scan + idx_scan) != 0
    THEN round(100.000 * idx_scan / (seq_scan + idx_scan) , 3)
    ELSE 0.000
  END AS percent_of_times_index_used,
  n_live_tup AS rows_in_table
FROM pg_stat_user_tables 
ORDER BY n_live_tup DESC;

Бонус.

Поиск зависимых объектов.

Как сделать дамп функций из базы данных.

Как получить список представлений.