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

VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJz
aXRlOgoKQnVnIHJlZmVyZW5jZTogICAgICAxNDE5MgpMb2dnZWQgYnk6ICAg
ICAgICAgIEhhbnMgQnVzY2htYW5uCkVtYWlsIGFkZHJlc3M6ICAgICAgYnVz
Y2htYW5uQG5pZHNhLm5ldApQb3N0Z3JlU1FMIHZlcnNpb246IDkuNS4zCk9w
ZXJhdGluZyBzeXN0ZW06ICAgV2luZG93cyAxMCA2NGJpdApEZXNjcmlwdGlv
bjogICAgICAgIAoKVG8gdHJhbnNwb3J0IGEgd2hvbGUgZGF0YWJhc2UgZnJv
bSBQcm9kdWN0aW9uIHRvIHRlc3QvZGV2ZWxvcG1lbnQgSSBkdW1wIHRoZQp3
aG9sZSBkYXRhYmFzZSB3aXRoIHBnX2R1bXAgLWQgeHh4ZGIgLUZjIC1mIG15
ZHVtcGZpbGUuZG1wLg0KDQpBZnRlciBjcmVhdGluZyB0aGUgZGF0YWJhc2Ug
b24gdGhlIHRhcmdldCBzeXN0ZW0gd2l0aCBjcmVhdGVkYiBJIHJlc3RvcmUg
dGhlCmRhdGFiYXNlIHdpdGggcGdfcmVzdG9yZSAtZCB4eHhkYiBteWR1bXBm
aWxlLmRtcC4gKG5vIG90aGVyIGZsYWdzIGV4Y2VwdApsb2dpbiBpbmZvcm1h
dGlvbiBpbnZvbHZlZCkNCg0KQWxsIEluZm9ybWF0aW9uIGlzIGNvcnJlY3Rs
eSByZXN0b3JlZCAoMiBzY2hlbWFzIGFuZCB0aGVpciBjb250ZW50KSBleGNl
cHQKdGhlIHNlYXJjaCBwYXRoLg0KDQpPbiB0aGUgb3JpZ2luIGRhdGFiYXNl
IEkgaGFkIGVudGVyZWQNCkFMVEVSIERBVEFCQVNFIHh4eGRiIFNFVCBzZWFy
Y2hfcGF0aCBUTyBwdWJsaWMscGFydGl0aW9uMjsNClRoZSByZXN0b3JlIG9t
aXRzIHNldHRpbmcgdGhlIHNlYXJjaCBwYXRoLCBpdCBtdXN0IGJlIGVudGVy
ZWQgYWdhaW4gd2l0aApBTFRFUiBEQVRBQkFTRSBvbiB0aGUgdGFyZ2V0IG1h
Y2hpbmUuDQoNClRoZSBzYW1lIG9jY3VycyBuYXR1cmFsbHkgYWxzbyBpbiB0
aGUgY2FzZSBvZiBhbiBlbWVyZ2VuY3kgcmVzdG9yZSBpbiBjYXNlCm9mIGZh
aWx1cmUsIHdoaWNoIGJyZWFrcyB0aGUgcmVzdG9yZSBmcm9tIHJ1bm5pbmcg
Y29ycmVjdGx5IHdpdGhvdXQKZXhwZXJ0aXNlIG9mIHRoZSB1bmRlcmx5aW5n
IGRhdGFiYXNlIGluIHRpbWUgb2YgcmVzdG9yYXRpb24uDQoNCldpdGggbXkg
dW5kZXJzdGFuZGluZywgdGhhdCB0aGUgc2VhcmNoIHBhdGggaXMgYW4gaW5o
ZXJlbnQgcGFydCBvZiB0aGUKb3JpZ2luIGRhdGFiYXNlIHN0cnVjdHVyZSwg
dGhlIHBnX2R1bXAvcGdfcmVzdG9yZSBzZXF1ZW5jZSBkb2VzIG5vdApjb21w
bGV0ZWx5IHJlcHJvZHVjZSB0aGUgb3JpZ2luYWwgZGF0YWJhc2UuIEkgdGhp
bmsgdGhpcyBpcyBhIGJ1Zy4NCg0KQWxsIGlzIGRvbmUgb24gOS41LjMsIDY0
Yml0IFdpbmRvd3MgZWRpdGlvbiBvbiBXaW5kb3dzIDY0Yml0IHBsYXRmb3Jt
cywgYnV0Ckkgc3VwcG9zZSBpdCBpcyBwbGF0Zm9ybSBpbmRlcGVuZGVudC4g
SSBoYXZlbid0IHRlc3RlZCBpdCBvbiBkaWZmZXJlbnQKdmVyc2lvbnMgb3Ig
cGxhdGZvcm1zLg0KDQpJIGhhdmVuJ3QgdGVzdGVkIHdoZXRoZXIgcGdfZHVt
cCBvciBwZ19yZXN0b3JlIG9taXRzIHRoZSBzZXR0aW5nIChtb3N0bHkKYmVj
YXVzZSBvZiBub3QgdXNpbmcgYW4gU1FMIGR1bXAgZm9ybWF0KS4NCg0KVGhh
bmtzIGZvciBsb29raW5nDQoKCg==

buschmann@nidsa.net writes:
> On the origin database I had entered
> ALTER DATABASE xxxdb SET search_path TO public,partition2;
> The restore omits setting the search path, it must be entered again with
> ALTER DATABASE on the target machine.

This is operating as designed: pg_dump dumps the content of a database,
not settings attached to the database object as such.  You can use
pg_dumpall (possibly with the -g switch) to capture that info.

There've been repeated discussions about refactoring this division of
labor, but nothing's been agreed to yet.

            regards, tom lane

Thank you for your quick reply (my first post/bug report).

When changeing my database partly to partitions, I introduced two =
schemas to separate current and archive data.

According to Postgres DOC chapter 5.8.3 it is generally not advisable to =
use schema qualified names for any objects but to use search_path =
instead.

In my opinion this encouraged naming of objects without explicit schema =
is semantically part of the application (e.g. functions) even when not =
written by words.

When setting the search_path altered for the database it becomes =
semantically a part of the database and the application. This implies it =
should be dumped with the content of the database to preserve the =
consistency of the application.

The same applies to cases with only one schema with no standard name =
(when public becomes myapplication).

My point is the logical consistency of what consists a database and how =
to transport all information in one container (a dump).

Even the syntax (ALTER DATABASE xxxdb SET SEARCH PATH) suggests this to =
be part of the database and not of a session or the cluster.

These are my 2 cents as being relatively new to PostgreSQL.

Thanks

Hans Buschmann

On Wed, Jun 15, 2016 at 11:49 AM, Hans Buschmann <buschmann@nidsa.net>
wrote:

>
> Thank you for your quick reply (my first post/bug report).
>
> When changeing my database partly to partitions, I introduced two schemas
> to separate current and archive data.
>
> According to Postgres DOC chapter 5.8.3 it is generally not advisable to
> use schema qualified names for any objects but to use search_path instead=
.
>
> In my opinion this encouraged naming of objects without explicit schema i=
s
> semantically part of the application (e.g. functions) even when not writt=
en
> by words.
>
> When setting the search_path altered for the database it becomes
> semantically a part of the database and the application. This implies it
> should be dumped with the content of the database to preserve the
> consistency of the application.
>
> The same applies to cases with only one schema with no standard name (whe=
n
> public becomes myapplication).
>
> My point is the logical consistency of what consists a database and how t=
o
> transport all information in one container (a dump).
>
> Even the syntax (ALTER DATABASE xxxdb SET SEARCH PATH) suggests this to b=
e
> part of the database and not of a session or the cluster.
>
> These are my 2 cents as being relatively new to PostgreSQL.
>
I suspect most people would agree with this sentiment.  But the status-quo,
while less than ideal, allows for the end result to be realized, if
imperfectly, and thus the motivation to donate ones time to improving it is
not that great.


=E2=80=8BThe underlying point is that users are global - but there is no "g=
lobal
shell" to operate from so every command has to be executed while within a
specific database.  The specific mapping you are making when you do "ALTER
DATABASE" has an optional user component (which if not specified simply
means all users) and so it too ends up being global=E2=80=8B.

David J.

On 6/15/2016 8:49 AM, Hans Buschmann wrote:
>
> Even the syntax (ALTER DATABASE xxxdb SET SEARCH PATH) suggests this
> to be part of the database and not of a session or the cluster.

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


--
john r pierce, recycling bits in santa cruz

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

On Wed, Jun 15, 2016 at 1:26 PM, Hans Buschmann <buschmann@nidsa.net> wrote=
:

> 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 th=
e
> 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.
>
=E2=80=8BNot sure if John just mis-understood you, mis-spoke, or doesn't un=
derstand
what "ALTER DATABASE SET" does but indeed his comment was incorrect insofar
as it was used to answer the question you posed.  ALTER DATABASE SET
installs a cluster record that points to a specific database in the same
cluster.  The cluster contains multiple databases and pg_dumpall is used to
obtain those items that are cluster-oriented (even if the specific values
only would pertain to a single database in the cluster).

David J.

I understand the differences between cluster and database and pg_dumpall =
and pg_dump.

In my opinion a pg_dump of a database should pack all informations of =
the application (the database) in the dumpfile in one container, to be =
able to restore it full functional at a different place.

Because the search_path is a crucial information for the application to =
work correctly (like any other object inside the database) it should be =
packed into this container called pg_dump dumpfile.

This should be independent of the current implementation, where we store =
the search_path in a cluster record: The informatation belongs =
semantically to the content of the database, even if it is stored =
elsewhere.

My concern  with promoting this suggestion is to avoid trouble in =
emergency cases, logical consistency, intuitive usage of pg_dump and =
smooth experience for non-expert people.

Hans B.

On Thursday, June 16, 2016, Hans Buschmann <buschmann@nidsa.net> wrote:
>
> I understand the differences between cluster and database and pg_dumpall
> and pg_dump.
>
> In my opinion a pg_dump of a database should pack all informations of the
> application (the database) in the dumpfile in one container, to be able to
> restore it full functional at a different place.
>
> Because the search_path is a crucial information for the application to
> work correctly (like any other object inside the database) it should be
> packed into this container called pg_dump dumpfile.
>
> This should be independent of the current implementation, where we store
> the search_path in a cluster record: The informatation belongs semantically
> to the content of the database, even if it is stored elsewhere.
>
> My concern  with promoting this suggestion is to avoid trouble in
> emergency cases, logical consistency, intuitive usage of pg_dump and smooth
> experience for non-expert people.
>
>
You've made your opinion quite clear and likely others share it.  But
someone needs to design and write a patch - we can't commit opinions.  I'd
suggest, at least pushing for this after 9.6 is released.  No one is really
interested in pondering this days before beta2 is to be released.  I'd
suggest researching past discussions on the topic, I'm sure there are some,
in the meantime.

David J.

Thank you for investigating. (I am not able to contribute with patches, =
at least in the foreseable future...)

By casually following pgsql-hackers I am aware of the current release =
cycle.

I really don't expect this to be addressed immediately with 9.6 b2 =
around the corner.

To have a firm opinion should not be misundertood as a push for a fast =
fix, but seen more as part of quality assurance.

Thanks

Hans B.