Thread: 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.