PostgreSQL
main tips stats roles efm_failover

Links

PSQL

  • toggle pager: \pset pager

Filesystem

Spazio ocupato dai DB

\l+
SELECT
    pg_database.datname,
    pg_size_pretty(pg_database_size(pg_database.datname)) AS size
    FROM pg_database;

Transazioni attive o appese

select pid,datname,usename, xact_start,state,left(query,64) query from pg_stat_activity where state != 'idle' order by xact_start;

Transazioni attive o appese da oltre 15 minuti

select pid, usename, state, waiting wait, to_char(xact_start,'DD-MON HH24:MI:SS') xact_start, to_char(extract(epoch from (age(now(),xact_start)))/3600,99999.99) x_hrs, left(query,72) query
    from pg_stat_activity
    where state != 'idle' and query NOT LIKE 'autovacuum%'
    and xact_start + interval '15 minutes' < now()
    order by xact_start;

Conta le transazioni attive o appese da oltre due ore

select count (*) x_olds 
    from pg_stat_activity 
    where state <> 'idle' 
        and query NOT LIKE 'autovacuum%' 
        and xact_start + interval '2 hours' < now();

N.B. per FW MNP รจ opportuno escludere anche le query SHOW data_path generate da un client Oracle.

File CSV con elenco transazioni

COPY (select datid,datname,pid,usename,datname,xact_start,state,query from pg_stat_activity where datname='MNP' and state != 'idle') to '/tmp/transazioni.csv' (format csv, delimiter ';');

Blocchi letti di tutte le tabelle di un DB

select schemaname, relname, heap_blks_read from pg_statio_user_tables order by 3 desc;

Killare una transazione

Safe - SIGINT

select pg_cancel_backend(pid);

Immediate - SIGTERM

select pg_terminate_backend(pid);

SQL

Numero di giorni tra due date

Torna INT

SELECT (EXTRACT(epoch FROM (SELECT (NOW() - '2014-08-02 08:10:56')))/86400)::int

Human redeable

select date 'now()' - date '2000-01-01 00:00:00' days_in_new_millenium;

GRANTS di una tabella

\c MNP  
\prompt 'Table name? ' tabella
select a.tablename,b.usename,HAS_TABLE_PRIVILEGE(usename,tablename, 'select') as select,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'insert') as insert,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'update') as update,
  HAS_TABLE_PRIVILEGE(usename,tablename, 'delete') as delete, 
  HAS_TABLE_PRIVILEGE(usename,tablename, 'references') as references  from pg_tables a , pg_user b 
where a.tablename= :'tabella';

DDL

Ruoli (utenti)

pg_dumpall  -U enterprisedb -r -f roles.ddl
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License