sidymar

SELECT pg_cancel_backend(pid of the postgres process); 
---------------------------------------------------------------------
SELECT pg_terminate_backend(pid) 
	from pg_stat_activity 
where query ilike '%autovacuum%'
and pid <> pg_backend_pid()

sidymar

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


-- CHECK INDEX USAGE ---

sidymar

SELECT us.relname, us.n_live_tup, us.n_dead_tup 
	FROM pg_stat_user_tables us 
WHERE us.schemaname = 'dados_ocorridos'
order by n_dead_tup desc

sidymar

SELECT 'DROP FUNCTION ' || ns.nspname || '.' || proname 
       || '(' || oidvectortypes(proargtypes) || ');'
FROM pg_proc INNER JOIN pg_namespace ns ON (pg_proc.pronamespace = ns.oid)
WHERE ns.nspname = 'bucardo'  order by proname;

sidymar

select 'drop table \"' || tablename || '\" cascade;' from pg_tables where schemaname='public'

sidymar

with 
all_tables as
(
SELECT  *
FROM    (
    SELECT  'all'::text as table_name, 
        sum( (coalesce(heap_blks_read,0) + coalesce(idx_blks_read,0) + coalesce(toast_blks_read,0) + coalesce(tidx_blks_read,0)) ) as from_disk, 
        sum( (coalesce(heap_blks_hit,0)  + coalesce(idx_blks_hit,0)  + coalesce(toast_blks_hit,0)  + coalesce(tidx_blks_hit,0))  ) as from_cache    
    FROM    pg_statio_all_tables  --> change to pg_statio_USER_tables if you want to check only user tables (excluding postgres's own tables)
    ) a

sidymar

#!/bin/sh

# This script verifies that the postgresql data directory has been correctly
# initialized.  We do not want to automatically initdb it, because that has
# a risk of catastrophic failure (ie, overwriting a valuable database) in
# corner cases, such as a remotely mounted database on a volume that's a
# bit slow to mount.  But we can at least emit a message advising newbies
# what to do.
#
# It's used directly from the systemd service file as a prelaunch check.

sidymar

pg_dumpall -g > roles.sql

sidymar

SELECT * FROM crosstab
('SELECT gid, inicio_alerta, fim_alerta
	from monitoramento.alerta_gis_audit where gid=80287 and (operacao = ''I''or operacao = ''D'')')
AS ct (gid int, inicio timestamp, fim timestamp);

sidymar

select 
	column_name, 
	data_type, 
	character_maximum_length
from INFORMATION_SCHEMA.COLUMNS 
	where table_name = 'condicoes_tempo_metar_ldm' 
AND table_schema = 'dados_ocorridos';