转: Postgres数据库DBA常用命令

1DB=# help --总的帮助
2
3DB=# \h --SQL commands级的帮助
4
5DB=# \? --psql commands级的帮助

1DB=# \x
2
3Expanded display is on.

1find / -name initdb

1find / -name postgresql.conf

1cat $PGDATA/PG_VERSION
2
3psql --version
4
5DB=# show server_version;
6
7DB=# select version();

1pg_ctl status

1psql –l --查看5432端口下面有多少个DB
2
3psql –p XX –l --查看XX端口下面有多少个DB
4
5DB=# \l
6
7DB=# select * from pg_database;

1createdb database_name
2
3DB=# \h create database --创建数据库的帮助命令
4
5DB=# create database database_name

1psql –d dbname
2
3DB=# \c dbname

1DB=# \c
2
3DB=# select current_database();

1DB=# show data_directory;
2
3cat $PGDATA/postgresql.conf |grep data_directory
4
5cat /etc/init.d/postgresql|grep PGDATA=
6
7lsof |grep 5432得出第二列的PID号再ps –ef|grep PID

1select * from pg_tablespace;

1select * from pg_language;

1select * from information_schema.schemata;
2
3SELECT nspname FROM pg_namespace;
4
5\dnS

1DB=# \dt --只能查看到当前数据库下public的表名
2
3DB=# SELECT tablename FROM pg_tables WHERE tablename NOT LIKE 'pg%' AND tablename NOT LIKE 'sql_%' ORDER BY tablename;
4
5DB=# SELECT * FROM information_schema.tables WHERE table_name='ff_v3_ff_basic_af';

1DB=# \d tablename
2
3DB=# select * from information_schema.columns where table_schema='public' and table_name='XX';

1DB=# \di
2
3DB=# select * from pg_index;

1DB=# \dv
2
3DB=# select * from pg_views where schemaname = 'public';
4
5DB=# select * from information_schema.views where table_schema = 'public';

1DB=# select * from information_schema.triggers;

1DB=# select * from information_schema.sequences where sequence_schema = 'public';

1DB=# select * from pg_constraint where contype = 'p'
2
3DB=# 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';

1SELECT pg_size_pretty(pg_database_size('XX')) As fulldbsize;

1select pg_database.datname, pg_size_pretty (pg_database_size(pg_database.datname)) AS size from pg_database;

1select 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;

1select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='public' order by pg_relation_size(relid) desc;

1select indexrelname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_indexes where schemaname='public' order by pg_relation_size(relid) desc;

1DB=# show config_file;
2
3DB=# show hba_file;
4
5DB=# show ident_file;

1DB=# show all;

1select * from pg_file_settings

1DB=# show work_mem

1DB=# alter system set work_mem='8MB'
2
3--使用alter system命令将修改postgresql.auto.conf文件，而不是postgresql.conf，这样可以很好的保护postgresql.conf文件，加入你使用很多alter system命令后搞的一团糟，那么你只需要删除postgresql.auto.conf，再执行pg_ctl reload加载postgresql.conf文件即可实现参数的重新加载。

1DB=# show archive_mode;

 1show logging_collector;--启动日志收集
 2
 3show log_directory;--日志输出路径
 4
 5show log_filename;--日志文件名
 6
 7show log_truncate_on_rotation;--当生成新的文件时如果文件名已存在，是否覆盖同名旧文件名
 8
 9show log_statement;--设置日志记录内容
10
11show log_min_duration_statement;--运行XX毫秒的语句会被记录到日志中，-1表示禁用这个功能，0表示记录所有语句，类似mysql的慢查询配置

1存放在data_directory/pg_wal目录

1DB=# \c
2
3DB=# select current_user;

1DB=# select * from pg_user;
2
3DB=# select * from pg_shadow;

1DB=# \du
2
3DB=# select * from pg_roles;

1select * from information_schema.table_privileges where grantee='XX';

1create user XXX SUPERUSER PASSWORD '123456'

1create role "user1" superuser;--pg_roles有user1，pg_user和pg_shadow没有user1
2
3alter role "user1" login;--pg_user和pg_shadow也有user1了

 1DB=# \h grant
 2
 3GRANT ALL PRIVILEGES ON schema schemaname TO dbuser;
 4
 5grant ALL PRIVILEGES on all tables in schema fds to dbuser;
 6
 7GRANT ALL ON tablename TO user;
 8
 9GRANT ALL PRIVILEGES ON DATABASE dbname TO dbuser;
10
11grant select on all tables in schema public to dbuser;--给用户读取public这个schema下的所有表
12
13GRANT create ON schema schemaname TO dbuser;--给用户授予在schema上的create权限，比如create table、create view等
14
15GRANT USAGE ON schema schemaname TO dbuser;
16
17grant select on schema public to dbuser;--报错ERROR: invalid privilege type SELECT for schema
18
19--USAGE：对于程序语言来说，允许使用指定的程序语言创建函数;对于Schema来说，允许查找该Schema下的对象;对于序列来说，允许使用currval和nextval函数;对于外部封装器来说，允许使用外部封装器来创建外部服务器;对于外部服务器来说，允许创建外部表。

1select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in
2
3(select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');
4
5SELECT c.relname,c2.relname, c2.relpages*8 as size_kb FROM pg_class c, pg_class c2, pg_index i
6
7WHERE c.relname ='cc' AND c.oid =i.indrelid AND c2.oid =i.indexrelid ORDER BY c2.relname;

1select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc';
2
3select pg_get_indexdef(b.indexrelid);

1select oid,* from pg_proc where proname = 'insert_platform_action_exist'; --oid = 24610
2
3select * from pg_get_functiondef(24610);

1select pg_relation_size('cc'); --368640 byte
2
3select pg_size_pretty(pg_relation_size('cc')) --360 kB

1SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'empsalary';

 1apple=# select proname from pg_proc where proname like 'pg_%_lsn';
 2
 3proname
 4
 5---------------------------------
 6
 7pg_current_wal_flush_lsn
 8
 9pg_current_wal_insert_lsn
10
11pg_current_wal_lsn
12
13pg_last_wal_receive_lsn
14
15pg_last_wal_replay_lsn

1apple=# select pg_current_wal_lsn();
2
3pg_current_wal_lsn
4
5--------------------------
6
70/45000098

1select pg_walfile_name('0/1732DE8');

1SELECT * FROM pg_walfile_name_offset(pg_current_wal_lsn());

1select pg_switch_wal();

1pg_archivecleanup /postgresql/pgsql/data/pg_wal 000000010000000000000005
2
3表示删除000000010000000000000005之前的所有日志
4
5--pg_wal日志没有设置保留周期的参数，即没有类似mysql的参数expire_logs_days，pg_wal日志永久保留，除非sql脚步删除几天前或pg-rman备份时候设置保留策略

1select * from pg_replication_slots;

1select statement_timestamp()-pg_postmaster_start_time() AS up_time;

1select * from pg_stat_replication;

1select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_flush_lsn(),write_lsn)) delay_wal_size,* from pg_stat_replication ;

1pg_ctl promote -D $PGDATA

1select count(*) from pg_stat_activity where query <>'IDLE';

1\i xx.sql

1select pg_cancel_backend(pid); — session 还在，transaction 回滚, pid 来自 pg_stat_activity
2select pg_terminate_backend(pid); — session 消失，transaction 回滚, pid 来自 pg_stat_activity