pg_dump and alter database

From: <btober(at)seaworthysys(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: pg_dump and alter database
Date: 2003-08-22 12:36:19
Message-ID: 64524.216.238.112.88.1061555779.squirrel@$HOSTNAME
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carmen Gloria Sepulveda Dedes 2003-08-22 13:13:46 Re: Postgresql for Solaris on Sparc
Previous Message Alvaro Herrera 2003-08-22 12:34:24 Re: Proble when running DBMirror.pl