After using pg_dump behind pg_bouncer, the search_path appears to be altered and other clients are affected

Question

My network looks like this:

App       (many connections)   pg_bouncer  (few sessions)   PostgreSql
nodes  -----------------------   nodes   -----------------    nodes

So pg_bouncer multiplexes connections giving app nodes the illusion that they are all connected directly.

The issue comes when I launch pg_dump: few milliseconds after the dump finishes, all app nodes fail with errors saying "relation xxxx does not exist" though the table or sequence is actually there. I'm pretty sure the cause is pg_bouncer manipulating the "search_path" variable, so that app nodes no longer find tables in my schema. This happens at dump time even if the dump file is not imported nor executed.

Note, I've searched SO and google and I've seen there are many threads asking about the search_path in the generated file, but that's not what I'm asking about. I have no problems with the generated file, my issue is the pg_bouncer session that other clients are using, and I haven't found anything about this.

The most obvious workaround would probably be to set the search_path manually in the app, but attention, don't fall into this fallacy: it's useless for the app to do it at the beginning since it may be assigned a different pg_bouncer session at the next transaction. And I cannot be setting it all the time.

The next most obvious workaround would be to set it back to the intended value immediately after launching pg_dump, but there's a race condition here, and other nodes are quick enough so that I fear they will still fail.

Is there a way to avoid letting pg_dump manipulate this variable, or making sure it resets it before exiting?

(Also, I'm taking for granted pg_dump and search_path are the cause for this, can you suggest a way to confirm that? All the evidence I have is the errors few milliseconds later and the set search_path instruction in the generated file which produces the same errors if executed.)

Thanks

Inertia

Are you using session or transaction pooling? – Alvarado

@LaurenzAlbe transaction pooling – Inertia

Answer 1

Don't connect pg_dump through pgbouncer with transaction pooling. Just change the port number so it connects directly to the database. pg_dump is incompatible with transaction pooling.

You might be able to get it to work anyway by setting server_reset_query_always = 1

Omeara

Not sure the sysadmins will allow me to connect directly. Is there any official document saying it's not compatible? It might be useful to justify my request. In the meantime I've taken a look at server_reset_query_always and server_reset_query, looks useful, I hope they agree to add that. Thanks! – Inertia

The problem is that with transaction pooling, parameters are not reset. – Alvarado

If connecting directly is not an option, just add a new pool(1 size) and connect using it. – Spotweld