Postgres数据库常用命令

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;