PID a DB

-- Zoznam DB a ich PID
select datid, datname, pid, usename, client_addr, client_hostname, client_port from pg_stat_activity;

-- Kill proces
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity

-- zabitie všetkých prebiehajúcich relácií:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'dvd_rental'
  AND pid <> pg_backend_pid();

-- 1. Presunutie tabuľky s údajmi z jednej schémy do druhej
UPDATE pg_catalog.pg_class
SET relnamespace = (SELECT oid FROM
pg_catalog.pg_namespace
                    WHERE nspname =
'destination_schema')
WHERE relnamespace = (SELECT oid FROM
pg_catalog.pg_namespace
                      WHERE nspname =
'source_schema')
AND relname = 'table_name';

-- 2. Presunutie tabuľky s údajmi z jednej schémy do druhej
UPDATE pg_catalog.pg_type
SET typnamespace = (SELECT oid FROM
pg_catalog.pg_namespace
                    WHERE nspname =
'destination_schema')
WHERE typnamespace = (SELECT oid FROM
pg_catalog.pg_namespace
                      WHERE nspname =
'source_schema')
AND typname = 'table_name';

-- Verzie
SELECT VERSION();
SHOW SERVER_VERSION;
SHOW server_version;
SELECT current_setting('server_version_num');
SHOW server_version_num;

-- Velkosti docasnych tabuliek
SELECT
n.nspname as SchemaName
,c.relname as RelationName
,CASE c.relkind
WHEN 'r' THEN 'table'
WHEN 'v' THEN 'view'
WHEN 'i' THEN 'index'
WHEN 'S' THEN 'sequence'
WHEN 's' THEN 'special'
END as RelationType
,pg_catalog.pg_get_userbyid(c.relowner) as RelationOwner
,pg_size_pretty(pg_relation_size(n.nspname ||'.'|| c.relname)) as RelationSize
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
                ON n.oid = c.relnamespace
WHERE  c.relkind IN ('r','s')
AND  (n.nspname !~ '^pg_toast' and nspname like 'pg_temp%')
ORDER BY pg_relation_size(n.nspname ||'.'|| c.relname) DESC

-- Slow query
select query, calls, total_time, min_time, max_time, mean_time, stddev_time, rows from pg_stat_statements order by mean_time desc;


-- Miesto toast
SELECT
    relname
    ,relpages
FROM pg_class,
     (SELECT reltoastrelid
      FROM pg_class) AS ss
WHERE oid = ss.reltoastrelid OR
      oid = (SELECT indexrelid
             FROM pg_index
             WHERE indrelid = ss.reltoastrelid)
ORDER BY relname;