postgre基本操作

1、连接数据库：

./pgsql -h 数据库IP地址 -p  端口 -U 用户名 -d 连接的库 

2、切换库

\CONNECT DBNAME

\c DBNAME

3、查看当前库下的表名：

\d

4、查询结果格式化：

默认情况下查询结果展示如下： 

在查询语句添加了：\g\x\g;之后，竖向展示查询结果，对比如下：
      

5、查询所有被锁的表，然后记下他的pid

select *
from pg_locks a
join pg_class b on a.relation = b.oid
where upper(b.relname) = 'TABLE_NAME';

再根据上面查出来的pid去表pg_stat_activity查询一下该锁对应的SQL语句：

select * from pg_stat_activity where procpid=17522;

杀掉进程，使用pg_terminate_backend()需要superuser权限，可以关闭所有的后台进程，向后台发送SIGTERM信号，用于关闭事务，此时session也会被关闭，并且事务回滚

select pg_terminate_backend(‘pid’)

 也可以使用pg_cancel_backend()，不过只能关闭当前用户下的后台进程，向后台发送SIGINT信号，用于关闭事务，此时session还在，并且事务回滚

6、查询当前所有连接的状态

select datname,pid,application_name,state from pg_stat_activity;

显示系统允许的最大连接数

show max_connections;

修改最大连接数

alter system set max_connections= 5000

pg库连接数释放

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state='idle';

7、查询pg库占用的大小

select pg_database.datname, pg_database_size(pg_database.datname)/1024/1024 AS size_M from pg_database order by 2 desc

8、查询pid（进程号），query_stay（命令执行了多少时间，单位s），query（正在执行什么命令 ）

select pid, query_stay, replace(query, chr(10), ' ') as query ,datname, usename, client_addr, application_name, state, backend_start, xact_start, xact_stay, query_start from (select pgsa.pid,pgsa.datname as datname, pgsa.usename as usename, pgsa.client_addr client_addr, pgsa.application_name as application_name, pgsa.state as state, pgsa.backend_start as backend_start, pgsa.xact_start as xact_start, extract(epoch from (now() - pgsa.xact_start)) as xact_stay, pgsa.query_start as query_start, extract(epoch from (now() - pgsa.query_start)) as query_stay , pgsa.query as query from pg_stat_activity as pgsa where pgsa.state != 'idle' and pgsa.state != 'idle in transaction' and pgsa.state != 'idle in transaction (aborted)' ) idleconnections order by query_stay desc