Re: [pmacct-discussion] pgsql recovery host
Hi Simon, Good it worked patching against 1.7.0, thanks for your feedback. What may be wrong is that starting with 1.7.1 a minimum libpq version 9.3 is checked - and that check should be failing since you have a 9.2 version. I checked a recent Ubuntu running libpq 9.4 and all compiles fine. Paolo On Thu, May 24, 2018 at 11:44:34PM +, Simon Travers-Jones (Xtreme) wrote: > 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 +, 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 ( d
Re: [pmacct-discussion] pgsql recovery host
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 +, 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
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 +, 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 >
