PG数据库常⽤命令
查看帮助命令
DB=# help --总的帮助
DB=# \h --SQL commands级的帮助
DB=# \? --psql commands级的帮助
按列显⽰，类似MySQL的\G
长颈龙
DB=# \x
Expanded display is on.
查看DB安装⽬录(最好root⽤户执⾏)
find / -name initdb
查看有多少DB实例在运⾏(最好root⽤户执⾏)
find / -f
查看DB版本
cat $PGDATA/PG_VERSION
psql --version
DB=# show rver_version;
DB=# lect version();
查看DB实例运⾏状态
pg_ctl status
查看所有数据库
psql –l --查看5432端⼝下⾯有多少个DB
psql –p XX –l --查看XX端⼝下⾯有多少个DB
DB=# \l
DB=# lect * from pg_databa;
创建数据库
createdb databa_name
DB=# \h create databa --创建数据库的帮助命令子育
DB=# create databa databa_name
进⼊某个数据库
psql –d dbname
DB=# \c dbname
查看当前数据库
DB=# \c
DB=# lect current_databa();
透明胶带的胶怎么去除查看数据库⽂件⽬录
DB=# show data_directory;
cat $f |grep data_directory
cat /etc/init.d/postgresql|grep PGDATA=
lsof |grep 5432得出第⼆列的PID号再ps –ef|grep PID
查看表空间
lect * from pg_tablespace;
查看语⾔
lect * from pg_language;
查询所有schema，必须到指定的数据库下执⾏
lect * 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_name='ff_v3_ff_basic_af';
查看表结构
查看表结构
DB=# \d tablename
第一滴泪吉他谱
DB=# lect * from lumns where table_schema='public' and table_name='XX';
查看索引
DB=# \di
DB=# lect * from pg_index;
查看视图
DB=# \dv
DB=# lect * from pg_views where schemaname = 'public';
DB=# lect * from information_schema.views where table_schema = 'public';
查看触发器
DB=# lect * from iggers;
查看序列
关于雨的诗DB=# lect * from information_schema.quences where quence_schema = 'public';
查看约束
DB=# lect * from pg_constraint where contype = 'p'
DB=# lname as table_ame as constraint_pe as constraint_type from pg_class a,pg_constraint b where a.oid = b.conrelid lname = 'cc';
查看XX数据库的⼤⼩
SELECT pg_size_pretty(pg_databa_size('XX')) As fulldbsize;
查看所有数据库的⼤⼩
lect pg_databa.datname, pg_size_pretty (pg_databa_size(pg_databa.datname)) AS size from pg_databa;
查看各数据库数据创建时间：
lect datname,(pg_stat_file(format('%s/%s/PG_VERSION',ca when spcname='pg_default' then 'b
a' el
'pg_tblspc/'||t2.oid||'/PG_11_201804061/' end, t1.oid))).* from pg_databa t1,pg_tablespace t2 where t1.dattablespace=t2.oid;
按占空间⼤⼩，顺序查看所有表的⼤⼩
lect relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_ur_tables where schemaname='public' order by
pg_relation_size(relid) desc;
按占空间⼤⼩，顺序查看索引⼤⼩
lect indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_ur_indexes where schemaname='public' order by
pg_relation_size(relid) desc;
查看参数⽂件
DB=# show config_file;
DB=# show hba_file;
18岁寄语
DB=# show ident_file;
查看当前会话的参数值
DB=# show all;
查看参数值
lect * from pg_file_ttings
查看某个参数值,⽐如参数work_mem
DB=# show work_mem
修改某个参数值,⽐如参数work_mem
DB=# alter system t work_mem='8MB'
-
-使⽤alter system命令将修改f⽂件，⽽不是f，这样可以很好的保护f⽂件，加⼊你使⽤很多alter system命令后搞的⼀团糟，那么你只需要删除f，再执⾏pg_ctl reload加载f⽂件即可实现参数的重新加载。
查看是否归档
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=# \c
DB=# lect current_ur;
查看所有⽤户
DB=# lect * from pg_ur;
DB=# lect * from pg_shadow;
查看所有⾓⾊
DB=# \du
DB=# lect * from pg_roles;
查询⽤户XX的权限，必须到指定的数据库下执⾏
lect * from information_schema.table_privileges where grantee='XX';
创建⽤户XX，并授予超级管理员权限
create ur XXX SUPERUSER PASSWORD '123456'
创建⾓⾊，赋予了login权限，则相当于创建了⽤户，在pg_ur可以看到这个⾓⾊
create role "ur1" superur;--pg_roles有ur1，pg_ur和pg_shadow没有ur1
alter role "ur1" login;--pg_ur和pg_shadow也有ur1了
授权
DB=# \h grant
GRANT ALL PRIVILEGES ON schema schemaname TO dbur;
grant ALL PRIVILEGES on all tables in schema fds to dbur;
GRANT ALL ON tablename TO ur;
GRANT ALL PRIVILEGES ON DATABASE dbname TO dbur;
grant lect on all tables in schema public to dbur;--给⽤户读取public这个schema下的所有表
GRANT create ON schema schemaname TO dbur;--给⽤户授予在schema上的create权限，⽐如create table、create view等
GRANT USAGE ON schema schemaname TO dbur;
grant lect on schema public to dbur;--报错ERROR: invalid privilege type SELECT for schema
--USAGE：对于程序语⾔来说，允许使⽤指定的程序语⾔创建函数；对于Schema来说，允许查找该Schema下的对象；对于序列来说，允许使⽤currval和nextval函数；对于外部封装器来说，允许使⽤外部封装器来创建外部服务器；对于外部服务器来说，允许创建外部表。
查看表上存在哪些索引以及⼤⼩
lect relname,n.amname as index_type from pg_class m,pg_am n lam = n.oid and m.oid in(lect b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid lname = 'cc');
lname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i lname ='cc' AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER lname;
查看索引定义
lect b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid lname = 'cc';
lect pg_get_indexdef(b.indexrelid);
查看过程函数定义
lect oid,* from pg_proc where proname = 'inrt_platform_action_exist'; --oid = 24610
lect * from pg_get_functiondef(24610);
查看表⼤⼩(不含索引等信息)
lect pg_relation_size('cc'); --368640 byte
lect pg_size_pretty(pg_relation_size('cc')) --360 kB
查看表所对应的数据⽂件路径与⼤⼩
SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';
pogresql查询当前lsn
1、⽤到哪些⽅法：
apple=# lect proname from pg_proc where proname like 'pg_%_lsn';
proname
---------------------------------
pg_current_wal_flush_lsn
pg_current_wal_inrt_lsn
pg_current_wal_lsn
pg_last_wal_receive_lsn
pg_last_wal_replay_lsn
2、查询当前的lsn值：
apple=# lect pg_current_wal_lsn();
pg_current_wal_lsn
--------------------------
0/45000098
3、查询当前lsn对应的⽇志⽂件
lect pg_walfile_name('0/1732DE8');
4、查询当前lsn在⽇志⽂件中的偏移量
SELECT * FROM pg_walfile_name_offt(pg_current_wal_lsn());
切换pg_wal⽇志
lect pg_switch_wal();
清理pg_wal⽇志
pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005
郑州铁路局电话
表⽰删除000000010000000000000005之前的所有⽇志
--pg_wal⽇志没有设置保留周期的参数，即没有类似mysql的参数expire_logs_days，pg_wal⽇志永久保留，除⾮shell脚步删除⼏天前或pg-rman备份时候设置保留策略
查询有哪些slot，任意⼀个数据库下都可以查，查询的结果都⼀样
lect * from pg_replication_slots;
