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=20
>of a database dump.
>
>you also can simply SET search_path 'somevalue';    and this applies=20
>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=3D# create database test1 template=3Dtemplate0 encoding 'UTF8';
CREATE DATABASE
postgres=3D# \c test1

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


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

(after reconnect to database to start a new session)

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


test1=3D# \q

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

I hope I have been more clear now

Hans Buschmann