pgpool命令

最新推荐文章于 2024-08-20 15:41:29 发布

张永光

最新推荐文章于 2024-08-20 15:41:29 发布

阅读量2.4w

点赞数 1

分类专栏： postgres 文章标签： postgres

本文链接：https://blog.csdn.net/hello_zyg/article/details/79193990

版权

postgres 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

查询锁：

SELECT

    locked.pid AS locked_pid,

    locker.pid AS locker_pid,

    locked_act.usename AS locked_user,

    locker_act.usename AS locker_user,

    locked.virtualtransaction,

    locked.transactionid,

    relname

FROM

    pg_locks locked

    LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid),

    pg_locks locker,

    pg_stat_activity locked_act,

    pg_stat_activity locker_act

WHERE

    locker.granted=true AND

    locked.granted=false AND

    locked.pid=locked_act.pid AND

    locker.pid=locker_act.pid AND

    locked.relation=locker.relation;

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

select * from pg_stat_activity aa,

(

    select a.locktype,a.database,a.pid,a.mode,a.relation,b.relname from pg_locks a join pg_class b on a.relation=b.oid

 where upper(b.relname)=FX_WDSJ_DSZH

) bb

where aa.pid=bb.pid

 and aa.waiting=t

–查询是否锁表了

select pid from pg_locks where relation=(select oid from pg_class where relname=表);

 tb_cyplate_localitycheck

查询proc：

select proname --, prosrc

from pg_proc where LOWER(prosrc) like %join% and CHAR_LENGTH(prosrc) ##  100;

查询连接：

SELECT * FROM pg_stat_activity

查询正在执行的语句：

select * from pg_stat_activity where waiting=t;



kill pid：

select pg_cancel_backend(16023);

select  pg_terminate_backend(16023);

查询备机延迟：

select now()-pg_last_xact_replay_timestamp()

查询备机：

select * from pg_stat_replication;

/opt/postgres/9.3/bin/psql -U postgres -p 5432 -c "select * from pg_stat_replication;"

查询语句log：

select * from pg_stat_statements

where query like %tttt%;

清除语句log：

SELECT pg_stat_statements_reset();

显示同步备机名：

show synchronous_standby_names



pgagent:

/opt/pgagent/bin/pgagent hostaddr=localhost dbname=postgres user=postgres



pgpool:

/opt/postgres/9.3/bin/psql -U postgres -p 9999 -c "show pool_nodes;"

查看主pgpool

pcp_watchdog_info 10 10.2.4.90 9898 postgres sa

pcp_watchdog_info 10 10.2.4.90 9898 postgres sa 0

添加pgpool节点

pcp_attach_node  10  localhost  9898  postgres  sa  0

删除pgpool节点

pcp_detach_node  10  localhost  9898  postgres  sa  0

停止pgpool

pcp_stop_pgpool  10  localhost  9898  postgres  sa  s

pcp_stop_pgpool  10  localhost  9898  postgres  sa  f

pcp_stop_pgpool  10  localhost  9898  postgres  sa  i

来源张永光的博客