How can I redirect database requests in my postgres server

Question

So I have a list of(lot many) scrapers which push lot many data into one of my database location. Unfortunately I have a requirement that my database location have to be changed for some performance issues. Rather than changing my so many scripts config can I have some mechanism implemented in my db server so that database requests coming from certain IP(machines) I would redirect them to other database/server?

Answer 1

As @horse_with_no_name already comment: you can use pgbouncer to do that.

Basically you need to create a pool configuration, and during your maintenance you ajust the pool config to a new server or database.

Take a look at the session [databases] on /etc/pgbouncer.ini file:

[databases]
; no connection info enforces unix socket
foodb =
; bardb config
bardb = host=127.0.0.1 dbname=bardb

So, if you want to redirect the connections for the database bardb for another database, what you need to do is edit the dbname and reload de pgbouncer service. E.G.:

; bardb config
bardb = host=127.0.0.1 dbname=maintenancedb

Please, take a look at the documentation^[1] for more details.

You can do the same with the pgpool^[2].

Hope it helps.

References:

1. https://pgbouncer.github.io/config.html
2. http://www.pgpool.net