2

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
  • 605,456
  • 145
  • 1,078
  • 1,228
varun7447
  • 574
  • 1
  • 6
  • 27

2 Answers2

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
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 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
0

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.

berenhas
  • 1
  • 2