postgresql命令

PG数据库常⽤命令

查看帮助命令

DB=#help--总的帮助

DB=#h--SQLcommands级的帮助

DB=#?--psqlcommands级的帮助

按列显⽰，类似MySQL的G

DB=#x

Expandeddisplayison.

查看DB安装⽬录(最好root⽤户执⾏)

find/-nameinitdb

查看有多少DB实例在运⾏(最好root⽤户执⾏)

find/-

查看DB版本

cat$PGDATA/PG_VERSION

psql--version

DB=#showserver_version;

DB=#selectversion();

查看DB实例运⾏状态

pg_ctlstatus

查看所有数据库

psql–l--查看5432端⼝下⾯有多少个DB

psql–pXX–l--查看XX端⼝下⾯有多少个DB

DB=#l

DB=#select*frompg_database;

创建数据库

createdbdatabase_name

DB=#hcreatedatabase--创建数据库的帮助命令

DB=#createdatabasedatabase_name

进⼊某个数据库

psql–ddbname

DB=#cdbname

查看当前数据库

DB=#c

DB=#selectcurrent_database();

查看数据库⽂件⽬录

DB=#showdata_directory;

cat$PGDATA/|grepdata_directory

cat/etc/init.d/postgresql|grepPGDATA=

lsof|grep5432得出第⼆列的PID号再ps–ef|grepPID

查看表空间

select*frompg_tablespace;

查看语⾔

select*frompg_language;

查询所有schema，必须到指定的数据库下执⾏

select*frominformation_ta;

SELECTnspnameFROMpg_namespace;

dnS

查看表名

DB=#dt--只能查看到当前数据库下public的表名

DB=#SELECTtablenameFROMpg_tablesWHEREtablenameNOTLIKE'pg%'ANDtablenameNOTLIKE'sql_%'ORDERBY

tablename;

DB=#SELECT*FROMinformation_WHEREtable_name='ff_v3_ff_basic_af';

查看表结构

查看表结构

DB=#dtablename

DB=#select*frominformation_swheretable_schema='public'andtable_name='XX';

查看索引

DB=#di

DB=#select*frompg_index;

查看视图

DB=#dv

DB=#select*frompg_viewswhereschemaname='public';

DB=#select*frominformation_heretable_schema='public';

查看触发器

DB=#select*frominformation_rs;

查看序列

DB=#select*frominformation_ceswheresequence_schema='public';

查看约束

DB=#select*frompg_constraintwherecontype='p'

DB=#eastable_name,easconstraint_name,easconstraint_typefrompg_classa,pg_constraintbwhere

=e='cc';

查看XX数据库的⼤⼩

SELECTpg_size_pretty(pg_database_size('XX'))Asfulldbsize;

查看所有数据库的⼤⼩

selectpg_e,pg_size_pretty(pg_database_size(pg_e))ASsizefrompg_database;

查看各数据库数据创建时间：

selectdatname,(pg_stat_file(format('%s/%s/PG_VERSION',casewhenspcname='pg_default'then'base'else

'pg_tblspc/'||||'/PG_11_201804061/'end,))).*frompg_databaset1,pg_lespace=;

按占空间⼤⼩，顺序查看所有表的⼤⼩

selectrelname,pg_size_pretty(pg_relation_size(relid))frompg_stat_user_tableswhereschemaname='public'orderby

pg_relation_size(relid)desc;

按占空间⼤⼩，顺序查看索引⼤⼩

selectindexrelname,pg_size_pretty(pg_relation_size(relid))frompg_stat_user_indexeswhereschemaname='public'orderby

pg_relation_size(relid)desc;

查看参数⽂件

DB=#showconfig_file;

DB=#showhba_file;

DB=#showident_file;

查看当前会话的参数值

DB=#showall;

查看参数值

select*frompg_file_settings

查看某个参数值,⽐如参数work_mem

DB=#showwork_mem

修改某个参数值,⽐如参数work_mem

DB=#altersystemsetwork_mem='8MB'

--使⽤altersystem命令将修改⽂件，⽽不是，这样可以很好的保护⽂件，加⼊你使⽤很

多altersystem命令后搞的⼀团糟，那么你只需要删除，再执⾏pg_ctlreload加载⽂件即可实现参数的重

新加载。

查看是否归档

DB=#showarchive_mode;

查看运⾏⽇志的相关配置，运⾏⽇志包括Error信息，定位慢查询SQL，数据库的启动关闭信息，checkpoint过于频繁等的告警信息。

showlogging_collector;--启动⽇志收集

showlog_directory;--⽇志输出路径

showlog_filename;--⽇志⽂件名

showlog_truncate_on_rotation;--当⽣成新的⽂件时如果⽂件名已存在，是否覆盖同名旧⽂件名

showlog_statement;--设置⽇志记录内容

showlog_min_duration_statement;--运⾏XX毫秒的语句会被记录到⽇志中，-1表⽰禁⽤这个功能，0表⽰记录所有语句，类似mysql的慢查

询配置

查看wal⽇志的配置，wal⽇志就是redo重做⽇志

存放在data_directory/pg_wal⽬录

查看当前⽤户

DB=#c

DB=#selectcurrent_user;

查看所有⽤户

DB=#select*frompg_user;

DB=#select*frompg_shadow;

查看所有⾓⾊

DB=#du

DB=#select*frompg_roles;

查询⽤户XX的权限，必须到指定的数据库下执⾏

select*frominformation__privilegeswheregrantee='XX';

创建⽤户XX，并授予超级管理员权限

createuserXXXSUPERUSERPASSWORD'123456'

创建⾓⾊，赋予了login权限，则相当于创建了⽤户，在pg_user可以看到这个⾓⾊

createrole"user1"superuser;--pg_roles有user1，pg_user和pg_shadow没有user1

alterrole"user1"login;--pg_user和pg_shadow也有user1了

授权

DB=#hgrant

GRANTALLPRIVILEGESONschemaschemanameTOdbuser;

grantALLPRIVILEGESonalltablesinschemafdstodbuser;

GRANTALLONtablenameTOuser;

GRANTALLPRIVILEGESONDATABASEdbnameTOdbuser;

grantselectonalltablesinschemapublictodbuser;--给⽤户读取public这个schema下的所有表

GRANTcreateONschemaschemanameTOdbuser;--给⽤户授予在schema上的create权限，⽐如createtable、createview等

GRANTUSAGEONschemaschemanameTOdbuser;

grantselectonschemapublictodbuser;--报错ERROR:invalidprivilegetypeSELECTforschema

--USAGE：对于程序语⾔来说，允许使⽤指定的程序语⾔创建函数;对于Schema来说，允许查找该Schema下的对象;对于序列来说，允许使

⽤currval和nextval函数;对于外部封装器来说，允许使⽤外部封装器来创建外部服务器;对于外部服务器来说，允许创建外部表。

查看表上存在哪些索引以及⼤⼩

selectrelname,asindex_typefrompg_classm,pg_=(elidfrompg_class

a,pg_=e='cc');

e,e,es*8assize_kbFROMpg_classc,pg_classc2,pg_e='cc'

==e;

查看索引定义

elidfrompg_classa,pg_=e='cc';

selectpg_get_indexdef(elid);

查看过程函数定义

selectoid,*frompg_procwhereproname='insert_platform_action_exist';--oid=24610

select*frompg_get_functiondef(24610);

查看表⼤⼩(不含索引等信息)

selectpg_relation_size('cc');--368640byte

selectpg_size_pretty(pg_relation_size('cc'))--360kB

查看表所对应的数据⽂件路径与⼤⼩

SELECTpg_relation_filepath(oid),relpagesFROMpg_classWHERErelname='empsalary';

posegresql查询当前lsn

1、⽤到哪些⽅法：

apple=#selectpronamefrompg_procwherepronamelike'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=#selectpg_current_wal_lsn();

pg_current_wal_lsn

--------------------------

0/45000098

3、查询当前lsn对应的⽇志⽂件

selectpg_walfile_name('0/1732DE8');

4、查询当前lsn在⽇志⽂件中的偏移量

SELECT*FROMpg_walfile_name_offset(pg_current_wal_lsn());

切换pg_wal⽇志

selectpg_switch_wal();

清理pg_wal⽇志

pg_archivecleanup/postgresql/pgsql/data/pg_wal

表⽰删除之前的所有⽇志

--pg_wal⽇志没有设置保留周期的参数，即没有类似mysql的参数expire_logs_days，pg_wal⽇志永久保留，除⾮shell脚步删除⼏天前或pg-

rman备份时候设置保留策略

查询有哪些slot，任意⼀个数据库下都可以查，查询的结果都⼀样

select*frompg_replication_slots;