sidymar

SELECT pg_size_pretty(pg_database_size('previsao'));

sidymar

SELECT u.usename AS "User name",
  u.usesysid AS "User ID",
  CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
       WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
       WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
       ELSE CAST('' AS pg_catalog.text)
  END AS "Attributes"
FROM pg_catalog.pg_user u

sidymar

SELECT DISTINCT 'DROP TRIGGER ' ||  trigger_name ||' ON ' || event_object_schema || '.' || event_object_table || ';' FROM information_schema.triggers where trigger_name ilike '%bucardo%' and trigger_schema not ilike '%bucardo%' order by 1

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';

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

pg_dumpall -g > roles.sql

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

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

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

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;