sidymar

** Find commmonly accessed tables and their use of indexes:

SELECT relname,seq_tup_read,idx_tup_fetch,cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct FROM pg_stat_user_tables WHERE (idx_tup_fetch + seq_tup_read)>0 ORDER BY idx_tup_pct;

Returns output like:

       relname        | seq_tup_read | idx_tup_fetch |      idx_tup_pct       
----------------------+--------------+---------------+------------------------
 schema_migrations    |          817 |             0 | 0.00000000000000000000
 user_device_photos   |          349 |             0 | 0.00000000000000000000

sidymar

SELECT pg_size_pretty(pg_database_size('mydatabasename')) as fulldbsize;

sidymar

SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS blocking_statement
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks 
        ON blocking_locks.locktype = blocked_locks.locktype

sidymar

SELECT format('%I.%I(%s)', ns.nspname, p.proname, oidvectortypes(p.proargtypes)) 
FROM pg_proc p INNER JOIN pg_namespace ns ON (p.pronamespace = ns.oid)
WHERE p.proname ilike '%dim_tempo%'

sidymar

CREATE USER slony SUPERUSER INHERIT CREATEDB CREATEROLE; 


ALTER USER slony PASSWORD 'slony123'; 

sidymar

SELECT i.relname as indname,
      i.relowner as indowner,
      idx.indrelid::regclass,
      am.amname as indam,
      idx.indkey,
      ARRAY(
      SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
      FROM generate_subscripts(idx.indkey, 1) as k
      ORDER BY k
      ) as indkey_names,

sidymar

select 'REFRESH MATERIALIZED VIEW' || ' ' || 'previsao.' || relname 
from pg_class where relkind = 'm' and relname ilike '%vw_mat_onda%'

sidymar

BEGIN
    INSERT INTO dados_ocorridos.temperatura_metar_tst (estacao_idestacao, "data", temperatura, temperatura_ponto_orvalho, fonte_idfonte) 
			VALUES (idEstacao,datahora,"temp", temp_orvalho,2);
  EXCEPTION WHEN unique_violation THEN
    UPDATE dados_ocorridos.temperatura_metar_tst SET temperatura = "temp" , temperatura_ponto_orvalho = temp_orvalho WHERE estacao_idestacao = idEstacao and "data" = datahora;
  END;

sidymar

GRANT SELECT ON ALL TABLES IN SCHEMA previsao TO previsores;


GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA previsao TO previsores;

sidymar

select 
	datname,
	pid,
	usename,
	application_name,
	client_addr,
	backend_start,
	query_start,
	waiting,state,query
from pg_stat_activity