查看帮助命令
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