Postgresql запросы

Create Postgresql custom backup

pg_dump -d db_name -v -Fc --blobs --file db_name_`date +\%F`.dump

Переименовать базу данных:

ALTER DATABASE datname RENAME TO newdatname;

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

CREATE DATABASE newdb WITH TEMPLATE originaldb OWNER dbuser;

Мониторинг

Активные запросы:

SELECT pid, state, now() - query_start as query_duration, query FROM pg_stat_activity WHERE NOT (state='idle' OR pid=pg_backend_pid());

Блокировки:

SELECT now() - blockeda.query_start AS waiting_duration ,blockingl.relation::regclass,
  blockeda.pid AS blocked_pid, blockeda.query as blocked_query,
  blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(blockingl.relation=blockedl.relation
  AND blockingl.locktype=blockedl.locktype AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
WHERE NOT blockedl.granted;

Ожидает освобождения блокировки:

SELECT pid, query, now() - query_start  AS waiting_duration
FROM pg_catalog.pg_stat_activity WHERE waiting;

Результат:

  pid  |                     query                     | waiting_duration
-------+-----------------------------------------------+------------------
 45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | 00:03:16.503233
(1 row)

Вьха для просмотра блокировок в текущей БД:

CREATE VIEW lock_monitor AS(
SELECT
  COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
  now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
  blockeda.query as blocked_query, blockedl.mode as blocked_mode,
  blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
  blockingl.mode as blocking_mode
FROM pg_catalog.pg_locks blockedl
JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
JOIN pg_catalog.pg_locks blockingl ON(
  ( (blockingl.transactionid=blockedl.transactionid) OR
  (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
  ) AND blockedl.pid != blockingl.pid)
JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
  AND blockinga.datid = blockeda.datid
WHERE NOT blockedl.granted
AND blockinga.datname = current_database()
);

Результат:

#SELECT * from lock_monitor;
  locked_item  | waiting_duration | blocked_pid |                 blocked_query                 | blocked_mode | blocking_pid |         blocking_query         | blocking_mode
---------------+------------------+-------------+-----------------------------------------------+--------------+--------------+--------------------------------+---------------
 transactionid | 00:01:02.143922  |       45265 | UPDATE toys SET usage = usage+1 WHERE id = 2; | ShareLock    |        45263 | SELECT * FROM toys FOR UPDATE; | ExclusiveLock
(1 row)

Статистика использования индексов в текущей БД:

SELECT
    idstat.relname    				        AS table_name,                  -- имя таблицы
    indexrelname    				        AS index_name,                  -- индекс
    idstat.idx_scan    			                AS index_scans_count,           -- число сканирований по этому индексу
    pg_size_pretty(pg_relation_size(indexrelid))        AS index_size,                  -- размер индекса
    tabstat.idx_scan    			        AS table_reads_index_count,     -- индексных чтений по таблице
    tabstat.seq_scan    			        AS table_reads_seq_count,       -- последовательных чтений по таблице
    tabstat.seq_scan + tabstat.idx_scan    	        AS table_reads_count,           -- чтений по таблице
    n_tup_upd + n_tup_ins + n_tup_del    	        AS table_writes_count,          -- операций записи
    pg_size_pretty(pg_relation_size(idstat.relid))      AS table_size                   -- размер таблицы
FROM
    pg_stat_user_indexes    			        AS idstat
JOIN
    pg_indexes
    ON
    indexrelname = indexname
    AND
    idstat.schemaname = pg_indexes.schemaname
JOIN
    pg_stat_user_tables    			        AS tabstat
    ON
    idstat.relid = tabstat.relid
WHERE
    indexdef !~* 'unique'
ORDER BY
    idstat.idx_scan DESC,
    pg_relation_size(indexrelid) DESC

Create read-only user

-- Create a role
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER tomek WITH PASSWORD 'secret';
GRANT readaccess TO tomek;
CREATE OR REPLACE FUNCTION revoke_all_privs(usr CHAR(50))
returns void as $$
DECLARE
sch VARCHAR(50);
BEGIN
    RAISE NOTICE 'Revoke privs to specified user...';
    FOR sch IN SELECT schema_name from information_schema.schemata where schema_name NOT IN ('pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','information_schema') LOOP
      EXECUTE  'REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA "' || sch || '" FROM ' ||usr;
      EXECUTE  'REVOKE USAGE ON SCHEMA "' || sch || '" FROM ' ||usr;
      EXECUTE  'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || sch || '" REVOKE ALL PRIVILEGES ON TABLES FROM ' ||usr;
    END LOOP;
    RAISE NOTICE 'GRANTED!';
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION grant_ro_privs(usr CHAR(50))
returns void as $$
DECLARE
sch VARCHAR(50);
BEGIN
    RAISE NOTICE 'Granting read only privs to specified user...';
    FOR sch IN SELECT schema_name from information_schema.schemata where schema_name NOT IN ('pg_toast','pg_temp_1','pg_toast_temp_1','pg_catalog','information_schema') LOOP
        EXECUTE 'GRANT USAGE ON SCHEMA "' || sch || '" TO ' ||usr;
        EXECUTE 'GRANT SELECT ON ALL TABLES IN SCHEMA "' || sch || '" TO ' ||usr;
        EXECUTE 'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || sch || '" GRANT SELECT ON TABLES TO ' ||usr;
    END LOOP;
    RAISE NOTICE 'GRANTED!';
END;
$$ LANGUAGE plpgsql;

Drop all foreign table

CREATE OR REPLACE FUNCTION drop_foreign_table(srv CHAR(50))
returns void as $$
declare
  l_rec record;
begin
  for l_rec in (select foreign_table_schema, foreign_table_name 
                from information_schema.foreign_tables where foreign_server_name=srv) loop
     execute format('drop foreign table %I.%I', l_rec.foreign_table_schema, l_rec.foreign_table_name);
  end loop;
end;
$$ LANGUAGE plpgsql;

select drop_foreign_table('foreign_main');