Hi,
Went to upgrade from postgresql (RedHat's postgresql rh-postgresql-7.3.6-7) to Fedora core 3 postgresql 7.4.6-1 and encountered a problem. If nothing else this is worth a note on the 7.4 upgrade doc page.
It appears as though pg_dumpall is setting the search_path runtime variable in the databases before it creates the schemas. Further, it appears as though the ALTER DATABASE command used to set the search path does not have the quotes correct. (I used alter database babase_test set search_path to babase, sandbox, '$user'; the ALTER DATABASE written by pg_dumpall does not work.)
Ran pg_dumpall on 7.3. When the input was fed to psql on 7.4 I got the following errors:
CREATE DATABASE ALTER DATABASE psql:7.3.dump:34: ERROR: unrecognized time zone name: "Nairobi" psql:7.3.dump:35: ERROR: schema "babase, sandbox, "$user"" does not exist CREATE DATABASE ALTER DATABASE psql:7.3.dump:38: ERROR: unrecognized time zone name: "Nairobi" psql:7.3.dump:39: ERROR: schema "babase, sandbox, "$user"" does not exist CREATE DATABASE ALTER DATABASE psql:7.3.dump:42: ERROR: unrecognized time zone name: "Nairobi" psql:7.3.dump:43: ERROR: schema "babase, sandbox, "$user"" does not exist You are now connected to database "babase". SET CREATE SCHEMA CREATE SCHEMA
The output of the pg_dumpall is:
ALTER DATABASE babase SET "DateStyle" TO 'European'; ALTER DATABASE babase SET "TimeZone" TO 'Nairobi'; ALTER DATABASE babase SET search_path TO 'babase, sandbox, "$user"'; CREATE DATABASE babase_copy WITH OWNER = babase_admin TEMPLATE = template0 ENCOD ING = 'SQL_ASCII'; ALTER DATABASE babase_copy SET "DateStyle" TO 'European'; ALTER DATABASE babase_copy SET "TimeZone" TO 'Nairobi'; ALTER DATABASE babase_copy SET search_path TO 'babase, sandbox, "$user"'; CREATE DATABASE babase_test WITH OWNER = babase_admin TEMPLATE = template0 ENCOD ING = 'SQL_ASCII'; ALTER DATABASE babase_test SET "DateStyle" TO 'European'; ALTER DATABASE babase_test SET "TimeZone" TO 'Nairobi'; ALTER DATABASE babase_test SET search_path TO 'babase, sandbox, "$user"';
\connect babase -- -- PostgreSQL database dump --
SET SESSION AUTHORIZATION 'babase_admin';
-- -- TOC entry 2 (OID 16979) -- Name: babase; Type: SCHEMA; Schema: -; Owner: babase_admin --
CREATE SCHEMA babase;
-- -- TOC entry 4 (OID 16980) -- Name: sandbox; Type: SCHEMA; Schema: -; Owner: babase_admin --
CREATE SCHEMA sandbox;
Karl <[EMAIL PROTECTED]>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
