pg_dumpall problem when roles have default schemas

From: btober(at)ct(dot)metrocast(dot)net
To: <pgsql-general-owner(at)postgresql(dot)org>
Subject: pg_dumpall problem when roles have default schemas
Date: 2008-08-28 15:09:11
Message-ID: 63617.1219936151@ct.metrocast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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.

Browse pgsql-general by date

  From Date Subject
Next Message btober 2008-08-28 15:10:49 pg_dumpall problem when roles have default schemas
Previous Message Adrian Klaver 2008-08-28 15:04:39 Re: pg_dump problem