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