活动预告:本周六,在北京将迎来一年一度的 ACOUG年会,在本次年会上,我们将对社区过去一年的工作进行回顾和梳理,并展望和探讨下一年工作的内容,同时,本次年会也开放了直播通道,名额不多,报名从速哦~ 编者的话:PostgreSQL连续两年被评为年度数据库,备受很多DBA的青睐,本文我们一起来了解学习PostgreSQL常用的查询语句有哪些? 查看帮助命令 DB=# help --总的帮助 按列显示,类似MySQL的\G DB=# \x 查看DB安装目录(最好root用户执行) find / -name initdb 查看有多少DB实例在运行(最好root用户执行) find / -name postgresql.conf 查看DB版本 cat $PGDATA/PG_VERSION 查看DB实例运行状态 pg_ctl status 查看所有数据库 psql –l --查看5432端口下面有多少个DB 创建数据库 createdb database_name 进入某个数据库 psql –d dbname 查看当前数据库 DB=# \c 查看数据库文件目录 DB=# show data_directory; 查看表空间 select * from pg_tablespace; 查看语言 select * from pg_language; 查询所有schema,必须到指定的数据库下执行 select * from information_schema.schemata; 查看表名 DB=# \dt --只能查看到当前数据库下public的表名 查看表结构 DB=# \d tablename 查看索引 DB=# \di 查看视图 DB=# \dv 查看触发器 DB=# select * from information_schema.triggers; 查看序列 DB=# select * from information_schema.sequences where sequence_schema = 'public'; 查看约束 DB=# select * from pg_constraint where contype = 'p' 查看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 indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc; 查看参数文件 DB=# show config_file; 查看当前会话的参数值 DB=# show all; 查看参数值 select * from pg_file_settings 查看某个参数值,比如参数work_mem DB=# show work_mem 修改某个参数值,比如参数work_mem DB=# alter system set work_mem='8MB' 查看是否归档 DB=# show archive_mode; 查看运行日志的相关配置,运行日志包括Error信息,定位慢查询SQL,数据库的启动关闭信息,checkpoint过于频繁等的告警信息。 show logging_collector;--启动日志收集 查看wal日志的配置,wal日志就是redo重做日志 存放在data_directory/pg_wal目录 查看当前用户 DB=# \c 查看所有用户 DB=# select * from pg_user; 查看所有角色 DB=# \du 查询用户XX的权限,必须到指定的数据库下执行 select * from information_schema.table_privileges where grantee='XX'; 创建用户XX,并授予超级管理员权限 create user XXX SUPERUSER PASSWORD '123456' 创建角色,赋予了login权限,则相当于创建了用户,在pg_user可以看到这个角色 create role "user1" superuser;--pg_roles有user1,pg_user和pg_shadow没有user1 授权 DB=# \h grant 查看表上存在哪些索引以及大小 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 oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610 查看表大小(不含索引等信息) select pg_relation_size('cc'); --368640 byte 查看表所对应的数据文件路径与大小 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'; 2、查询当前的lsn值: apple=# select pg_current_wal_lsn(); 3、查询当前lsn对应的日志文件 select pg_walfile_name('0/1732DE8'); 4、查询当前lsn在日志文件中的偏移量 SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn()); 切换pg_wal日志 select pg_switch_wal(); 清理pg_wal日志 pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005 查询有哪些slot,任意一个数据库下都可以查,查询的结果都一样 select * from pg_replication_slots; 原创:廖学强 出处:http://blog./30126024/viewspace-2655205/ 另:墨天轮社区有开设专门的PG专栏,欢迎大家参考学习(https://www./db,复制到浏览器或者点击文末左下角“阅读原文”) |
|