Re: pg_dumpall problem when roles have default schemas

btober(at)mail(dot)ct(dot)metrocast(dot)net wrote:
> I've run into a problem while migrating an existing 8.2.7 data base to a
> new server running 8.3.3 (although I think the version numbers may not
> matter -- I think I've seen this problem in the past and just lived with
> it since so much of Postgresql is so great!).
>
> The problem stems from the fact that for certain roles, I have defined
> default search paths, and because the output of pg_dumpall includes role
> definitions first, then data base definitions, then schema definitions.
>
> Thus, when piping the output (from legacy host 192.168.2.2) to populate
> the newly initialized cluster, by way of running (on the new host
> 192.168.2.3)
>
>
> pg_dumpall -h 192.168.2.2|psql
>
>
> an error occurs in that first section when the script attempts to set a
> role-specific search path ... because the schema named in the search
> path hasn't been created yet.
>
> Not sure if there is some different way I should be using these tools to
> accomplish this, or if there is a work-around, or if this feature needs
> improvement.

I tested this on 8.3.3:

test=> CREATE USER test;
CREATE ROLE
test=> ALTER USER test SET search_path = 'asdf';
NOTICE: schema "asdf" does not exist
ALTER ROLE
test=> SELECT * FROM pg_user;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd |
valuntil | useconfig
----------+----------+-------------+----------+-----------+----------+----------+--------------------

postgres | 10 | t | t | t | ******** |
|
test | 16385 | f | f | f | ******** |
| {search_path=asdf}
(2 rows)

The ALTER SET command is performed; it only generates a NOTICE. Is
that the problem?

(FYI, you emailed the Postgres general _owner_; that is not the right
address for posting questions.)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +