Hi Paolo, I've manually patched against 1.7.0 and can confirm it is now working.
I had to remove the config.sql_port from PG_compose_conn_string() as it wasn't recognised in 1.7.0. I can't get 1.7.1 or master branch to compile on an up to date CentOS 7.5.1804 at the moment - configure stage fails with 'Missing PQ library'... but 1.7.0 works fine. FYI - postgresql-devel-9.2.23-3.el7_4 is installed. I'm happy on 1.7.0 for now... Thanks for your help.. :) -Simon -----Original Message----- From: pmacct-discussion [mailto:pmacct-discussion-boun...@pmacct.net] On Behalf Of Paolo Lucente Sent: Thursday, 24 May 2018 7:51 a.m. To: pmacct-discussion@pmacct.net Subject: Re: [pmacct-discussion] pgsql recovery host Hi Simon, Thanks for reporting this. I've just committed a fix that should solve the issue, please see: https://github.com/pmacct/pmacct/commit/3cae80307780ffa84f2d37348b7ebb7dd34147b5 You could either try to apply the specific patch to 1.7.0 or clone master from GitHub. It appeared to work for me but it's true i have a very limited environment to reproduce this specific issue (ie. catching error codes from PostgreSQL library to make sure the primary host is failed); please give this a try and let me know if it works for you; in case it does not, it would be beneficial if i could access your system for the sake of debugging this issue further. Paolo On Mon, May 21, 2018 at 03:09:26AM +0000, Simon Travers-Jones (Xtreme) wrote: > Hi all, > Does anyone out there have a working postgres backup/failover server working? > > Previously I was using an early version of nfacctd with the recovery_logfile > option in case our postgres server became unavailable. > However since that option has been removed, I'm trying to get the SQL host > failover to work instead (with a backup postgresql server running on the > local host). > The docs imply that it is as simple as declaring 'sql_recovery_backup_host', > but no matter what I try - it either fails to try the backup host, or uses > the primary address instead. > I've had a quick look through the code, and discovered there also seems to be > a 'sql_backup_host' option - but same result. > > > When using: > .... > sql_host[national]: aa.bb.cc.122 > sql_host[international]: aa.bb.cc.122 > sql_backup_host[national]: 127.0.0.1 > sql_backup_host[international]: 127.0.0.1 > .... > > > Result: > INFO ( default/core ): NetFlow Accounting Daemon, nfacctd 1.7.0 > (20170924-00+c1) > INFO ( default/core ): '--enable-pgsql' '--enable-l2' '--enable-ipv6' > '--enable-64bit' '--enable-threads' '--enable-traffic-bins' > '--enable-bgp-bins' '--enable-bmp-bins' '--enable-st-bins' > INFO ( default/core ): Reading configuration file > '/usr/local/collector/conf.d/jbr1-to-db1.conf'. > INFO ( national/pgsql ): plugin_pipe_size=4096000 bytes > plugin_buffer_size=288 bytes > INFO ( national/pgsql ): ctrl channel: obtained=212992 bytes target=113776 > bytes > INFO ( international/pgsql ): plugin_pipe_size=4096000 bytes > plugin_buffer_size=288 bytes > INFO ( international/pgsql ): ctrl channel: obtained=212992 bytes > target=113776 bytes > INFO ( national/pgsql ): cache entries=10622267 base cache memory=3744797984 > bytes > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( international/pgsql ): cache entries=10622267 base cache > memory=3744797984 bytes > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): waiting for NetFlow/IPFIX data on :::2100 > ..... > ..... > ..... > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50102] version [10] seqno [3580780] > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50102] version [10] seqno [3580782] > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50102] version [10] seqno [3580784] > ( international/pgsql ) *** Purging queries queue *** > ( national/pgsql ) *** Purging queries queue *** > ERROR ( international/pgsql ): PRIMARY 'pgsql' backend trouble. > ERROR ( national/pgsql ): PRIMARY 'pgsql' backend trouble. > ERROR ( international/pgsql ): The SQL server says: Failed connecting to > dbname=international_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > ERROR ( national/pgsql ): The SQL server says: Failed connecting to > dbname=national_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > INFO ( international/pgsql ): *** Purging cache - START (PID: 31091) *** > INFO ( national/pgsql ): *** Purging cache - START (PID: 31090) *** > ERROR ( international/pgsql ): BACKUP 'pgsql' backend trouble. > ERROR ( national/pgsql ): BACKUP 'pgsql' backend trouble. > ERROR ( international/pgsql ): The SQL server says: Failed connecting to > dbname=international_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > ERROR ( national/pgsql ): The SQL server says: Failed connecting to > dbname=national_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > INFO ( national/pgsql ): *** Purging cache - END (PID: 31090, QN: 0/1788, ET: > 127) *** > INFO ( international/pgsql ): *** Purging cache - END (PID: 31091, QN: > 0/5232, ET: 127) *** > INFO ( default/core ): OK, Exiting ... > > > > > > > Using: > .... > sql_host[national]: aa.bb.cc.122 > sql_host[international]: aa.bb.cc.122 > sql_recovery_backup_host[national]: 127.0.0.1 > sql_recovery_backup_host[international]: 127.0.0.1 > .... > > Result: > INFO ( default/core ): NetFlow Accounting Daemon, nfacctd 1.7.0 > (20170924-00+c1) > INFO ( default/core ): '--enable-pgsql' '--enable-l2' '--enable-ipv6' > '--enable-64bit' '--enable-threads' '--enable-traffic-bins' > '--enable-bgp-bins' '--enable-bmp-bins' '--enable-st-bins' > INFO ( default/core ): Reading configuration file > '/usr/local/collector/conf.d/jbr1-to-db1.conf'. > INFO ( national/pgsql ): plugin_pipe_size=4096000 bytes > plugin_buffer_size=288 bytes > INFO ( national/pgsql ): ctrl channel: obtained=212992 bytes target=113776 > bytes > INFO ( international/pgsql ): plugin_pipe_size=4096000 bytes > plugin_buffer_size=288 bytes > INFO ( international/pgsql ): ctrl channel: obtained=212992 bytes > target=113776 bytes > INFO ( national/pgsql ): cache entries=10622267 base cache memory=3744797984 > bytes > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( international/pgsql ): cache entries=10622267 base cache > memory=3744797984 bytes > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): [/usr/local/collector/pretagmap] (re)loading map. > INFO ( default/core ): [/usr/local/collector/pretagmap] map successfully > (re)loaded. > INFO ( default/core ): waiting for NetFlow/IPFIX data on :::2100 > ..... > ..... > ..... > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50101] version [10] seqno [2909066] > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50101] version [10] seqno [2909067] > DEBUG ( default/core ): Received NetFlow/IPFIX packet from > [172.31.255.5:50101] version [10] seqno [2909068] > ( national/pgsql ) *** Purging queries queue *** > ( international/pgsql ) *** Purging queries queue *** > ERROR ( international/pgsql ): PRIMARY 'pgsql' backend trouble. > ERROR ( international/pgsql ): The SQL server says: Failed connecting to > dbname=international_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > ERROR ( national/pgsql ): PRIMARY 'pgsql' backend trouble. > ERROR ( national/pgsql ): The SQL server says: Failed connecting to > dbname=national_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > INFO ( international/pgsql ): *** Purging cache - START (PID: 3529) *** > INFO ( national/pgsql ): *** Purging cache - START (PID: 3528) *** > ERROR ( international/pgsql ): BACKUP 'pgsql' backend trouble. > ERROR ( national/pgsql ): BACKUP 'pgsql' backend trouble. > ERROR ( international/pgsql ): The SQL server says: Failed connecting to > dbname=international_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > ERROR ( national/pgsql ): The SQL server says: Failed connecting to > dbname=national_nfacct user=nfacct password=s3cr3t host=aa.bb.cc.122 > > INFO ( national/pgsql ): *** Purging cache - END (PID: 3528, QN: 0/8451, ET: > 127) *** > INFO ( default/core ): OK, Exiting ... > INFO ( international/pgsql ): *** Purging cache - END (PID: 3529, QN: > 0/27494, ET: 127) *** > > > > Thanks for your help.. > _______________________________________________ > 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
