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