智慧团建网站注册,青岛微信网站制作,wordpress邮箱验证失败,广州巨腾建网站公司查看帮助命令DB# help --总的帮助DB# \h --SQL commands级的帮助DB# \? --psql commands级的帮助按列显示#xff0c;类似MySQL的\GDB# \xExpanded display is on.查看DB安装目录(最好root用户执行)find / -name initdb查看有多少DB实例在运行(最好root用户执行)find / -name…查看帮助命令DB# help --总的帮助DB# \h --SQL commands级的帮助DB# \? --psql commands级的帮助按列显示类似MySQL的\GDB# \xExpanded display is on.查看DB安装目录(最好root用户执行)find / -name initdb查看有多少DB实例在运行(最好root用户执行)find / -name postgresql.conf查看DB版本cat $PGDATA/PG_VERSIONpsql --versionDB# show server_version;DB# select version();查看DB实例运行状态pg_ctl status查看所有数据库psql –l --查看5432端口下面有多少个DBpsql –p XX –l --查看XX端口下面有多少个DBDB# \lDB# select * from pg_database;创建数据库createdb database_nameDB# \h create database --创建数据库的帮助命令DB# create database database_name进入某个数据库psql –d dbnameDB# \c dbname查看当前数据库DB# \cDB# select current_database();查看数据库文件目录DB# show data_directory;cat $PGDATA/postgresql.conf |grep data_directorycat /etc/init.d/postgresql|grep PGDATAlsof |grep 5432得出第二列的PID号再ps –ef|grep PID查看表空间select * from pg_tablespace;查看语言select * from pg_language;查询所有schema必须到指定的数据库下执行select * from information_schema.schemata;SELECT nspname FROM pg_namespace;\dnS查看表名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_nameff_v3_ff_basic_af;查看表结构DB# \d tablenameDB# select * from information_schema.columns where table_schemapublic and table_nameXX;查看索引DB# \diDB# select * from pg_index;查看视图DB# \dvDB# 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 pDB# 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 spcnamepg_default then base else pg_tblspc/||t2.oid||/PG_11_201804061/ end, t1.oid))).* from pg_database t1,pg_tablespace t2 where t1.dattablespacet2.oid;按占空间大小顺序查看所有表的大小select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemanamepublic order by pg_relation_size(relid) desc;按占空间大小顺序查看索引大小select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemanamepublic order by pg_relation_size(relid) desc;查看参数文件DB# show config_file;DB# show hba_file;DB# show ident_file;查看当前会话的参数值DB# show all;查看参数值select * from pg_file_settings查看某个参数值,比如参数work_memDB# show work_mem修改某个参数值,比如参数work_memDB# alter sy stem set work_mem8MB--使用alter system命令将修改postgresql.auto.conf文件而不是postgresql.conf这样可以很好的保护postgresql.conf文件加入你使用很多alter system命令后搞的一团糟那么你只需要删除postgresql.auto.conf再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。查看是否归档DB# show archive_mode;查看运行日志的相关配置运行日志包括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# \cDB# select current_user;查看所有用户DB# select * from pg_user;DB# select * from pg_shadow;查看所有角色DB# \duDB# select * from pg_roles;查询用户XX的权限必须到指定的数据库下执行select * from information_schema.table_privileges where granteeXX;创建用户XX并授予超级管理员权限create user XXX SUPERUSER PASSWORD 123456创建角色赋予了login权限则相当于创建了用户在pg_user可以看到这个角色create role user1 superuser;--pg_roles有user1pg_user和pg_shadow没有user1alter role user1 login;--pg_user和pg_shadow也有user1了授权DB# \h grantGRANT 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函数;对于外部封装器来说允许使用外部封装器来创建外部服务器;对于外部服务器来说允许创建外部表。查看表上存在哪些索引以及大小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 iWHERE 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 24610select * from pg_get_functiondef(24610);查看表大小(不含索引等信息)select pg_relation_size(cc); --368640 byteselect pg_size_pretty(pg_relation_size(cc)) --360 kB查看表所对应的数据文件路径与大小SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname empsalary;posegresql查询当前lsn1、用到哪些方法apple# select proname from pg_proc where proname like pg_%_lsn;proname---------------------------------pg_current_wal_flush_lsnpg_current_wal_insert_lsnpg_current_wal_lsnpg_last_wal_receive_lsnpg_last_wal_replay_lsn2、查询当前的lsn值apple# select pg_current_wal_lsn();pg_current_wal_lsn--------------------------0/450000983、查询当前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表示删除000000010000000000000005之前的所有日志--pg_wal日志没有设置保留周期的参数即没有类似mysql的参数expire_logs_dayspg_wal日志永久保留除非shell脚步删除几天前或pg-rman备份时候设置保留策略查询有哪些slot任意一个数据库下都可以查查询的结果都一样select * from pg_replication_slots;-原文http://blog.itpub.net/30126024/viewspace-2655205/
