pg_dumpall problem when roles have default schemas - Mailing list pgsql-general

From btober@ct.metrocast.net
Subject pg_dumpall problem when roles have default schemas
Date
Msg-id 63617.1219936151@ct.metrocast.net
Whole thread Raw
List pgsql-general
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.


pgsql-general by date:

Previous
From: "Harshad Pethe"
Date:
Subject: Hi
Next
From: Jerry LeVan
Date:
Subject: Re: OS X library path issues for libpq (ver 8.3