Postgres数据库常用命令

启动：service postgresql start
 重启: service postgresql restart
 停止：service postgresql stop创建数据库新用户，如 dbuser：
 create user dbuser with password ‘*****’;
 将数据库的所有权限都赋予dbuser：
 GRANT ALL PRIVILEGES ON DATABASE exampledb TO dbuser;
 修改用户密码
 ALTER USER 用户名 WITH PASSWORD ‘密码’;
 赋予用户超级权限：
 alter user 用户名 with SUPERUSER;
 撤销用户超级权限：
 alter user 用户名 with NOSUPERUSER;
 给用户设置允许连接数为1，仅对普通用户有效
 ALTER ROLE 用户名 CONNECTION LIMIT 1;创建表，设置部位空，默认值，主键和外键约束
 CREATE TABLE public.tbl_deptrule(uidrecordid varchar(48) not null,iisbasedonaddroruser int default 1, primary key(uidrecordid), constraint fk_tbl_dept_fk_tbl_de_tbl_orga FOREIGN KEY (uiddeptid) REFERENCES tbl_organization(uidroleid) ON UPDATE RESTRICT ON DELETE CASCADE);按照用户分组查看各用户连接数
 select usename, count() from pg_stat_activity group by usename order by count() desc;创建Linux普通用户，与刚才新建的数据库用户同名，如 dbuser：
 $ sudo adduser dbuser
 $ sudo passwd dbuser连接指定数据库： psql -d 数据库名 -U 用户名 -W 密码 -h ip
 pg远程链接权限配置文件路径： /var/lib/postgresql/9.1/main/pg_hba.conf
 PostgreSQL 重新读取pg_hba.conf文件生效： service postgresql reload 或 service postgresql restart给指定表上添加列：
 ALTER TABLE table_name ADD column_name datatype;
 修改指定表指定列的的类型
 ALTER TABLE table_name COLUMN column_name TYPE datatype ;

INFORMATION_SCHEMA.COLUMNS相关网址：
https://www.mssqltips.com/sqlservertutorial/183/informationschemacolumns/

获取表的所有列

select column_name from information_schema.columns where table_name= ‘表名’;
 获取指定表指定列的数据类型以及character类型长度
 select data_type ,character_maximum_length from information_schema.columns where table_name = ‘test’ and column_name = ‘num1’;COPY命令:
 copy table_name to /dir/file 从表中拷贝数据到文件
 copy table_name from /dir/file 把文件中的数据拷贝到表中分割函数
 split_part(string text, delimiter text, field int)
 例子：select split_part(’“public”.“tbl_devgroupdef”’, ‘.’, 2); 结果为：tbl_devgroupdef转义字符：单引号’
\h SQL命令帮助
 \d table 查看表结构
 \q 退出
 \l 列出所有数据库
 \dt 列出连接数据库中所有表
 \ef 触发器函数名 查看指定触发器函数
 \c 数据库名 切换数据库
 \a 如果目前的表输出格式是不对齐的，切换成对齐的。如果是对齐的，则切换成不对齐。
 \du 查看用户权限序列：
 序列查询：select * from seq_name; select MAX(id) from table_name;
 修改序列值：SELECT setval(’“public”.“seq_name”’, id_max, true);触发器：
 创建触发器函数：
 create or replace function trigger_function_name () returns trigger as
 $$
begin
	raise exception 'err';
end
$$ language plpgsql;
 创建触发器：create trigger trigger_name BEFORE insert on table_name for each row execute procedure trigger_function_name();
 删除触发器：DROP TRIGGER trigger_name on table_name ;
 禁用触发器：ALTER TABLE table_name DISABLE TRIGGER trigger_name;
 启用触发器：ALTER TABLE table_name ENABLE TRIGGER trigger_name;
 查看触发器函数：\ef trigger_name

常用公共表和视图：

  1. pg_locks ：展示锁信息，每一个被锁或者等待锁的对象一条记录
  2. pg_stat_activity：每个会话一条记录，显示会话状态信息
  3. pg_trigger: 存储表和视图上面的触发器
  4. pg_roles: 记录所有用户信息

锁问题排查：

查询锁： select PID, STATE, QUERY, WAIT_EVENT_TYPE, WAIT_EVENT,QUERY_START from PG_STAT_ACTIVITY where WAIT_EVENT_TYPE = ‘Lock’;
 查询可能被锁的表的oid：select oid from pg_class where relname = ‘表名’;
 根据oid查找表上的锁：select * from pg_locks where relation = ‘oid’; granted为t表示阻塞别人，f为被阻塞
 杀死进程：
 select pg_terminate_backend(pid); //可以kill各种操作，同时释放连接资源
 select pg_cancel_backend(pid); //只能kill select查询，不能释放数据库连接查询指定表主键的列名：
 select t.colname from information_schema.columns LEFT JOIN (select pg_class.relname as tablename,pg_attribute.attname as colname from pg_constraint inner join pg_class on pg_constraint.conrelid = pg_class.oid inner join pg_attribute on pg_attribute.attrelid = pg_class.oid and pg_attribute.attnum = pg_constraint.conkey[1] where pg_constraint.contype=‘p’) t on table_name=t.tablename where TABLE_NAME = ‘tablename’ group by t.colname;