0

I am using pgbouncer on redshift cluster. When I use pgbouncer on Postgres I can login into pgbouncer instance using psql -h localhost -p xxxx pgbouncer.

But how do I login into pgbouncer setup for redshift? pgbouncer is running on an ec2 instance whereas reshift is managed by aws.

I tried accessing psql using both localhost (where pgbouncer is running) and redshift endpoint. Any idea on how to access pgbouncer instance, which is setup for redshift?

[ec2-user@xxxx ~]$ psql -h localhost -p 5439 -U admin pgbouncer
psql: ERROR:  not allowed
ERROR:  not allowed
[ec2-user@xxxx pgbouncer]$ psql -h xxxxxxxx.us-east-2.redshift.amazonaws.com -p 5439 -U admin pgbouncer
Password for user admin:
psql: FATAL:  database "pgbouncer" does not exist
DJo
  • 2,133
  • 4
  • 30
  • 46
nmakb
  • 1,069
  • 1
  • 17
  • 35

2 Answers2

0

You need to create the pgbouncer database on redshift.

When the redshift cluster was created, a single database was also created (specified), you can find this by looking at the AWS console.

You can then log in to that database, using PSQL if you wish.

psql -h xxxxxxxx.us-east-2.redshift.amazonaws.com -p 5439 -U admin your-redshift-db

from there you can create the pgbouncer database.

create database pgbouncer;

I suggest you use pgbouncer-rr which has more redshift features. https://github.com/awslabs/pgbouncer-rr-patch

Jon Scott
  • 4,144
  • 17
  • 29
  • Thanks Jon, I should have been more clear. Yes I am using pgbouncer-rr and did create pgbouncer database as you mentioned. But this does not show the commands and options which show up when connecting to pgbouncer server on postgresql db. For example - show clients, show connections etc. Does pgbouncer instance not exists when its created for redshift using pgbouncer and pgbouncer-rr-patch? Please let me know if there is any way to access it to monitor the connections. – nmakb Dec 02 '18 at 20:44
  • Do the queries work/run on redshift when routed that way? – Jon Scott Dec 03 '18 at 07:36
  • Yes the queries get executed on Redshift cluster, here is the link of the pgbouncer for redshift. https://aws.amazon.com/blogs/big-data/query-routing-and-rewrite-introducing-pgbouncer-rr-for-amazon-redshift-and-postgresql/ – nmakb Dec 04 '18 at 20:45
  • No, I know how it works! I was asking whether in your setup, does anything run on the redshift cluster when you use pgbouncer-rr? – Jon Scott Dec 05 '18 at 07:54
0

I was able to login using 

$ psql -h pgbouncerhost -p 5439 -U master pgbouncer
nmakb
  • 1,069
  • 1
  • 17
  • 35