0

I have a Postgres database defined with the public schema and it is accessed from within a Python application. Before doing a pg_dump, the tables can be accessed without using a qualified table name, for example select * from user works. After doing the pg_dump, select * from user fails with a relation "user" does not exist error, but select * from public.user works. No restore has been performed.

Since this is within an application, I cannot change the access to include the schema. The application uses sqlalchemy and pgbouncer for interacting with the database.

In trying to figure out what's happening, I've discovered that running pg_dump causes the session to change. Before running the command, by querying pg_stat_activity, I can see there are 10 sessions in the pool, one active and nine idle. After running the command, a different session is active and the other nine are idle. Also, the settings in pg_db_role_setting and pg_user look correct for the session that I can see. But, even when those look correct, the query select * from user fails.

Also, just for reference, the code currently does not contain pg_dump and runs fine. As soon as I add the pg_dump, I see the issues mentioned.

Is there anything in pg_dump that could be causing what I'm seeing or is it just a result of going to another session? Since these are some of the first commands being run after running migrations, is there any reason the sessions should have different settings? What else am I missing?

Thanks!

Cheryl Sabella
  • 1
  • 1
  • 1) There is a `public` schema defined in the database, no the other way around. 2) Best guess is the [search_path](https://www.postgresql.org/docs/15/ddl-schemas.html#DDL-SCHEMAS-PATH) has been changed. In `psql` do `show search_path ;` . Also `select name, setting, source, sourcefile from pg_settings where name = 'search_path';` to find what is setting it. – Adrian Klaver Jan 28 '23 at 16:44
  • Hi Adrian, thanks for the helpful suggestion. I had been logging the output of similar queries, but not `show search_path;`. Luckily for me, the first run with that showed it changed from the default before the `pg_dump` call to an empty string after the call. However, rerunning the job showed the default value both before and after the `pg_dump` call, confirming the randomness of the issue. – Cheryl Sabella Jan 28 '23 at 21:53
  • Thanks to the Stack Overflow "related" questions I saw a link to [this one](https://stackoverflow.com/questions/64066007/after-using-pg-dump-behind-pg-bouncer-the-search-path-appears-to-be-altered-and), which is exactly what I needed. In all my searching before I posted, I didn't see that question, so sorry for the duplicate. – Cheryl Sabella Jan 28 '23 at 21:57

0 Answers0