史上最全:PostgreSQL DBA常用SQL查询语句(建议收藏学习) | 您所在的位置:网站首页 › pg数据库添加索引语句 › 史上最全:PostgreSQL DBA常用SQL查询语句(建议收藏学习) |
查看帮助命令本周六,在北京将迎来一年一度的 ACOUG年会,在本次年会上,我们将对社区过去一年的工作进行回顾和梳理,并展望和探讨下一年工作的内容,同时,本次年会也开放了直播通道,名额不多,报名从速哦~ 编者的话:PostgreSQL连续两年被评为年度数据库,备受很多DBA的青睐,本文我们一起来了解学习PostgreSQL常用的查询语句有哪些? 查看帮助命令 代码语言:javascript复制DB=# help --总的帮助 DB=# \h --SQL commands级的帮助 DB=# \? --psql commands级的帮助按列显示,类似MySQL的\G 代码语言:javascript复制DB=# \x Expanded display is on.查看DB安装目录(最好root用户执行) 代码语言:javascript复制find / -name initdb查看有多少DB实例在运行(最好root用户执行) 代码语言:javascript复制find / -name postgresql.conf查看DB版本 代码语言:javascript复制cat $PGDATA/PG_VERSION psql --version DB=# show server_version; DB=# select version();查看DB实例运行状态 代码语言:javascript复制pg_ctl status查看所有数据库 代码语言:javascript复制psql –l --查看5432端口下面有多少个DB psql –p XX –l --查看XX端口下面有多少个DB DB=# \l DB=# select * from pg_database;创建数据库 代码语言:javascript复制createdb database_name DB=# \h create database --创建数据库的帮助命令 DB=# create database database_name进入某个数据库 代码语言:javascript复制psql –d dbname DB=# \c dbname查看当前数据库 代码语言:javascript复制DB=# \c DB=# select current_database();查看数据库文件目录 代码语言:javascript复制DB=# show data_directory; cat $PGDATA/postgresql.conf |grep data_directory cat /etc/init.d/postgresql|grep PGDATA= lsof |grep 得出第二列的PID号再ps –ef|grep PID查看表空间 代码语言:javascript复制select * from pg_tablespace;查看语言 代码语言:javascript复制select * from pg_language;查询所有schema,必须到指定的数据库下执行 代码语言:javascript复制select * from information_schema.schemata; SELECT nspname FROM pg_namespace; \dnS查看表名 代码语言:javascript复制DB=# \dt --只能查看到当前数据库下public的表名 DB=# SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename; DB=# SELECT * FROM information_schema.tables WHERE table_name='ff_v3_ff_basic_af';查看表结构 代码语言:javascript复制DB=# \d tablename DB=# select * from information_schema.columns where table_schema='public' and table_name='XX';查看索引 代码语言:javascript复制DB=# \di DB=# select * from pg_index;查看视图 代码语言:javascript复制DB=# \dv DB=# select * from pg_views where schemaname = 'public'; DB=# select * from information_schema.views where table_schema = 'public';查看触发器 代码语言:javascript复制DB=# select * from information_schema.triggers;查看序列 代码语言:javascript复制DB=# select * from information_schema.sequences where sequence_schema = 'public';查看约束 代码语言:javascript复制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数据库的大小 代码语言:javascript复制SELECT pg_size_pretty(pg_database_size('XX')) As fulldbsize;查看所有数据库的大小 代码语言:javascript复制select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;查看各数据库数据创建时间: 代码语言:javascript复制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;按占空间大小,顺序查看所有表的大小 代码语言:javascript复制select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;按占空间大小,顺序查看索引大小 代码语言:javascript复制select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;查看参数文件 代码语言:javascript复制DB=# show config_file; DB=# show hba_file; DB=# show ident_file;查看当前会话的参数值 代码语言:javascript复制DB=# show all;查看参数值 代码语言:javascript复制select * from pg_file_settings查看某个参数值,比如参数work_mem 代码语言:javascript复制DB=# show work_mem修改某个参数值,比如参数work_mem 代码语言:javascript复制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文件即可实现参数的重新加载。查看是否归档 代码语言:javascript复制DB=# show archive_mode;查看运行日志的相关配置,运行日志包括Error信息,定位慢查询SQL,数据库的启动关闭信息,checkpoint过于频繁等的告警信息。 代码语言:javascript复制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重做日志 代码语言:javascript复制存放在data_directory/pg_wal目录查看当前用户 代码语言:javascript复制DB=# \c DB=# select current_user;查看所有用户 代码语言:javascript复制DB=# select * from pg_user; DB=# select * from pg_shadow;查看所有角色 代码语言:javascript复制DB=# \du DB=# select * from pg_roles;查询用户XX的权限,必须到指定的数据库下执行 代码语言:javascript复制select * from information_schema.table_privileges where grantee='XX';创建用户XX,并授予超级管理员权限 代码语言:javascript复制create user XXX SUPERUSER PASSWORD '123456'创建角色,赋予了login权限,则相当于创建了用户,在pg_user可以看到这个角色 代码语言:javascript复制create role "user1" superuser;--pg_roles有user1,pg_user和pg_shadow没有user1 alter role "user1" login;--pg_user和pg_shadow也有user1了授权 代码语言:javascript复制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:对于程序语言来说,允许使用指定的程序语言创建函数;对于Schema来说,允许查找该Schema下的对象;对于序列来说,允许使用currval和nextval函数;对于外部封装器来说,允许使用外部封装器来创建外部服务器;对于外部服务器来说,允许创建外部表。查看表上存在哪些索引以及大小 代码语言:javascript复制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* 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;查看索引定义 代码语言:javascript复制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);查看过程函数定义 代码语言:javascript复制select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610 select * from pg_get_functiondef();查看表大小(不含索引等信息) 代码语言:javascript复制select pg_relation_size('cc'); --368640 byte select pg_size_pretty(pg_relation_size('cc')) --360 kB查看表所对应的数据文件路径与大小 代码语言:javascript复制SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';posegresql查询当前lsn 1、用到哪些方法: 代码语言:javascript复制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_lsn2、查询当前的lsn值: 代码语言:javascript复制apple=# select pg_current_wal_lsn(); pg_current_wal_lsn -------------------------- 0/450000983、查询当前lsn对应的日志文件 代码语言:javascript复制select pg_walfile_name('0/1732DE8');4、查询当前lsn在日志文件中的偏移量 代码语言:javascript复制SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());切换pg_wal日志 代码语言:javascript复制select pg_switch_wal();清理pg_wal日志 代码语言:javascript复制pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005 表示删除000000010000000000000005之前的所有日志 --pg_wal日志没有设置保留周期的参数,即没有类似mysql的参数expire_logs_days,pg_wal日志永久保留,除非shell脚步删除几天前或pg-rman备份时候设置保留策略查询有哪些slot,任意一个数据库下都可以查,查询的结果都一样 代码语言:javascript复制select * from pg_replication_slots;原创:廖学强 出处:http://blog.itpub.net/30126024/viewspace-2655205/ |
CopyRight 2018-2019 实验室设备网 版权所有 |