pgpool

来源：互联网发布：数据挖掘算法有哪些编辑：程序博客网时间：2024/05/29 05:15

查询锁：

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,    relnameFROM    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_actWHERE    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) bbwhere 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 --, prosrcfrom 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_statementswhere query like %tttt%;

清除语句log：

SELECT pg_stat_statements_reset();

显示同步备机名：

show synchronous_standby_namespgagent:/opt/pgagent/bin/pgagent hostaddr=localhost dbname=postgres user=postgrespgpool:/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 sapcp_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  spcp_stop_pgpool  10  localhost  9898  postgres  sa  fpcp_stop_pgpool  10  localhost  9898  postgres  sa  i

0 0