[Bucardo-general] Trouble shooting db connections

Paul Tader ptader at spoton.com
Mon Oct 1 20:45:09 UTC 2018


On Sep 21, 2018, 3:06 PM -0500, David Christensen <david at endpoint.com>, wrote:
> > On Sep 21, 2018, at 2:40 PM, Paul Tader <ptader at spoton.com> wrote:
> >
> > On Sep 21, 2018, 2:13 PM -0500, David Christensen <david at endpoint.com>, wrote:
> > > > On Sep 21, 2018, at 1:45 PM, Paul Tader <ptader at spoton.com> wrote:
> > > >
> > > > On Sep 21, 2018, 1:28 PM -0500, David Christensen <david at endpoint.com>, wrote:
> > > > > > On Sep 21, 2018, at 12:36 PM, Paul Tader <ptader at spoton.com> wrote:
> > > > > >
> > > > > > Is there a way to troubleshoot why the "add sync" command fails?
> > > > > >
> > > > > > This is the second sync I've run from this host between a different set of hosts (DEV and QA environments). The hosts have been added and a psql connection from the command line works fine but when I try to setup a sync it connections to one of the databases times out.
> > > > > >
> > > > > > $ bucardo add sync qasync tables=all dbs=qadbs --verbose
> > > > > > DBD::Pg::st execute failed: ERROR: DBI connect('dbname=mydb;host=123.123.123.123;port=5432','migration',...) failed: could not connect to server: Connection timed out
> > > > > > Is the server running on host "123.123.123.123" and accepting
> > > > > > TCP/IP connections on port 5432? at line 63.
> > > > > > CONTEXT: PL/Perl function "validate_goat" at /usr/local/bin/bucardo line 5209.
> > > > >
> > > > > Is the “bucardo” database running on a different server as the Bucardo daemon? Some database connections are made from the server that the Bucardo daemon runs on and some are made directly from the database in plperl functions, so I suspect the “bucardo” database is the piece having connection issues, potentially due to firewalls or other access. If you can run a test psql connection from the database server itself then that may help in debugging this issue.
> > > > >
> > > > Sorry, I should have clarified. Yes, the bucardo database, daemon and commands are all run from a “admin” node. Connections from the admin node to the two database I want to be apart of the sync are successful. I also checked the connection to the local (bucardo) database and it was fine.
> > >
> > > Hmm, if it were SELinux issues or something I’d expect a connection denied not a timeout. Can you raise your log_level to DEBUG and see what it logs for the DSN? Looking for the output of this line:
> > >
> > > elog(DEBUG, "Connecting in validate_goat as $db{dsn} user=$db{user} pid=$$ for table $schema.$table");
> > >
> > > Then could have a test perl script which just tries to do a DBI->connect as so:
> > >
> > > $dbh = DBI->connect($db{dsn}, $db{user}, $db{pass},
> > > {AutoCommit=>0, RaiseError=>1, PrintError=>0});
> > >
> > > with those corresponding logged values.
> > >
> > > If that works then we can see about next steps.
> > > --
> > > David Christensen
> > > End Point Corporation
> > > david at endpoint.com
> > > 785-727-1171
> > >
> > >
> > >
> > Hmm nothing. I set the log_level to debug in ~/.bucardorc and restarted but saw nothing logged before/during/after the command.
>
> Sorry, that would be Postgres’ “log_min_messages” parameter, and it will show up in the PostgreSQL log file. You could probably just change on the validate_goat function via running the following in your “bucardo” database:
>
> ALTER FUNCTION bucardo.validate_goat() SET log_min_messages = ‘DEBUG’;
>
> (Hand-waving syntax here; if you want to just change globally you can as well, it’ll just introduce a lot more noise.)
>
> --
> David Christensen
> End Point Corporation
> david at endpoint.com
> 785-727-1171
>
>
Unfortunately, because the disk containing the WAL files filled up, the database shutdown and I had to restart the bucardo process and the original problem is back, adding a sync fails but psql connections from the command line succeed.

$ bucardo add sync mysync tables=all dbs=qasync --verbose
DBD::Pg::st execute failed: ERROR: DBI connect(‘dbname=mydb;host=123.123.123.123;port=5432','migration',...) failed: could not connect to server: Connection timed out
Is the server running on host “123.123.123.123" and accepting
TCP/IP connections on port 5432? at line 63.
CONTEXT: PL/Perl function "validate_goat" at /usr/local/bin/bucardo line 5209.


I did set:

 ALTER FUNCTION bucardo.validate_goat() SET log_min_messages = ‘DEBUG’;

…but the postgres logs echo the same error.


$ bucardo list dbs
Database: qaec2 Status: active Conn: psql -p 5432 -U migration -d mydb -h 123.123.123.123
Database: qards Status: active …

$ psql -p 5432 -U migration -d mydb -h 123.123.123.123
psql (9.6.10, server 9.6.9)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

server=# \q



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20181001/6e8137b8/attachment.html>


More information about the Bucardo-general mailing list