Thread: Error during restore
Hi,
I´m trying to restore a client cluster in my Linux box but during the restore the following error is reported:
__________________________________________________________
DELETE 0
CREATE USER
'
\set ON_ERROR_STOP 'true'
--
-- PostgreSQL database cluster dump
--
\connect "template1"
You are now connected to database "template1".
--
-- Users
--
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
CREATE USER "TOTALL" WITH SYSID 100 PASSWORD 'md5fa2b2cda5e1d8577a287f118620a4d58' CREATEDB CREATEUSER;
ALTER USER "BENK" SET search_path TO '"BENK", public, pg_catalog';
psql:dumpall7.4.6_0902:14: ERROR: schema ""BENK", public, pg_catalog" does not exist
CREATE USER
'
\set ON_ERROR_STOP 'true'
--
-- PostgreSQL database cluster dump
--
\connect "template1"
You are now connected to database "template1".
--
-- Users
--
DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');
CREATE USER "TOTALL" WITH SYSID 100 PASSWORD 'md5fa2b2cda5e1d8577a287f118620a4d58' CREATEDB CREATEUSER;
ALTER USER "BENK" SET search_path TO '"BENK", public, pg_catalog';
psql:dumpall7.4.6_0902:14: ERROR: schema ""BENK", public, pg_catalog" does not exist
__________________________________________________________
The schema is only created somewhere after this commands. It´s like the execution order is wrong.
I used pg_dumpall withou any options to create the dump.
What could be wrong?
Reimer
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador do Yahoo! agora.
Carlos Henrique Reimer <carlosreimer@yahoo.com.br> writes:
> I�m trying to restore a client cluster in my Linux box but during the restore the following error is reported:
> ALTER USER "BENK" SET search_path TO '"BENK", public, pg_catalog';
> psql:dumpall7.4.6_0902:14: ERROR: schema ""BENK", public, pg_catalog" does not exist
I believe we decided this was a backend bug: the ALTER command shouldn't
be trying to verify the validity of the search_path setting. [ digs in
CVS logs... ] Ah, here we go:
2004-01-19 14:04 tgl
* src/: backend/access/transam/xlog.c, backend/catalog/namespace.c,
backend/commands/variable.c, backend/utils/adt/datetime.c,
backend/utils/adt/pg_locale.c, backend/utils/adt/regexp.c,
backend/utils/misc/README, backend/utils/misc/guc.c,
include/access/xlog.h, include/catalog/namespace.h,
include/commands/variable.h, include/utils/builtins.h,
include/utils/datetime.h, include/utils/guc.h,
include/utils/guc_tables.h, include/utils/pg_locale.h,
interfaces/ecpg/pgtypeslib/dt.h: Repair problem identified by
Olivier Prenant: ALTER DATABASE SET search_path should not be too
eager to reject paths involving unknown schemas, since it can't
really tell whether the schemas exist in the target database.
(Also, when reading pg_dumpall output, it could be that the schemas
don't exist yet, but eventually will.) ALTER USER SET has a
similar issue. So, reduce the normal ERROR to a NOTICE when
checking search_path values for these commands. Supporting this
requires changing the API for GUC assign_hook functions, which
causes the patch to touch a lot of places, but the changes are
conceptually trivial.
The fix was too complex to consider back-patching to older versions, so
this is only fixed in 8.0. What you'll have to do in 7.4 is manually
re-issue the ALTER USER command after the dump is reloaded.
It looks like you may also be dealing with an old pg_dumpall bug: it
shouldn't be trying to put quotes around the entire search_path value.
However, there's not much point in worrying about that when the backend
won't take the command anyway until the schema exists ...
regards, tom lane