Re: BUG #14192: pg_dump/pg_restore omit setting search_path in restored db

On 6/15/2016 John R Pierce wrote:

>that syntax is indeed stored with the database schema, and would be part
>of a database dump.
>
>you also can simply SET search_path 'somevalue'; and this applies
>only to the current session, and wouldn't be part of the database

This is the point I don't understand.
You said, the syntax ALTER DATABASE xxxdb SET SEARCH PATH is stored in the database and would be part of a database dump.

But to my observation, this is not the case. I certainly set the search path to the database (not the session) on the dumping machine, but it is not restored with pg_restore.

I verified this with a little test database and checked the resulting SQL dump. (You may do it yourself).

The databse search path appeared not in the dump.

To test:

postgres=# create database test1 template=template0 encoding 'UTF8';
CREATE DATABASE
postgres=# \c test1

test1=# create schema test2;
CREATE SCHEMA
test1=# show search_path;
search_path
-----------------
"$user", public
(1 Zeile)

test1=# alter database test1 set search_path TO public,test2;
ALTER DATABASE
test1=# create table pubtable (id int);
CREATE TABLE
test1=# create table test2.p2table (id2 int);
CREATE TABLE

(after reconnect to database to start a new session)

test1=# \dt
Liste der Relationen
Schema | Name | Typ | Eigent³mer
--------+----------+---------+------------
public | pubtable | Tabelle | postgres
test2 | p2table | Tabelle | postgres
(2 Zeilen)

test1=# \q

pg_dump -U postgres -d test1 -f test1_search_path.sql

I hope I have been more clear now

Hans Buschmann