From a 7.4 dump:

decibel@fritz.2[23:08]~:90>grep search_path fritz-20051106.sql
ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public';

Trying that command in psql...

decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd,
page_log, public';
NOTICE: schema "decibel, rrs, rrd, page_log, public" does not exist
ALTER ROLE
decibel=# \d
No relations found.
decibel=# show search_path;
search_path
----------------------------------
"decibel, rrs, page_log, public"
(1 row)

decibel=#

There are tables in the database...

Something else that struck me looking at some errors from this restore;
would it be a good idea to come up with some kind of dedicated
API/interface for dump to use so that it's not as vulnerable to these
kind of issues? For example, this dumpall has:

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
WHERE datname = 'template0');

before all the CREATE USER statements. Problem with that is:

psql:fritz-20051106.sql:11: ERROR: cannot delete from a view

Granted, this could be handled by creating the needed rules on that
view, but ISTM it would be better if instead we had a function like
pg_delete_all_users that dump called instead. For most of the dump this
isn't much of an issue, because it uses standard commands that we're
really careful about not breaking backwards compatability on.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

> decibel@fritz.2[23:08]~:90>grep search_path fritz-20051106.sql
> ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public';
>
> Trying that command in psql...
>
> decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd,
> page_log, public';
> NOTICE: schema "decibel, rrs, rrd, page_log, public" does not exist
> ALTER ROLE
> decibel=# \d
> No relations found.
> decibel=# show search_path;
> search_path
> ----------------------------------
> "decibel, rrs, page_log, public"
> (1 row)
>
> decibel=#

Yes, that's all fixed in the 8.0 and higher pg_dump. Use the 8.1 dump
to dump your 7.4 database.

> Something else that struck me looking at some errors from this restore;
> would it be a good idea to come up with some kind of dedicated
> API/interface for dump to use so that it's not as vulnerable to these
> kind of issues? For example, this dumpall has:
>
> DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database
> WHERE datname = 'template0');
>
> before all the CREATE USER statements. Problem with that is:
>
> psql:fritz-20051106.sql:11: ERROR: cannot delete from a view
>
> Granted, this could be handled by creating the needed rules on that
> view, but ISTM it would be better if instead we had a function like
> pg_delete_all_users that dump called instead. For most of the dump this
> isn't much of an issue, because it uses standard commands that we're
> really careful about not breaking backwards compatability on.

I believe this is all done in 8.1 pg_dump.

Chris


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq