pg_dumpall problem when roles have default schemas
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.
btober@ct.metrocast.net writes:
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.
Could we see a complete example?
Recent versions of the backend are not supposed to throw hard errors in
this situation, because of exactly that hazard. For instance:
regression=# create role joe;
CREATE ROLE
regression=# alter role joe set search_path to foo, bar;
NOTICE: schema "foo" does not exist
NOTICE: schema "bar" does not exist
ALTER ROLE
regression=#
which AFAICS is exactly what will happen while restoring a pg_dumpall
dump.
regards, tom lane
Tom Lane wrote:
btober@ct.metrocast.net writes:
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.Could we see a complete example?
Recent versions of the backend are not supposed to throw hard errors in
this situation, because of exactly that hazard. For instance:regression=# create role joe;
CREATE ROLE
regression=# alter role joe set search_path to foo, bar;
NOTICE: schema "foo" does not exist
NOTICE: schema "bar" does not exist
ALTER ROLE
regression=#which AFAICS is exactly what will happen while restoring a pg_dumpall
dump.
Ah, I understand. I think.
What I saw was not literally and "error", but rather a "notice": The
"alter role ... set search_path" statement doesn't actually fail.
The real problem is my preconceived notion. I was thinking in terms of,
say, a database insert to a table with a foreign key, i.e., that since
the schema to be set doesn't exist, the the statement should actually fail.
The implication of your illustration above is that the "alter role ...
set search_path" statement completes successfully even though it is
setting an invalid search path (invalid because the schema referenced in
the search path to be set does not exist at the time the statement is
executed).
So this behavior, which seems a little counter intuitive, actually makes
for a robust end result.
Tom Lane wrote:
btober@ct.metrocast.net writes:
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.
Could we see a complete example?
Recent versions of the backend are not supposed to throw hard
errors in
this situation, because of exactly that hazard. For instance:
regression=# create role joe;
CREATE ROLE
regression=# alter role joe set search_path to foo, bar;
NOTICE: schema "foo" does not exist
NOTICE: schema "bar" does not exist
ALTER ROLE
regression=#which AFAICS is exactly what will happen while restoring a
pg_dumpall
dump.
Ah, I understand. I think.
What I saw was not literally and "error", but rather a "notice":
The "alter role ... set search_path" statement doesn't actually fail.
The real problem is my preconceived notion. I was thinking in terms
of, say, the analogous situation for a database insert to a table
with a foreign key, i.e., that since the schema to be set doesn't
exist, the the statement should actually fail.
The implication of your illustration above is that the "alter role
... set search_path" statement completes successfully even though it
is setting an invalid search path (invalid because the schema
referenced in the search path to be set does not exist at the time
the statement is executed).
So this behavior, which seems a little counter intuitive, actually
makes for a robust end result.
Import Notes
Resolved by subject fallback