pgcli: Can't connect to pgbouncer database

Description

I’m trying to connect to pgbouncer admin panel using pgcli, but the result is:

$ pgcli -h localhost -p 6432 -u cs pgbouncer
/home/br0ke/.local/lib/python2.7/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
Password for cs: 
invalid command 'SHOW ALL', use SHOW HELP;

and then pgcli exits with error code. I’m able to connect to another actual databases proxied through pgbouncer, but not to pgbouncer virtual database. It seems that this virtual database lacks some functionality needed by pgcli.

I use pgbouncer version 1.9.0.

Your environment

Please provide your OS and version information. Linux, Fedora 30.

$ uname -a
Linux PC-004 5.2.9-200.fc30.x86_64 #1 SMP Fri Aug 16 21:37:45 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux

Please provide your CLI version. Version: 2.0.2
What is the output of pip freeze command.

$ pip freeze
DEPRECATION: Python 2.7 will reach the end of its life on January 1st, 2020. Please upgrade your Python as Python 2.7 won't be maintained after that date. A future version of pip will drop support for Python 2.7.
backports.csv==1.0.6
backports.ssl-match-hostname==3.7.0.1
bernhard==0.2.6
bottle==0.12.16
cassandra-driver==3.17.1
certifi==2018.11.29
chardet==3.0.4
cli-helpers==1.1.0
Click==7.0
configobj==5.0.6
CouchDB==1.2
dbus-python==1.2.8
docker==4.0.1
elasticsearch==7.0.1
enum-compat==0.0.2
enum34==1.1.6
futures==3.2.0
Glances==3.1.0
humanize==0.5.1
idna==2.8
influxdb==5.2.2
ipaddress==1.0.22
kafka-python==1.4.6
netifaces==0.10.9
nvidia-ml-py3==7.352.0
paho-mqtt==1.4.0
pbkdf2==1.3
pgcli==2.0.2
pgspecial==1.11.5
pika==1.0.1
pipenv==2018.11.26
ply==3.11
potsdb==1.0.3
prometheus-client==0.6.0
prompt-toolkit==2.0.7
protobuf==3.7.1
psutil==5.6.2
psycopg2==2.7.6.1
pwquality==1.4.0
py-cpuinfo==5.0.0
pyasn1==0.4.5
pycairo==1.18.1
pycryptodomex==3.8.1
pygal==2.4.0
Pygments==2.3.1
PyGObject==3.32.2
pymdstat==0.4.2
pysmi==0.3.4
pysnmp==4.4.9
pystache==0.5.4
python-dateutil==2.8.0
pytz==2019.1
pyxdg==0.26
pyzmq==18.0.1
requests==2.22.0
rpm==4.14.2.1
scandir==1.10.0
setproctitle==1.1.10
six==1.12.0
sqlparse==0.2.4
statsd==3.3.0
tabulate==0.8.2
terminaltables==3.1.0
typing==3.6.6
urllib3==1.25.2
vboxapi==1.0
virtualenv==16.2.0
virtualenv-clone==0.5.0
wcwidth==0.1.7
websocket-client==0.56.0
wifi==0.3.8
zeroconf==0.19.1

About this issue

Original URL
State: closed
Created 5 years ago
Comments: 21 (10 by maintainers)

Most upvoted comments

@amjith and @and-semakin,

I think the developer in pgbouncer was talking about this way to get extra parameters from connection:

http://initd.org/psycopg/docs/connection.html#connection.get_parameter_status

I did a quick experiment in a branch and it seems to work just fine. Here is the PR (still WIP):

https://github.com/dbcli/pgcli/pull/1094

In fact, I was also able to get rid of an extra query to retrieve server version.

I suggest you try out the branch, and we will see if all tests pass with the change.

j-bennet on Aug 28, 2019

Connection error fixed in 2.2.0 (released). Not closing the issue since it’s only a partial fix.

j-bennet on Dec 4, 2019

@and-semakin I made multiple fixes in https://github.com/dbcli/pgcli/pull/1097. Please try the branch again. Thanks!

j-bennet on Sep 6, 2019

@and-semakin It’s like playing a game of whack-a-mole. 😃 I finally gave up and installed pgbouncer. It should be easier to debug things this way. Still working on it.

j-bennet on Sep 6, 2019

@and-semakin Thank for following that up with pgbouncer. I’ve asked the dev about an alternative. If I don’t hear anything back I’ll change the SHOW ALL query to be an opportunistic query and recover gracefully when we can’t make that query.

amjith on Aug 27, 2019