[BUG]: Cannot use pgbouncer / connection pooling

[discostur]

Describe the bug

• if you try to use pgbouncer for connection pooling with postgresql you get a connection error on the migration job

Caused by:                                                                                                                                                                                                                                                                                        
PG::ConnectionBad: connection to server at "10.1.0.37", port 25061 failed: FATAL:  no such database: postgres (PG::ConnectionBad)                                                                                                                                                                 
/usr/local/bundle/gems/pg-1.5.9/lib/pg/connection.rb:709:in 'PG::Connection#async_connect_or_reset'                                                                                                                                                                                               
/usr/local/bundle/gems/pg-1.5.9/lib/pg/connection.rb:844:in 'PG::Connection.connect_to_hosts'                                                                                                                                                                                                     
/usr/local/bundle/gems/pg-1.5.9/lib/pg/connection.rb:772:in 'PG::Connection.new'                                                                                                                                                                                                                  
/usr/local/bundle/gems/pg-1.5.9/lib/pg.rb:63:in 'PG.connect'     

The lago migration job seems to make a database connection test at the beginning towards the database with the name "postgres". Since the postgres connection pool is "tailored" against the lago db (which should be different to default postgres db) the conneciton check fails because the database cannot be reached.

Regards,
Kilian

[jdenquin]

Hello @discostur

We need to do some adaptation on the configuration side to support PgBouncer out of the box. I'll keep you posted on how we can leverage this!

[discostur]

@jdenquin what would really help at a first step is to not check if the database with name postgres is reachable. Because if you connect to a pgbouncer you only have access to one database. At the moment everytime you release a new version your "migration" container tries to connect to "postgres" database and fails (because like i said its not available through pgbouncer). If the migration container could just check if the configured database (from the secret where it would run the migration) is available that would be a good start ;)

[jdenquin]

@discostur can you try to change the command for migrate job by using bundle exec rails db:migrate only?
Also, if you use pgbouncer, you have to disable prepared statement with ActiveRecord, you should set DATABASE_PREPARED_STATEMENTS env var to false for each back end service

[discostur]

@jdenquin thanks for the hint with the DATABASE_PREPARED_STATEMENTS env var. I will try your recommendations +1

[discostur]

Just a short update: we run pgBouncer v1.21.0 which supports prepared statements. So we tried running lago with both, DATABASE_PREPARED_STATEMENTS true and false and it seems that both are running without errors.

[jdenquin]

really nice, thanks for the update @discostur