Hi,
I have a problem with duplicated key entries in postgresql 9.2. I tried to
increase the sql_cache_entries, but there was no change to the result. If I ad
src_host and dst_host to the aggregation list it works, but the table size
explodes and this won't be a solution in my case. There is ipv4 and ipv6
traffic on the tracked interfaces is that a problem?
On every INSERT/COPY I get the following error in the db:
------------------
2013-03-24 15:51:01 CET ERROR: duplicate key value violates unique constraint
"test_24_15_pk"
2013-03-24 15:51:01 CET DETAIL: Key (agent_id, etype, mac_src, mac_dst,
tcp_flags, ip_proto, iface_in, iface_out, stamp_inserted)=(9, 800 ,
xx:xx:xx:xx:xx:xx, xx:xx:xx:xx:xx:xx, 31, 6, 963, 962, 2013-03-24 15:50:00)
already exists.
2013-03-24 15:51:01 CET CONTEXT: COPY test_24_15, line 2930: "2013-03-24
15:51:01,2013-03-24 15:50:00, xx:xx:xx:xx:xx:xx,
xx:xx:xx:xx:xx:xx,800,833,897,0,1,9,1024,1..."
2013-03-24 15:51:01 CET STATEMENT: COPY test_24_15 (stamp_updated,
stamp_inserted, mac_src, mac_dst, etype, iface_in, iface_out, tcp_flags,
ip_proto, agent_id, packets, bytes) FROM STDIN DELIMITER ','
------------------
Config:
daemonize: true
syslog: deamon
post_tag: 9
sfacctd_port: 6340
sfacctd_renormalize: true
plugins: pgsql[test]
plugin_pipe_size: 1024000
plugin_buffer_size: 10240
imt_mem_pools_number: 0
sql_host: localhost
sql_user: pmacct
sql_passwd: pmacct
sql_db: peeringbox
sql_optimize_clauses: true
sql_max_writers: 70
sql_multi_values: 10240000
sql_cache_entries: 1299827
!---test 1 min
aggregate[test]: tag, in_iface, out_iface, etype, src_mac, dst_mac, proto,
tcpflags
sql_table_schema[test]: /usr/local/etc/pmacct/test/test.pgsql
sql_table[test]: test_%d_%H
sql_refresh_time[test]: 60
sql_history[test]: 1m
sql_history_roundoff[test]: m
sql_dont_try_update[test]: true
sql_use_copy[test]: true
SQL-schema:
CREATE TABLE test_%d_%H (
agent_id BIGINT NOT NULL DEFAULT 0,
iface_in BIGINT NOT NULL DEFAULT 0,
iface_out BIGINT NOT NULL DEFAULT 0,
etype CHAR(5) NOT NULL DEFAULT '',
mac_src macaddr NOT NULL DEFAULT '0:0:0:0:0:0',
mac_dst macaddr NOT NULL DEFAULT '0:0:0:0:0:0',
ip_proto SMALLINT NOT NULL DEFAULT 0,
tcp_flags SMALLINT NOT NULL DEFAULT 0,
packets BIGINT NOT NULL,
bytes BIGINT NOT NULL,
flows BIGINT NOT NULL DEFAULT 0,
stamp_inserted timestamp without time zone NOT NULL DEFAULT '0001-01-01
00:00:00',
stamp_updated timestamp without time zone,
CONSTRAINT test_%d_%H_pk PRIMARY KEY (agent_id, etype, mac_src,
mac_dst, tcp_flags, ip_proto, iface_in, iface_out, stamp_inserted)
);
Compile configuration:
--enable-ipv6 --enable-pgsql --enable-threads --enable-64bit
I'm using sfacctd -f config.conf
Am I doing something wrong?
Any thoughts on how to resolve this would be greatly appreciated.
--
Thanks,
Reto Gsell
_______________________________________________
pmacct-discussion mailing list
http://www.pmacct.net/#mailinglists
