That did it! My apologies for not seeing the sql_delimiter directive while reading through the documentation. I will try to rtfm better in the future.
Pat On 12/03/2014 11:21 PM, Paolo Lucente wrote: > Hi Pat, > > Give a try changing the COPY delimiter using the sql_delimiter directive. > You can read more about the directive in CONFIG-KEYS. Keep me posted. > > Cheers, > Paolo > > On Wed, Dec 03, 2014 at 01:30:20AM -0800, THE MIGHTY VEXORG wrote: >> Using the pgsql plugin with both sql_use_copy and as_path as part of the >> aggregate appears to cause errors during the copy. It seems that there >> is an occasional extra comma in the as_path which then causes the copy >> to fail. This only appears to happen with the pgsql plugin when both >> sql_use_copy is true and as_path is part of the aggregate. >> >> relevant parts of my nfacctd.conf: >> aggregate[bgp_hourly]: src_as, dst_as, peer_src_ip, peer_dst_ip, >> as_path, std_comm, local_pref, med, in_iface >> aggregate[flat]: src_as, dst_as, peer_src_ip, peer_dst_ip, as_path, >> std_comm, local_pref, med, in_iface >> plugins: print[flat], pgsql[bgp_hourly] >> sql_use_copy[bgp_hourly]: true >> print_output[flat]: csv >> >> "flat" was used to print to csv file to help troubleshoot this problem. >> When the error occurs, the postgresql log has the following: >> >> 2014-12-02 19:50:51 EST ERROR: 22P04: extra data after last expected column >> 2014-12-02 19:50:51 EST CONTEXT: COPY acct_bgp_hourly_20141202_1900, >> line 28889: "2014-12-02 19:50:51,2014-12-02 19:00:00,0,5,64520,577:55 >> 577:5606 577:11100 577:22111,174 39743 {322..." >> 2014-12-02 19:50:51 EST LOCATION: NextCopyFrom, copy.c:2376 >> 2014-12-02 19:50:51 EST STATEMENT: COPY acct_bgp_hourly_20141202_1900 >> (stamp_updated, stamp_inserted, as_src, iface_in, as_dst, comms, >> as_path, local_pref, med, peer_ip_src, peer_ip_dst, packets, bytes) FROM >> STDIN DELIMITER ',' >> >> and the corresponding line from the csv file: >> >> SRC_AS,DST_AS,COMMS,AS_PATH,PREF,MED,PEER_SRC_IP,PEER_DST_IP,IN_IFACE,PACKETS,BYTES >> 0,64520,577:55_577:5606_577:11100_577:22111,174_39743_{3223,64520},110,0,64.230.195.154,64.230.195.154,5,4,160 >> >> It looks like the extra comma in the as_path field matches the >> delimiter, making postgresql think there is an extra field. Is there a >> way around this other than setting sql_use_copy to false or not >> collecting as_path data when using the pgsql plugin? Is there any other >> information I should collect to better troubleshoot this problem? In my >> setup, setting sql_use_copy to false makes it hard for nfacct/pgsql to >> be able to keep up with the incoming flows, so I would prefer to avoid >> that option if possible. >> >> Pat >> >> _______________________________________________ >> pmacct-discussion mailing list >> http://www.pmacct.net/#mailinglists > _______________________________________________ > pmacct-discussion mailing list > http://www.pmacct.net/#mailinglists > _______________________________________________ pmacct-discussion mailing list http://www.pmacct.net/#mailinglists