Postgresql Common Commands

PSQL 快捷命令

cat ~/.psqlrc

-- check active session
\set active_session 'select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),query from pg_stat_activity where pid<>pg_backend_pid() and state=\'active\' order by query_start desc;'

-- all_reslove_session
\set session 'select pid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),query from pg_stat_activity where pid<>pg_backend_pid() and state=\'idle\' and upper(query) not like \'SET%\' and upper(query) not like \'SHOW%\' and query != \'COMMIT\' order by query_start desc;'
-- check wait events
\set wait_event 'select pid,application_name,client_addr,age(clock_timestamp(),query_start),state,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event_type;'

-- table_size
\set table_size 'select table_name,pg_size_pretty(total_bytes) AS total, pg_size_pretty(index_bytes) AS idx , pg_size_pretty(toast_bytes) AS toast , pg_size_pretty(table_bytes) AS relsize from (select *, total_bytes-index_bytes-COALESCE(toast_bytes,0) AS table_bytes FROM ( SELECT c.oid,nspname AS table_schema, relname AS table_name , c.reltuples AS row_estimate , pg_total_relation_size(c.oid) AS total_bytes , pg_indexes_size(c.oid) AS index_bytes , pg_total_relation_size(reltoastrelid) AS toast_bytes FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE relkind = \'r\' and relname in (select tablename from pg_tables where schemaname=\'public\') ) a) a order by total_bytes desc;'

--database size
\set database_size 'SELECT d.datname AS Name,pg_catalog.pg_get_userbyid(d.datdba) AS Owner,CASE WHEN pg_catalog.has_database_privilege(d.datname, \'CONNECT\') THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))  ELSE \'No Access\'  END AS SIZE FROM pg_catalog.pg_database d ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, \'CONNECT\') THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END DESC LIMIT 20;'

--table static
\set table_stat 'select relname,seq_scan,seq_tup_read,idx_scan,idx_tup_fetch,n_tup_ins,n_tup_upd,n_tup_del,n_tup_hot_upd,n_live_tup,n_dead_tup,n_mod_since_analyze,n_ins_since_vacuum,last_vacuum,last_analyze,vacuum_count from pg_stat_user_tables where relname in (\'devices\',\'device_time\',\'device_mview\',\'gpdevices_view\');'

--redshift running sqls
\set redshift_run 'SELECT pid,starttime,duration,trim(user_name) AS user,trim(query) AS querytxt FROM stv_recents WHERE STATUS = \'Running\' order by starttime desc;'
--redshift run2
\set redshift_run2 'select pid, trim(starttime) as start, to_char(sysdate,\'HH24:MI:SS\') as cur,duration, trim(user_name) as user,substring (query,1,40) as querytxt from stv_recents where status = \'Running\' order by starttime desc;'

\set redshift_disk 'select owner, host, diskno, used, capacity,(used-tossed)/capacity::numeric *100 as pctused from stv_partitions order by owner;'


--redshift disk usage
\set redshift_disk_usage 'select node, used,round(used/2097152.0*100,2) as pct from stv_node_storage_capacity order by node;'

--green running
\set gp_active_session 'select procpid,usename,datname,application_name,client_addr,age(clock_timestamp(), query_start),current_query from pg_stat_activity where current_query !=\'<IDLE>\' order by query_start desc;'

\set gp_run 'select procpid,application_name,client_addr,age(clock_timestamp(), query_start),SUBSTRING(current_query,0,100) from pg_stat_activity where current_query !=\'<IDLE>\' order by query_start desc;'

--redshfit_vacuum_status
\set redshift_vacuum 'select * from svv_vacuum_progress;'