Postgres技巧

SELECT
		pid,
		datname,
		usename,
		application_name,
		client_addr,
		client_port,
		to_char (now (), 'YYYY-MM-DD HH24:MI:SS') as now,
		to_char (now () - xact_start, 'DD HH24:MI:SS MS') as xact_time,
		to_char (now () - query_start, 'DD HH24:MI:SS MS') as query_time,
		state,
		to_char (now () - state_change, 'DD HH24:MI:SS MS') as state_time,
		wait_event,
		wait_event_type,
		left (query, 40)
	  FROM
		pg_stat_activity
	  WHERE
		state != 'idle'
		and pid != pg_backend_pid ()
	  ORDER BY
		query_time desc;

WITH sos AS (
			SELECT array_cat(array_agg(pid),
				   array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
			FROM pg_locks
			WHERE NOT granted
		)
		SELECT a.pid, a.usename, a.datname, a.state,
			   a.wait_event_type || ': ' || a.wait_event AS wait_event,
			   current_timestamp-a.state_change time_in_state,
			   current_timestamp-a.xact_start time_in_xact,
			   l.relation::regclass relname,
			   l.locktype, l.mode, l.page, l.tuple,
			   pg_blocking_pids(l.pid) blocking_pids,
			   (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
			   coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
			   a.query
		FROM pg_stat_activity a
			 JOIN sos s on (a.pid = any(s.pids))
			 LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
		ORDER BY lock_depth;

ALTER SYSTEM SET lock_timeout = '10s';

ALTER database postgres SET log_min_duration_statement = '250ms';

ALTER DATABASE postgres SET log_statement = 'all';

ALTER DATABASE postgres SET log_lock_waits = 'on';

ALTER DATABASE mydatabase SET statement_timeout = '60s';

-- 安装pg_stat_statements
-- Create the extension

CREATE EXTENSION pg_stat_statements;

-- Change in config

alter system set shared_preload_libraries='pg_stat_statements';
Restart

-- systemctl restart postgresql
-- Verify changes applied or not.

select * from pg_file_Settings where name='shared_preload_libraries';

SELECT
	total_exec_time,
	mean_exec_time as avg_ms,
	calls,
	query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT count(*),
	   state
FROM pg_stat_activity
GROUP BY state;

SELECT pg_relation_size('table_name');

-- For prettier formatting you can wrap with:

SELECT pg_size_pretty(pg_relation_size('table_name'));

SELECT relname AS relation,
       pg_size_pretty (
         pg_total_relation_size (C .oid)
       ) AS total_size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C .relnamespace)
WHERE nspname NOT IN (
        'pg_catalog',
        'information_schema'
      )
  AND C .relkind <> 'i'
  AND nspname !~ '^pg_toast'
  ORDER BY pg_total_relation_size (C .oid) DESC

SELECT schemaname || '.' || relname AS table,
       indexrelname AS index,
       pg_size_pretty(pg_relation_size(i.indexrelid)) AS "index size",
       idx_scan as "index scans"
FROM pg_stat_user_indexes ui
JOIN pg_index i ON ui.indexrelid = i.indexrelid
WHERE NOT indisunique
  AND idx_scan < 50
  AND pg_relation_size(relid) > 5 * 8192
ORDER BY 
  pg_relation_size(i.indexrelid) / nullif(idx_scan, 0) DESC NULLS FIRST,
  pg_relation_size(i.indexrelid) DESC;

SELECT reltuples::numeric as count
FROM pg_class
WHERE relname='table_name';

CREATE INDEX CONCURRENTLY foobar ON foo (bar);

\x auto

\e

\pset null 👻

\set HISTFILE ~/.psql_history- :DBNAME

psql -E

psql -qtA

psql -qtH

(reverse-i-search)

\! clear

\watch

\set ON_ERROR_ROLLBACK interactive

psql <connection-string> --csv -c 'select * from test;'

\i filename

\pset border 2