Postgres Pro
Downloads
Home   >   mailing lists

Thread: 9.2 upgrade glitch with search_path

9.2 upgrade glitch with search_path

From
Scott Ribe
Date:
Built & installed 9.2.3. Dumped 9.1 db (using 9.2 pg_dump IIRC). Restored.

Database search path was not restored. Had to execute alter database ... set search_path to...

Dump commands:

pg_dumpall -g -f roles.dump
pg_dump -F c -Z 0 -v pedcard > db.dump

Restore commands:

psql -f roles.dump postgres
pg_restore -j 4 -veC -d postgres db.dump


--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: 9.2 upgrade glitch with search_path

From
Tom Lane
Date:
Scott Ribe <scott_ribe@elevated-dev.com> writes:
> Built & installed 9.2.3. Dumped 9.1 db (using 9.2 pg_dump IIRC). Restored.
> Database search path was not restored. Had to execute alter database ... set search_path to...

That's a hole in the particular dump methodology you selected:

> pg_dumpall -g -f roles.dump
> pg_dump -F c -Z 0 -v pedcard > db.dump

pg_dump does not dump/restore database properties, only database
contents.  Properties are the responsibility of pg_dumpall, which
you bypassed (for databases anyway).

There's been some discussion of refactoring these responsibilities,
but no consensus.

            regards, tom lane

Re: 9.2 upgrade glitch with search_path

From
Scott Ribe
Date:
On Jan 13, 2013, at 2:51 PM, Tom Lane wrote:

> That's a hole in the particular dump methodology you selected:
>
>> pg_dumpall -g -f roles.dump
>> pg_dump -F c -Z 0 -v pedcard > db.dump
>
> pg_dump does not dump/restore database properties, only database
> contents.  Properties are the responsibility of pg_dumpall, which
> you bypassed (for databases anyway).
>
> There's been some discussion of refactoring these responsibilities,
> but no consensus.

Ah, this is my first upgrade using that methodology, in order to get concurrent restore functionality. Prior to this
I'vealways used pg_dumpall. 

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Re: 9.2 upgrade glitch with search_path

From
Bruce Momjian
Date:
On Sun, Jan 13, 2013 at 04:51:55PM -0500, Tom Lane wrote:
> Scott Ribe <scott_ribe@elevated-dev.com> writes:
> > Built & installed 9.2.3. Dumped 9.1 db (using 9.2 pg_dump IIRC). Restored.
> > Database search path was not restored. Had to execute alter database ... set search_path to...
>
> That's a hole in the particular dump methodology you selected:
>
> > pg_dumpall -g -f roles.dump
> > pg_dump -F c -Z 0 -v pedcard > db.dump
>
> pg_dump does not dump/restore database properties, only database
> contents.  Properties are the responsibility of pg_dumpall, which
> you bypassed (for databases anyway).
>
> There's been some discussion of refactoring these responsibilities,
> but no consensus.

pg_upgrade fixed this for pg_dumpall -g --binary-upgrade by outputing
the per-database settings.

Isn't this a bug?  Seems there is no way to get these exported without
pg_dumpall non-'g' mode.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: 9.2 upgrade glitch with search_path

From
Tom Lane
Date:
Bruce Momjian <bruce@momjian.us> writes:
> On Sun, Jan 13, 2013 at 04:51:55PM -0500, Tom Lane wrote:
>> pg_dump does not dump/restore database properties, only database
>> contents.  Properties are the responsibility of pg_dumpall, which
>> you bypassed (for databases anyway).

> Isn't this a bug?  Seems there is no way to get these exported without
> pg_dumpall non-'g' mode.

No, it's not a bug.  It is something that'd be nice to make more
flexible, but "bug" is an inappropriate pejorative.  (If you are
unsure about the dividing line, ask yourself if we'd back-patch
such a change.)

            regards, tom lane