1

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?

Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
varun7447
  • 516
  • 5
  • 27

1 Answers1

4

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 539,169
  • 125
  • 977
  • 1,137
  • My understanding is like the `search_pat`h that I see in the plain sql file is just for the database objects to be able to successfully import. That doesn't really set the `search_path` for the database even though the source database has the `search_path` set at the time of export.? – varun7447 Mar 06 '17 at 04:24
  • @varun7447: Like I said: a plain `SET` only sets the `search_path` for the current session. – Erwin Brandstetter Mar 06 '17 at 04:27
  • Great., so I just trying to see if there is a better way to get every object and setting as is from source database the same in destination database. Including `search_path`. – varun7447 Mar 06 '17 at 04:30