I'm not sure whether this list is the appropriate place to pose this
question/comment, but I this place is my best guess of where to discuss a
feature that I don't see in pg_dump with PostgreSQL version 7.3.
The problem I have is that the SQL DDL and DML produced by pg_dump fails
to include the ALTER DATABASE ... SET search_path ... statement that sets
the search path for when I re-load the database from a dump file.
More specifically, I have several schemas in my database, and generally I
want them all in the search path after re-loading a database from pg_dump
output. What I have done as a work-around is created a file with my
command:
alter.sql:
ALTER DATABASE my_database SET search_path = schema1, schema2, schema3,
schema4, public;
and then my normal routine is to execute these two commands
pg_dump -U postgres my_database > my_pgdump_output.sql
cat alter.sql >> my_pgdump_output.sql
so that when I re-load the database by running
psql -f my_pgdump_output.sql -U postgres my_database
against a newly-created, empty database it has the correct, complete
search_path set. (Alternatively, I "manually" run the command listed as
stored in the file alter.sql above, I want this as fully automated as
possible--lazyness is a great motivater.)
Note that the my_pgdump_output.sql DOES contain the numerous "SET
search_path = " and "\connect" statements scattered throughout so as to
set the default schema and owner while re-creating the database tables,
but again what seems to be missing is the final ALTER DATABASE command to
permanently set the search path appropriately.
So, please tell me, am I missing something about the way this works, and
if so help me learn to use it the way I think I should work, or tell me
how to initiate the process of getting this feature considered for
incorporation in to pg_dump.
~Berend Tober
