PostgreSQL search_path issue using pg_dump created plain .sql file

Question

Created a Postgres plain text SQL file using pg_dump. When importing the text SQL file using the psql -f everything seems to be running fine.

But the newly imported databases is missing the search_path. The source database has the search_path, I expected the destination database to have the same. Looking at the plain SQL file in an editor I see the SET search_path command. So is this an issue or am I missing something?

Answer 1

The search_path is set per session and can be changed any time. You can store presets for a database, a role or even a role in a particular database. That would be set with commands like:

ALTER DATABASE test SET search_path = blarg,public;
ALTER ROLE foo SET search_path = blarg,public;

Etc.

Or maybe you want a general setting in postgresql.conf to begin with? See link below.

A plain:

SET search_path = blarg,public;

like you see in SQL file only sets a search_path for the session it is executed in.

The underlying issue may be this (quoting the manual):

Database roles are global across a database cluster installation (and not per individual database).

Bold emphasis mine.

A backup of a database with pg_dump (not the whole cluster with pg_dumpall, not including global objects), does not include roles. If you have set a default search_path for a role, then that is not included.

Details:

• How does the search_path influence identifier resolution and the "current schema"

Answer 2

Setting of search_path does not appear in the pg_dump's output even if set on database level, ie. by command

ALTER DATABASE XXX set search_path to aaa, bbb, ccc;

Then, after dumping and restoring the database with pg_dump/pg_restore, the restored database does not have the search_path set, ie. the one coming from source template stays active in the restored database.

Tested with version 13.7.

It is also discussed here PG-mail-list and David J. explains it, but I consider it a bug in pg_dump (or better called: design flaw). All per-database settings should appear in the dump, imho.