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?
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.