Обсуждение: pg_dump and alter database

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

<btober@seaworthysys.com> writes:
> 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.

This functionality is in pg_dumpall, not pg_dump.

(Whether that's the right place for it is debatable, perhaps.)

            regards, tom lane

> <btober@seaworthysys.com> writes:
>> 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.
>
> This functionality is in pg_dumpall, not pg_dump.
>
> (Whether that's the right place for it is debatable, perhaps.)

I thought maybe I overlooked that, however, I don't see any explicit
reference to this in man pg_dumpall.

I HAVE used pg_dumpall -g to make a backup of of users and groups, but
this output does not include the ALTER DATABASE commands. (I have not
used pg_dumpall to backup an entire database cluster, however.)

Does the ALTER DATABASE command get written only when I do an
unconditional pg_dumpall, i.e., produce a dump of all databases in the
cluster? If that is the case, then I do think the feature ought to be
included in pg_dump, because schemas would, I think, generally be
database-specific and not necessarily applicable to all databases in a
cluster, and so it would seem to make sense to have it in included in the
output from pg_dump, IMHO. Can that be made to happen? (Sorry that I
don't know enough about the PostgreSQL to take a stab at modifiying the
source code myself to implements this.)

~Berend Tober

<btober@seaworthysys.com> writes:
> I HAVE used pg_dumpall -g to make a backup of of users and groups, but
> this output does not include the ALTER DATABASE commands.

No, it looks like pg_dumpall dumps ALTER DATABASE operations for a
particular database when it dumps that database.

> Does the ALTER DATABASE command get written only when I do an
> unconditional pg_dumpall, i.e., produce a dump of all databases in the
> cluster? If that is the case, then I do think the feature ought to be
> included in pg_dump,

I think there were a couple of arguments for doing it this way.  I can
see a permissions issue for one.  pg_dumpall scripts assume they will be
run by superuser, but pg_dump scripts try to avoid that assumption.
Also, a pg_dump script doesn't (and shouldn't, IMHO) assume it knows the
name of the database it's being restored into.

Peter, do you recall any other issues?

            regards, tom lane

> <btober@seaworthysys.com> writes:
>> I HAVE used pg_dumpall -g to make a backup of of users and groups,
>> but this output does not include the ALTER DATABASE commands.
>
> No, it looks like pg_dumpall dumps ALTER DATABASE operations for a
> particular database when it dumps that database.
>
>> Does the ALTER DATABASE command get written only when I do an
>> unconditional pg_dumpall, i.e., produce a dump of all databases in
>> the cluster? If that is the case, then I do think the feature ought
>> to be included in pg_dump,
>
> I think there were a couple of arguments for doing it this way.  I can
> see a permissions issue for one.  pg_dumpall scripts assume they will
> be run by superuser, but pg_dump scripts try to avoid that assumption.
> Also, a pg_dump script doesn't (and shouldn't, IMHO) assume it knows
> the name of the database it's being restored into.
>

I see what you mean about the database name issue, which has the most
impact in my particular case, of course. Would it be possible to do
something like

ALTER DATABASE CURRENT_DATABASE() SET search_path = schema1, schema2;

?

~Berend Tober

Tom Lane writes:

> Also, a pg_dump script doesn't (and shouldn't, IMHO) assume it knows the
> name of the database it's being restored into.

That is pretty much it; pg_dump doesn't record any information about the
database, no matter whether that information happens to be represented in
the command CREATE DATABASE or ALTER DATABASE.  But if you use pg_dump -C,
then ALTER DATABASE ought to be dumped.

--
Peter Eisentraut   peter_e@gmx.net

> <btober@seaworthysys.com> writes:
> > I HAVE used pg_dumpall -g to make a backup of of users and groups, but
> > this output does not include the ALTER DATABASE commands.

Another related problem. pg_dump/pg_restore doesn't properly restore GRANT
CREATE ON DATABASE

pg_restore --create db.dump:

--Sample--
[...]

CREATE DATABASE db WITH TEMPLATE = template0 ENCODING = 8;

\connect db pgsql

\connect - dnsadm

--
-- TOC entry 4 (OID 20645)
-- Name: dns; Type: SCHEMA; Schema: -; Owner: dnsadm
-- Data Pos: 0
--

CREATE SCHEMA dns;

--End sample--

Output:

psql template1 -f /tmp/x
CREATE DATABASE
You are now connected to database db as user pgsql.
You are now connected as new user dnsadm.
psql:/tmp/x:13: ERROR:  db: permission denied

Regards.

Fernando.