PostgreSQL DBA常用SQL查询语句

查看帮助命令
DB=# help  –总的帮助
DB=# \h    –SQL commands级的帮助
DB=# \?    –psql commands级的帮助
\dn[S+] [PATTERN],其中[PATTERN]表示可以使用正则表达式,比如\dns f*表示列出所有f开头的schema

按列显示,类似mysql的\G
DB=# \x
Expanded display is on.

显示斜杠\后面的命令对应的语句,ECHO_HIDDEN必须大写
\set ECHO_HIDDEN on

psql -E

查看DB安装目录(最好root用户执行)
find / -name initdb

查看有多少DB实例在运行(最好root用户执行)
find / -name postgresql.conf

查看DB版本
cat  $PGDATA/PG_VERSION
psql –version
DB=# show server_version;
DB=# select version();

查看DB实例运行状态
pg_ctl status

查看所有数据库
psql -l              –查看5432端口下面有多少个DB
psql -p XX -l        –查看XX端口下面有多少个DB
DB=# \l
DB=# select * from pg_database;

创建数据库
createdb database_name
DB=# \h create database  –创建数据库的帮助命令
DB=#  create database database_name

进入或切换到某个数据库
psql –d dbname
DB=# \c dbname

查看当前数据库
DB=# \c
DB=# select current_database();

postgresql数据库启动时间
select pg_postmaster_start_time();

查询当前客户端的端口号
select inet_client_port();

查看与当前会话相关联的服务器进程ID
select pg_backend_pid();

查看配置文件最后一次载入时间
select pg_conf_load_time();

查看数据库文件目录
DB=# show data_directory;
cat $PGDATA/postgresql.conf |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432得出第二列的PID号再ps –ef|grep PID

查看每个数据库对应的目录
DB=# select oid, datname from pg_database;–每个database会在base目录下有一个子目录,base目录里的每一个数字目录对于一个database的 oid
再到文件目录比如ll /pgdata/data/base/,就可以看到和上面oid对应的目录名称
–drop database后,base目录下面这个数据库的子目录也随即删除了

查看表空间(表空间是实例级别的,任意一个数据库下看到的结果都是一样,一个表空间可以让多个数据库使用,而一个数据库可以使用多个表空间。\db还能看到表空间的默认存放目录)
\db
select * from pg_tablespace;

查看语言
select * from pg_language;

查询所有schema,必须到指定的数据库下执行
select * from information_schema.schemata;–普通用户只能看到用户自己有权限登录的schema的信息
SELECT nspname FROM pg_namespace;
\dnS

查看表名
DB=# \dt  –使用默认的search_path参数,只看到public的表名,如果有几个schema,每个schema的表名相同,也只能看到一个schema下的表名,看谁在参数值的前面。如果每个schema下的表名都不一样,则所有表名都可以看到
DB=# select * from  pg_tables WHERE tablename NOT LIKE ‘pg%’ AND tablename NOT LIKE ‘sql_%’ ORDER BY tablename;–普通用户也能查询到整个数据库下的所有表,标准语句
DB=# select table_catalog,table_schema,table_name,table_type,is_insertable_into,is_typed from information_schema.tables where table_schema<>’information_schema’ and table_schema<>’pg_catalog’;–superuser可以查询到所有表,普通用户只能查询到自己owner的表,但是查询结果居然有视图在里面,所以不是标准语句
DB=# SELECT n.nspname as “Schema”,c.relname as “Name”,pg_catalog.pg_get_userbyid(c.relowner) as “Owner” FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind =’r’ AND n.nspname in(‘public’,current_user) ORDER BY 2,1;

查看表结构
DB=# \d tablename  –表名前面加上schema名称就可以不同schema下表的表结构
DB=# select * from information_schema.columns where table_schema=’public’ and table_name=’XX’;

增加多个字段
DB=# alter table tablename add column_name1 data_type,add column_name2 data_type,add column_name3 data_type

查看表对应的目录
DB=# select pg_relation_filepath(‘schema_name.table_name’)  –需要到指定的数据库下执行
这个文件的时间表示的是表的checkpoint时间

查看索引(表名前面没有schema前缀)
DB=# \di  –只能看到public这个schema下的索引
DB=# select * from pg_indexes where tablename=’tbname’; –要到当前数据库下执行
DB=# select * from pg_statio_all_indexes where relname=’tbname’;–要到当前数据库下执行

查看视图
DB=# \dv
DB=# select * from pg_views where schemaname = ‘public’;
DB=# select * from information_schema.views where table_schema = ‘public’;

查看触发器
DB=# select * from information_schema.triggers;

查看序列
DB=# select * from information_schema.sequences where sequence_schema = ‘public’;

查看约束
DB=# select * from pg_constraint where contype = ‘p’
DB=# select a.relname as table_name,b.conname as constraint_name,b.contype as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid and a.relname = ‘cc’;

查看XX数据库的大小
SELECT pg_size_pretty(pg_database_size(‘XX’)) As fulldbsize;

查看所有数据库的大小
select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

查看各数据库数据创建时间:
select datname,(pg_stat_file(format(‘%s/%s/PG_VERSION’,case when spcname=’pg_default’ then ‘base’ else ‘pg_tblspc/’||t2.oid||’/PG_11_201804061/’ end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespace=t2.oid;

按占空间大小,顺序查看所有表的大小
select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname=’public’ order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||’.’||tablename))/1024/1024) “Size_MB” from pg_tables where schemaname=’public’ group by 1;

按占空间大小,顺序查看索引大小
select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname=’public’ order by pg_relation_size(relid) desc;
select schemaname ,round(sum(pg_total_relation_size(schemaname||’.’||indexname))/1024/1024) “Size_MB” from pg_indexes where schemaname=’public’ group by 1;

按占空间大小,顺序查看表包含索引的大小
select indexrelname, pg_size_pretty(pg_total_relation_size(relid)) from pg_stat_user_tables where schemaname=’public’ order by pg_relation_size(relid) desc;

查看各个表空间的大小
select spcname, pg_size_pretty(pg_tablespace_size(spcname)) from pg_tablespace;

查看参数文件
DB=# show config_file;
DB=# show hba_file;
DB=# show ident_file;

查看当前会话的参数值
DB=# show all;

查看参数值
select * from pg_file_settings

查看某个参数值,比如参数work_mem
DB=# show work_mem

修改某个参数值,比如参数work_mem
DB=# alter system set work_mem=’8MB’
–使用alter system命令将修改postgresql.auto.conf文件,而不是postgresql.conf,这样可以很好的保护postgresql.conf文件,加入你使用很多alter system命令后搞的一团糟,那么你只需要删除postgresql.auto.conf,再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。

查看是否归档
DB=# show archive_mode;

查看WAL归档信息,pg_stat_archiver来源于pg_stat_get_archiver()函数,执行\dS+ pg_stat_archiver可看到详细信息
select * from pg_stat_archiver;

查看运行日志的相关配置,运行日志包括Error信息,定位慢查询SQL,数据库的启动关闭信息,checkpoint过于频繁等的告警信息。
show logging_collector;–启动日志收集
show log_directory;–日志输出路径
show log_filename;–日志文件名
show log_truncate_on_rotation;–当生成新的文件时如果文件名已存在,是否覆盖同名旧文件名
show log_statement;–设置日志记录内容
show log_min_duration_statement;–运行XX毫秒的语句会被记录到日志中,-1表示禁用这个功能,0表示记录所有语句,类似mysql的慢查询配置

查看wal日志的配置,wal日志就是redo重做日志
存放在data_directory/pg_wal目录

查看当前用户
DB=# \c
DB=# select current_user;

切换用户,当前用户切换到user1用户
DB=# \c – user1

查看所有用户,及用户包含的权限
DB=# \du
DB=# select * from pg_user;
DB=# select * from pg_shadow;

查看所有角色拥有的权限和包含的用户
DB=# \du
DB=# select * from pg_roles;

psql 终端可以用\du 或\du+ 查看,也可以查看系统表
select * from pg_roles;
select * from pg_user;

查询用户XX的权限,必须到指定的数据库下执行
select * from information_schema.table_privileges where grantee=’XX’;
select * from INFORMATION_SCHEMA.role_table_grants where grantee=’XX’;

创建用户XX,并授予超级管理员权限
create user XXX SUPERUSER PASSWORD ‘A_@#qa23’

备注:创建角色,赋予了login权限,则相当于创建了用户,在pg_user可以看到这个角色
create role “user1” superuser;–pg_roles有user1,pg_user和pg_shadow没有user1
alter role “user1” login;–pg_user和pg_shadow也有user1了

把角色role1赋给user2
GRANT role1 to user2;

查看某个用户是否拥有某个schema的usage权限
\dns+ schema1
–显示结果中第三列access privileges如果有用户1,表示用户1有schema1的usage权限

查看某个用户是否拥有查询某个schema的所有表的权限
\ddp schema1

查询group
select * from pg_group

授权
DB=# \h grant
GRANT ALL PRIVILEGES ON schema schemaname TO dbuser;
grant ALL PRIVILEGES on all tables in schema fds to dbuser;
GRANT ALL ON tablename TO user;
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
grant select on all tables in schema public to dbuser;–给用户读取public这个schema下的所有表
GRANT create  ON schema schemaname TO dbuser;–给用户授予在schema上的create权限,比如create table、create view等
GRANT USAGE  ON schema schemaname TO dbuser;
grant select on schema public to dbuser;–报错ERROR:  invalid privilege type SELECT for schema
–USAGE:
对于LANGUAGE过程语言而言,表示允许使用指定的过程语句(pl/sql,pl/python等)创建相应的函数。目前过程语句上只有这一种权限控制
对于SCHEMA模式而言,表示允许查询模式中对象,当然,如果要查询一个模式中的表,实际上还需要有表的select权限。当然如果没有usage权限仍然有办法可以看见模式下的对象的名字,比如通过查询系统视图pg_tables
对于SEQUENCE序列而言,表示允许使用currval和nextval函数
对于FOREIGN DATA WRAPPER外部数据封装器来说,表示允许被授权者使用外部数据封装器创建新的外部服务器(FOREIGN SERVERS)
对于FOREIGN SERVER外部服务器来说,允许创建外部表(FOREIGN TABLE)
–CREATE:对于数据库,允许在数据库上创建Schema;对于Schema,允许对Schema上创建数据库对象;对于表空间,允许把表或是索引指定到对应的表空间上。
–ALL PRIVILEGES,其中关键字PRIVILEGES可以忽略

在psql中的查看权限的快捷指令
(options: S = show system objects, + = additional detail)
\du或\dg   列出role名称和在它所属的哪个权限组
–du或dg等价,因为postgresql数据库中角色和用户不分
\dns+  列出所有schema和它的schema owner和哪些用户对它有usage权限
\ddp   列出默认权限,列出schema的名称并列出哪些角色对他什么类型的对象有权限
\dts+  列出表属于哪个schema,这些表的owner是谁,表多大,但是只能显示public这个schema的信息
\dp    列出表,视图和序列的访问权限,同\z,但是只能显示public这个schema的信息

\drds [模式1 [模式2]] 列出每个数据库的角色设置
\dp – lists table/view permissions
\dn+ – lists schema permissions
\l+ does not list all users that can access the database

\du *owner*
列出包含owner名称的角色名称和在它所属的哪个权限组
\dns+ s*
列出包含s名称信息的schema和它的schema owner和哪些用户对它有usage权限
\ddp schema1
列出schema1这个schema的名称,列出哪些角色对他什么类型的对象有权限
\dt s*
列出包含s名称的表的信息,这些表属于哪个schema,这些表的owner是谁

\set ECHO_HIDDEN on|off或psql -E来打开\命令后面的语句

database、schema、table_seq_view_etc、table_column 分4个级别来授权。

schema:一个database下可以有多个schema。可以给schema指定一个owner,如果没有指定,那么当前用户就是schema的默认owner。

每个schema有自己的owner,并且db owner可以操作所有schema,db owner可以访问所有schema下的表。

常见问题分析
postgresql ERROR: permission denied for schema
不仅需要授予对schema中表的访问权限,还需要授予对schema本身的访问权限。
手册中有这样一段:默认情况下,用户不能访问他们不拥有的schema中的任何对象。要允许这样做,schema的所有者必须授予此用户对该schema的使用权限,即使该用户有对该schema下所有对象的操作权限(public schema除外)。因此,要么让所创建的用户成为该schema的所有者,要么将schema的使用权授予这个用户。

# GRANT USAGE ON SCHEMA the_schema TO some_user;
接下来再对schema下的TABLES,SEQUENCES,FUNCTIONS等对象进行授权。

alter schema s2 owner to owner_1;
修改schema s2的owner为owner_1,但是s2下的表的权限还是保持原来的owner,并没有也一并成了owner1

— 变更指定表owner
alter table s2.t1 owner to owner_1;

— 或者在不变更表的owner的情况下,批量赋权s2下的所有表权限给owner_1
grant all on all tables in schema s2 to owner_1;

对一个用户授予只读某个schema的权限
grant usage on schema s9 to owner_2;
grant select on all tables in schema s9 to owner_2;
–授权owner_2可以访问s9下面的所有表
— 重要提示:这种方式仅对已经存在的表有效。以后建立的表不会自动有只读权限
如果新建的表也能访问,则操作如下
alter default privileges for user user1 in schema s9 grant select on tables to owner_2;
— 对用户user1以后在schema s9下新建的表,owner_2都可以访问

alter default privileges in schema s9 grant select on tables to owner_2;
–当前用户执行如上语句后,此用户在s9下新建的任何表,owner_2都可以访问(其他用户用户创建的表,owner_2不能访问)
–上述语句不是这个意思:对于任何用户在s9下新建的表,owner_2都可以访问
alter default privileges for user user1,user2 in schema s9 grant select on tables to owner_2;
–以后user1,user2在schema s9下新建的表,用户owner_2都可以访问

备注:目前postgresql没有一种方法,可以使以后任何用户在s9下新建的表,owner_2都可以访问。

查看表上存在哪些索引以及大小
select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘cc’);

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname =’cc’ AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;

查看索引定义
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = ‘cc’;
select pg_get_indexdef(b.indexrelid);

查看过程函数定义
select oid,* from pg_proc where proname = ‘insert_platform_action_exist’; –oid = 24610
select * from pg_get_functiondef(24610);

查看表大小(不含索引等信息)
select pg_relation_size(‘cc’); –368640 byte
select pg_size_pretty(pg_relation_size(‘cc’)) –360 kB
t1=# \dts+ cc

查看表所对应的数据文件路径与大小
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = ’empsalary’;

posegresql查询当前lsn
1、用到哪些方法:
apple=# select proname from pg_proc where proname like ‘pg_%_lsn’;
proname
———————————
pg_current_wal_flush_lsn
pg_current_wal_insert_lsn
pg_current_wal_lsn
pg_last_wal_receive_lsn
pg_last_wal_replay_lsn

2、查询当前的lsn值:
apple=# select pg_current_wal_lsn();
pg_current_wal_lsn
————————–
0/45000098

3、查询当前lsn对应的日志文件
select pg_walfile_name(‘0/1732DE8’);
select pg_walfile_name(pg_current_wal_lsn());

4、查询当前lsn在日志文件中的偏移量
SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());

切换pg_wal日志
select pg_switch_wal();

查询当前lsn在pg_wal日志中的详细信息,/pgdata1/data是默认的数据文件目录,/pgdata1/data/pg_wal是它的子目录
pg_controldata /pgdata1/data

清理pg_wal日志
pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005
表示删除000000010000000000000005之前的所有日志
–pg_wal日志没有设置保留周期的参数,即没有类似mysql的参数expire_logs_days,但是pg_wal日志保留期限受参数wal_keep_segments、max_wal_size的影响

查询逻辑复制中延迟数据量
select  pid, client_addr,application_name,state, sync_state, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay from pg_stat_replication;

查询有哪些slot,任意一个数据库下都可以查,查询的结果都一样
select * from pg_replication_slots;

删除复制槽
/usr/pgsql-10/bin/pg_recvlogical -d postgres –drop-slot –slot=slotname
select pg_drop_replication_slot(‘slotname’);

查询某个用户XX拥有的所有对象权限,要到对应的数据库下执行
SELECT relname,relacl FROM pg_class WHERE relacl::TEXT LIKE ‘%user1%’
备注:这里只有表、视图、序列、索引、物化视图、复合类型、TOAST表、外部表
函数、类型、语言、数据库、表空间需要找对应的系统表pg_proc.proacl , pg_type.typacl , pg_language.lanacl , pg_database.datacl , pg_tablespace.spcacl
–系统权限,postgresql没有存放系统权限的系统表或系统视图,也是说postgresql不像oracle一样有系统权限的概念,poostgresql查不到select on all tables这样的系统权限

查看某用户的拥有对哪些表、视图的权限,要到对应的数据库下执行
select * from INFORMATION_SCHEMA.role_table_grants where grantee=’XX’;
select * from information_schema.table_privileges where grantee=’XX’;

查看usage权限表
select * from information_schema.usage_privileges where grantee=’XX’;

查看存储过程函数相关权限表
select * from information_schema.routine_privileges where grantee=’XX’;

查询某个用户拥有的角色

查询某个角色拥有的权限

查看哪些用户对XX表有哪些权限
\z XX
\dp XX
select relname,relacl from pg_class where relname=’XX’;

创建一个用户,再删除用户,报错
postgres=# create role “lukes.liao”;
CREATE ROLE
postgres=# alter role “lukes.liao” login;
ALTER ROLE
postgres=# grant all privileges on schema fds to “lukes.liao”;
ERROR:  schema “fds” does not exist
postgres=# \c fds
fds=# grant all privileges on schema fds to “lukes.liao”;
GRANT
fds=# grant ALL PRIVILEGES on all tables in schema fds to “lukes.liao”;
GRANT
fds=# \c postgres
postgres=# drop user “lukes.liao”;
ERROR:  role “lukes.liao” cannot be dropped because some objects depend on it
DETAIL:  248 objects in database fds

解决方法
postgres=# \c fds
fds=# select * from INFORMATION_SCHEMA.role_table_grants where grantee=’lukes.liao’;
fds=# revoke all privileges on schema fds from “lukes.liao”;
REVOKE
fds=# drop user “lukes.liao”;
and 147 other objects (see server log for list)
fds=# revoke ALL PRIVILEGES on all tables in schema fds from “lukes.liao”;
REVOKE
fds=# drop user “lukes.liao”;
DROP ROLE

查看消耗cpu的sql
select * from pg_stat_activity where pid=XX;

SELECT procpid, START, now() – START AS lap, current_query  FROM ( SELECT backendid, pg_stat_get_backend_pid (S.backendid) AS procpid, pg_stat_get_backend_activity_start (S.backendid) AS START,pg_stat_get_backend_activity (S.backendid) AS current_query  FROM (SELECT pg_stat_get_backend_idset () AS backendid) AS S) AS S WHERE current_query <> ‘<IDLE>’ and procpid=XX  ORDER BY lap DESC;

查看锁,需要到对应的db里面执行
select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname
from pg_locks a
join pg_class b on a.relation = b.oid

SELECT locker.pid,
pc.relname,
locker.mode,
locker_act.application_name,
least(query_start,xact_start) start_time,
locker_act.state,
CASE
WHEN granted=’f’ THEN
‘wait_lock’
WHEN granted=’t’ THEN
‘get_lock’
END lock_satus,current_timestamp – least(query_start,xact_start) AS runtime,
locker_act.query
FROM pg_locks locker,pg_stat_activity locker_act, pg_class pc
WHERE locker.pid=locker_act.pid
AND NOT locker.pid=pg_backend_pid()
AND application_name<>’pg_statsinfod’
AND locker.relation = pc.oid
AND pc.reltype<>0 –and pc.relname=’t’
ORDER BY  runtime desc;

查看sql语句的执行计划
explain analyze select XX

ANALYZE schema_name.table_name会在表上加上ShareUpdateExclusiveLock锁,这个锁不堵塞读

不用重启,直接加载配置文件的两种方法
1、使用linux命令pg_ctl reload
2、使用psql命令select pg_reload_conf();

客户端连接数统计
select client_addr,count(1) from pg_stat_activity  group by client_addr;

查看活动会话
select pid,usename,datname,query,client_addr from pg_stat_activity where pid<>pg_backend_pid() and state=’active’ order by query;

查看等待事件
select pid,usename,datname,query,client_addr,wait_event_type,wait_event from pg_stat_activity where pid<>pg_backend_pid() and wait_event is not null order by wait_event;

查看哪些字段使用了timestamptz类型
SELECT relname, attname FROM pg_class c,pg_attribute attr WHERE c.oid = attr.attrelid and attisdropped = ‘f’ and atttypid=1184 and relname !~ ‘^pg_’;

制作快捷方式
编辑家目录下.psqlrc文件,格式如下:
\set short_command ‘SQL;’
–short_commad为自定义的快捷命令名称
–SQL里的单引号需要转义
–需要带分号

使用方法
DB=> :short_command

查看配置文件信息
select name,setting from pg_settings where category=’File Locations’;

查看是否自动提交
postgres=# \echo :AUTOCOMMIT

FDW的一些参考语句
select * from pg_extension;
select * from pg_foreign_data_wrapper;
select * from pg_foreign_server;
select * from pg_foreign_table;
select * from pg_user_mappings;

查询表的碎片率
select n_dead_tup,(case when n_live_tup > 0 then n_dead_tup::float8/n_live_tup::float8 else 0 end) as “dead_tup/live_tup_pert” from pg_stat_all_tables

检查autovacuum是否已对膨胀的表进行过处理
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum FROM pg_stat_all_tables
ORDER BY n_dead_tup / (n_live_tup* current_setting(‘autovacuum_vacuum_scale_factor’)::float8 + current_setting(‘autovacuum_vacuum_threshold’)::float8)
DESC LIMIT 10;

查询哪些SQL语句消耗cpu
ps aux | grep postgres | sort -n -r -k 3 | head -10
select procpid, start, now()-start as last_time, current_query
from
(select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query
from
(select pg_stat_get_backend_idset() as backendid) as s
) as s
where procpid IN (spid);

检查堵塞
select a.txn_owner, a.txn_db, a.xid, a.pid, a.txn_start, a.lock_mode, a.relation as table_id,nvl(trim(c.”name”),d.relname) as tablename, a.granted,b.pid as blocking_pid ,datediff(s,a.txn_start,getdate())/86400||’ days ‘||datediff(s,a.txn_start,getdate())%86400/3600||’ hrs ‘||datediff(s,a.txn_start,getdate())%3600/60||’ mins ‘||datediff(s,a.txn_start,getdate())%60||’ secs’ as txn_duration
from svv_transactions a
left join (select pid,relation,granted from pg_locks group by 1,2,3) b
on a.relation=b.relation and a.granted=’f’ and b.granted=’t’
left join (select * from stv_tbl_perm where slice=0) c
on a.relation=c.id
left join pg_class d on a.relation=d.oid
where  a.relation is not null;

Analyze表
select ‘analyze ‘||schemaname||’.’||tablename||’;’ from pg_tables where schemaname not in (‘pg_catalog’,’information_schema’,’dms_wam’,’public’,’admin’);

Vacuum表
select ‘vacuum ‘||schemaname||’.’||tablename||’;’ from pg_tables where schemaname not in (‘pg_catalog’,’information_schema’,’dms_wam’,’public’,’admin’);

数据库尺寸函数

名字    返回类型    描述
pg_column_size(any)    int    存储一个指定的数值需要的字节数(可能压缩过)
pg_database_size(oid)    bigint    指定 OID 代表的数据库使用的磁盘空间
pg_database_size(name)    bigint    指定名称的数据库使用的磁盘空间
pg_relation_size(oid)    bigint    指定 OID 代表的表或者索引所使用的磁盘空间
pg_relation_size(text)    bigint    指定名称的表或者索引使用的磁盘空间。表名字可以用模式名修饰。
pg_size_pretty(bigint)    text    把字节计算的尺寸转换成一个人类易读的尺寸。
pg_tablespace_size(oid)    bigint    指定 OID 代表的表空间使用的磁盘空间
pg_tablespace_size(name)    bigint    指定名字的表空间使用的磁盘空间
pg_total_relation_size(oid)    bigint    指定 OID 代表的表使用的磁盘空间,包括索引和压缩数据。
pg_total_relation_size(text)    bigint    指定名字的表所使用的全部磁盘空间,包括索引和压缩数据。表名字可以用模式名修饰。

锁函数

名字    返回类型    描述
pg_advisory_lock(keybigint)    void    获取排它咨询锁
pg_advisory_lock(key1int, key2 int)    void    获取排它咨询锁
pg_advisory_lock_shared(keybigint)    void    获取共享咨询锁
pg_advisory_lock_shared(key1int, key2 int)    void    获取共享咨询锁
pg_try_advisory_lock(keybigint)    boolean    尝试获取排它咨询锁
pg_try_advisory_lock(key1int, key2 int)    boolean    尝试获取排它咨询锁
pg_try_advisory_lock_shared(keybigint)    boolean    尝试获取共享咨询锁
pg_try_advisory_lock_shared(key1int, key2 int)    boolean    尝试获取共享咨询锁
pg_advisory_unlock(keybigint)    boolean    释放排它咨询锁
pg_advisory_unlock(key1int, key2 int)    boolean    释放排它咨询锁
pg_advisory_unlock_shared(keybigint)    boolean    释放共享咨询锁
pg_advisory_unlock_shared(key1int, key2 int)    boolean    释放共享咨询锁
pg_advisory_unlock_all()    void    释放当前会话持有的所有咨询锁

原创文章,作者:奋斗,如若转载,请注明出处:https://blog.ytso.com/233247.html

(0)
上一篇 2022年1月23日 17:17
下一篇 2022年1月23日

相关推荐

发表回复

登录后才能评论