一点一滴努力,满仓满谷收成

1 版本

postgres # SELECT version();
或
bash # psql --version

2、server uptime?

SELECT
	date_trunc(
	'second',
	CURRENT_TIMESTAMP - pg_postmaster_start_time ()) AS uptime;

3、Locating the database’s system identifier

bash #pg_controldata <data-directory> | grep "system identifier"

4、空间占用
(1)统计库内各表空间占用

SELECT table_schema || '.' || table_name AS table_full_name, round(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')/1024/1024) AS size_MB
FROM information_schema.tables
where table_schema not in ('pg_catalog','information_schema')
ORDER BY 2 desc;

(2)库空间占用

SELECT pg_database_size(current_database());

SELECT
	datname,
	pg_database_size ( datname ) db_size 
FROM
	pg_database 
ORDER BY
	2 DESC;

(3)表的空间占用

select pg_relation_size('pgbench_accounts');
select pg_total_relation_size('pgbench_accounts');  --带索引
SELECT pg_size_pretty(pg_relation_size('pgbench_accounts')); --输入美化

(4)哪个表占空间最多

SELECT table_name,pg_relation_size(table_schema || '.' || table_name) as
size
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema', 'pg_catalog')
ORDER BY size DESC
LIMIT 10;

5、How many tables are there in a database?

SELECT count(*)
FROM
	information_schema.tables 
WHERE
	table_schema NOT IN ( 'information_schema', 'pg_catalog' );

6、查看一张表的相关依赖

SELECT * FROM pg_constraint
WHERE confrelid = 'orders'::regclass;

7、参数配置

postgres=#SHOW config_file;  --配置文件位置
postgres=#SHOW work_mem;  --显示当前值 
postgres=#SET work_mem = '16MB';  --临时设置
postgres=#RESET work_mem;;  --复位
postgres=# SELECT name, source, setting FROM pg_settings 
            WHERE source != 'default' AND source != 'override'
            ORDER by 2, 1;;  --不是缺省值参数

8、阻止新连接连入

ALTER DATABASE foo_db CONNECTION LIMIT 0;
or
ALTER USER foo CONNECTION LIMIT 0;

9、权限

REVOKE ALL ON table1 FROM user2; --从用户回收权限
\du user2: --查看用户权限
REVOKE ALL ON SCHEMA someschema FROM PUBLIC;  --从public回收某schema权限
GRANT USAGE ON SCHEMA someschema TO webreaders;  --给用户访问某个schema权限
GRANT INSERT, UPDATE, DELETE ON someschema.comments TO webreaders; --授权某个对象
GRANT SELECT ON ALL TABLES IN SCHEMA someschema TO bob;  --所有对象

10、Knowing who is blocking a query

SELECT
  pid,
	datname,
	usename,
	wait_event_type,
	wait_event,
	pg_blocking_pids ( pid ) AS blocked_by,
	query 
FROM
	pg_stat_activity 
WHERE
	wait_event IS NOT NULL
	--and pid = 4477
--	and query like '%GRANT%'
	order by 6 desc ;

11、Detecting an in-doubt prepared transaction

SELECT t.schemaname || '.' || t.relname AS tablename,
l.pid, l.granted
FROM pg_locks l JOIN pg_stat_user_tables t
ON l.relation = t.relid;

12、创建schema

CREATE SCHEMA AAA  AUTHORIZATION BBB;

13、查看数据库里有哪些对象

SELECT
	relkind,
	nspname,
	relname,
	COUNT ( * ) 
FROM
	pg_catalog.pg_class
	C JOIN pg_catalog.pg_namespace N ON ( N.oid = C.relnamespace ) 
WHERE
	relkind IN ( 'r', 'v', 'm', 'i', 'S', 's', 'f', 'p' ) 
GROUP BY
	relkind,
	nspname,
	relname 
ORDER BY
	relkind,
	nspname,
	relname;

14、查年占资源多的sql,执行时间长的sql

SELECT
    'select pg_terminate_backend(' || pid || ');' AS KILL,
backend_xid,
datname,
usename,
client_addr,
application_name,
STATE,
wait_event,
backend_start,
xact_start,
xact_stay,
query_start,
query_stay,
REPLACE ( QUERY, chr ( 10 ), ' ' ) AS QUERY 
FROM
    (
    SELECT
        pgsa.pid,
        pgsa.datname AS datname,
				pgsa.wait_event as wait_event,
        pgsa.usename AS usename,
        pgsa.client_addr client_addr,
        pgsa.application_name AS application_name,
        pgsa.STATE AS STATE,
        pgsa.backend_xid,
        pgsa.backend_start AS backend_start,
        pgsa.xact_start AS xact_start,
        EXTRACT ( epoch FROM ( now( ) - pgsa.xact_start ) ) AS xact_stay,
        pgsa.query_start AS query_start,
        EXTRACT ( epoch FROM ( now( ) - pgsa.query_start ) ) AS query_stay,
        pgsa.QUERY AS QUERY 
    FROM
        pg_stat_activity AS pgsa 
    WHERE
        pgsa.STATE != 'idle'
        AND pgsa.STATE != 'idle in transaction'
        AND pgsa.STATE != 'idle in transaction (aborted)'
				--and query like '%count%'
    ) idleconnections 
WHERE
    backend_xid IS NULL
		and query not like 'autovacuum%'
		and usename<>'repmgr'
	--	and xact_stay >500
ORDER BY
    query_stay DESC 
    --LIMIT 100;