Search code examples
postgresqlpg-dumpsearch-path

PostgreSQL search_path issue using pg_dump created plain .sql file

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?

Solution

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