Re: Loading 7.4 dump to 8.1 with user-custom search_path

> decibel(at)fritz(dot)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