Узнать размер самых больших таблиц в базе PostgreSQL, создать дамп БД

Узнать размер таблиц

Размер самых больших 20 таблиц

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) DESC
  LIMIT 20;

Размер всех таблиц

SELECT nspname || '.' || relname AS "relation",
    pg_size_pretty(pg_relation_size(C.oid)) AS "size"
  FROM pg_class C
  LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
  WHERE nspname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_relation_size(C.oid) 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
  LIMIT 20;

Дамп БД

Создание дампа

pg_dump dbname > outfile

Восстановление из файла

psql --set ON_ERROR_STOP=on dbname < infile

или для отображения лога и остановки при ошибке

psql -v ON_ERROR_STOP=1

Копирование данных с одной ДБ в другую

pg_dump -h host1 dbname | psql -h host2 dbname

Создание дампа с сжатием GZ

pg_dump dbname | gzip > filename.gz

Восстановление из архива

gunzip -c filename.gz | psql dbname

или

cat filename.gz | gunzip | psql dbname

Ошибка permission_denied при создании расширений бд (добавление прав пользователю)

например ошибка:

permission denied to create extension "uuid-ossp"

$psql (зайти от root)

ALTER USER username WITH SUPERUSER;

Восстановление в docker

docker exec -i posgres_1 psql -v -d dbname -U username < ./sqlscript.sql

Создание копии (бэкапа) таблицы

create table table_name_backup as table table_name -- копирование только схемы, без индексов
insert into table_name_backup select * from table_name

Json, jsonb

Поиск по полю типа json:

json:

{
  "items": {
     "component": {
       "ca0c2211-c660-4255-a976-ef046ffda2ca": {
          "name": "GridComponent"
       }
     }
  }
}

select data->'items'->'component'

Поиск по полю c json типа text

select data::json->'items'->'component'

Поиск по ключу в объекте для любого ключа

select data->'items'->'component', e.value->'name' from contents
    join json_each(data->'items'->'component') e on true
    where type = 'content' and components <> '[]';

json_each создает таблицу вида ключ-значение из json

Сделать копию базы данных

CREATE DATABASE [new_database] WITH TEMPLATE [source_db] owner [OWNER]

при выполнении команды возможна ошибка из-за текущих подключений. Для сброса подключений можно их сбросить

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

Источники

• PostgreSQL. Как узнать размер базы данных и таблиц? : РБ-Софт
• PostgreSQL: Documentation: 9.1: pg_dump
• PostgreSQL: Documentation: 9.1: SQL Dump
• Postgres Pro Standard : Документация: 9.5: 9.15. Функции и операторы JSON : Компания Postgres Professional
• postgresql — Postgres and jsonb — search value at any key — Stack Overflow
• Creating a copy of a database in PostgreSQL — Stack Overflow
• postgresql — psql invalid command \N while restore sql — Stack Overflow
• ruby on rails — permission denied to create extension "uuid-ossp" — Stack Overflow