Hi all,
We are running an Hot-Standby Cluster with UTM 9.005 both virtuell on parallels server.
Tonight the HA confd sync deamon crashed and the postgres was in a deadlock. After that, the internet uplink was flipping up&down. Our internal network was really slow, too.
In the morning I have seen, that the slave node was dead. After rebooting the slave all problems were gone.
Here is a snip out of the system log from last night. Hope this is enough. Otherwise please do not hestitate to ask me for more detaills [[:)]]
--------------------------------------------------------------------
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-1] ERROR: deadlock detected
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-2] DETAIL: Process 14278 waits for AccessShareLock on relation 356201 of database 16702; blocked by process 13919.
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-3] Process 13919 waits for Acces***clusiveLock on relation 16725 of database 16702; blocked by process 14278.
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-4] Process 14278: select ins_accounting($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-5] Process 13919: drop schema "_asg_cluster" cascade;
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-6] HINT: See server log for query details.
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-7] CONTEXT: SQL statement "SELECT last_value::int4 FROM _asg_cluster.sl_log_status"
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-8] SQL statement "update accounting set raw_in_pktlen = raw_in_pktlen + $1 , raw_in_pktcount = raw_in_pktcount + $2 , raw_out_pktlen = raw_out_pktlen + $3 , raw_out_pktcount = raw_out_pktcount + $4 , flow_count = flow_count + 1 where logday = $5 and srcip = $6 and dstip = $7 and l4_dport = $8 and ip_protocol = $9 and (srcid = $10 or (srcid IS NULL and $10 IS NULL)) and (dstid = $11 or (dstid IS NULL and $11 IS NULL)) and afc_proto = $12 "
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-9] PL/pgSQL function "ins_accounting" line 20 at SQL statement
2013:03:12-21:02:33 SDS-1 postgres[14278]: [2-10] STATEMENT: select ins_accounting($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
2013:03:12-21:02:33 SDS-1 ulogd[9740]: pg1: ERROR: deadlock detected
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-1] ERROR: deadlock detected
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-2] DETAIL: Process 13919 waits for Acces***clusiveLock on relation 16725 of database 16702; blocked by process 14209.
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-3] Process 14209 waits for AccessShareLock on relation 356195 of database 16702; blocked by process 13919.
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-4] Process 13919: drop schema "_asg_cluster" cascade;
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-5] Process 14209: select ins_pfilter($1, $2, $3, $4, $5)
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-6] HINT: See server log for query details.
2013:03:12-21:02:34 SDS-1 postgres[13919]: [2-7] STATEMENT: drop schema "_asg_cluster" cascade;
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-1] ERROR: deadlock detected
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-2] DETAIL: Process 14261 waits for Acces***clusiveLock on relation 356195 of database 16702; blocked by process 14204.
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-3] Process 14204 waits for AccessShareLock on relation 356201 of database 16702; blocked by process 14261.
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-4] Process 14261: DROP SCHEMA IF EXISTS _asg_cluster CASCADE;
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-5] Process 14204: select ins_waf($1, $2, $3, $4, $5, $6, $7, $8)
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-6] HINT: See server log for query details.
2013:03:12-21:02:35 SDS-1 postgres[14261]: [2-7] STATEMENT: DROP SCHEMA IF EXISTS _asg_cluster CASCADE;
2013:03:12-21:02:36 SDS-1 postgres[14319]: [2-1] ERROR: relation "_asg_cluster.sl_trigger" does not exist at character 15
2013:03:12-21:02:36 SDS-1 postgres[14319]: [2-2] STATEMENT: select * from _asg_cluster.sl_trigger
2013:03:12-21:02:38 SDS-1 postgres[14425]: [2-1] LOG: unexpected EOF on client connection
...
2013:03:12-21:03:42 SDS-2 ulogd[32362]: pg1: ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:42 SDS-2 postgres[32363]: [2-1] ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:42 SDS-2 postgres[32363]: [2-2] CONTEXT: SQL statement "update accounting set raw_in_pktlen = raw_in_pktlen + $1 , raw_in_pktcount = raw_in_pktcount + $2 , raw_out_pktlen = raw_out_pktlen + $3 , raw_out_pktcount = raw_out_pktcount + $4 , flow_count = flow_count + 1 where logday = $5 and srcip = $6 and dstip = $7 and l4_dport = $8 and ip_protocol = $9 and (srcid = $10 or (srcid IS NULL and $10 IS NULL)) and (dstid = $11 or (dstid IS NULL and $11 IS NULL)) and afc_proto = $12 "
2013:03:12-21:03:42 SDS-2 postgres[32363]: [2-3] PL/pgSQL function "ins_accounting" line 20 at SQL statement
2013:03:12-21:03:42 SDS-2 postgres[32363]: [2-4] STATEMENT: select ins_accounting($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
2013:03:12-21:03:48 SDS-2 ulogd[32362]: pg1: ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:48 SDS-2 postgres[778]: [2-1] ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:48 SDS-2 postgres[778]: [2-2] CONTEXT: SQL statement "update accounting set raw_in_pktlen = raw_in_pktlen + $1 , raw_in_pktcount = raw_in_pktcount + $2 , raw_out_pktlen = raw_out_pktlen + $3 , raw_out_pktcount = raw_out_pktcount + $4 , flow_count = flow_count + 1 where logday = $5 and srcip = $6 and dstip = $7 and l4_dport = $8 and ip_protocol = $9 and (srcid = $10 or (srcid IS NULL and $10 IS NULL)) and (dstid = $11 or (dstid IS NULL and $11 IS NULL)) and afc_proto = $12 "
2013:03:12-21:03:48 SDS-2 postgres[778]: [2-3] PL/pgSQL function "ins_accounting" line 20 at SQL statement
2013:03:12-21:03:48 SDS-2 postgres[778]: [2-4] STATEMENT: select ins_accounting($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)
2013:03:12-21:03:54 SDS-2 ulogd[32362]: pg1: ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:54 SDS-2 postgres[779]: [2-1] ERROR: Slony-I: Table accounting is replicated and cannot be modified on a subscriber node - role=0
2013:03:12-21:03:54 SDS-2 postgres[779]: [2-2] CONTEXT: SQL statement "update accounting set raw_in_pktlen = raw_in_pktlen + $1 , raw_in_pktcount = raw_in_pktcount + $2 , raw_out_pktlen = raw_out_pktlen + $3 , raw_out_pktcount = raw_out_pktcount + $4 , flow_count = flow_count + 1 where logday = $5 and srcip = $6 and dstip = $7 and l4_dport = $8 and ip_protocol = $9 and (srcid = $10 or (srcid IS NULL and $10 IS NULL)) and (dstid = $11 or (dstid IS NULL and $11 IS NULL)) and afc_proto = $12 "
-----------------------------------------------------------
Cheers and Thanks [[:)]]
This thread was automatically locked due to age.