How do I get a Golang Database connection pool to manage connections to multiple hosts in a cluster?

postgresql go replication connection-pooling postgresql-bdr
theory picture theory · Apr 25, 2017 · Viewed 7.3k times · Source

I'm setting up bi-direction replication between four PostgreSQL workers, and I'd like to have my Go database connection pool handle connections to all four. It ought to be able to create multiple connections to them all, randomly select one for any given query, and fail over when a connection drops. Is this doable in the Go database library? Or should I just use pgBouncer and not try to get database/sql or pgx to handle that balancing?

Answer

putu picture putu · Apr 27, 2017

Connection pool in golang is created whenever you call sql.Open(driverName, dataSourceName), where dataSourceName is driver specific configuration for connecting to database. Whenever we change the configuration (i.e. changing host address, schema, username, etc) we need to open new connection, thus new connection pool will be created. If a driver can handle load balancing, it should be configurable in dataSourceName, e.g. as in MariaDB Connector/J high availability configuration.

AFAIK, load balancing not yet supported in lib/pq and pgx. In your case, to connect to database servers in cluster, you need to open different connection pool for each server, then manage the connections (and perform load balancing) manually. This approach needs a lot of work to be done. I think it is better to use pgBouncer.

Related questions

Raw sql transactions with golang prepared statements

I'm having trouble finding some examples that do three of the following things: 1) Allow raw sql transactions in golang. 2) Use prepared statements. 3) Rollback on query failures. I would like to do something like this, but with prepared statements. stmt, stmt_…

Read More
Why even use *DB.exec() or prepared statements in Golang?

I'm using golang with Postgresql. It says here that for operations that do not return rows (insert, delete, update) we should use exec() If a function name includes Query, it is designed to ask a question of the database, and …

Read More
Go: How to get last insert id on Postgresql with NamedExec()

I use jmoiron/sqlx library for communicating with my PostgreSql server in my Go apps. Somewhere on my apps i have this following code: sqlQuery := ` INSERT INTO table_to_insert ( code, status, create_time, create_by ) VALUES ( '', 0, CURRENT_TIMESTAMP, 0 ) …

Read More