PostgreSQL
main | tips | stats | roles | efm_failover |
Table of Contents
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
page revision: 27, last edited: 02 May 2017 15:41