Restoring dump of multiuser databases

Started by David Garamondover 21 years ago3 messagesgeneral
Jump to latest
#1David Garamond
lists@zara.6.isreserved.com

All of my non-superusers are restricted from creating databases.
Whenever I upgrade Postgres, I have to hand-edit my dump and change:

CREATE USER usr1 WITH SYSID 101 PASSWORD '...' NOCREATEDB NOCREATEUSER;

into:

CREATE USER usr1 WITH SYSID 101 PASSWORD '...' CREATEDB NOCREATEUSER;

and then after the restore, I do a:

UPDATE pg_shadow SET usecreatedb=false WHERE usesysid <> 1;

Is there an easier way? Or would it make more sense if pg_dumpall
generates this:

\connect template1 postgres
CREATE DATABASE db1 WITH TEMPLATE=template0 ENCODING = 'SQL_ASCII';
UPDATE pg_database SET datdba=101 WHERE datnam='db1';
\connect db1 usr1
...
CREATE TABLE ( ... );

instead of this:

\connect template1 usr1
CREATE DATABASE db1 WITH TEMPLATE=template0 ENCODING = 'SQL_ASCII';
\connect db1 usr1
...
CREATE TABLE ( ... );

That is, the superuser creates the database for the normal user, and
then set the owner to the normal user. This way, the normal user doesn't
have to create the database himself.

--
dave

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: David Garamond (#1)
Re: Restoring dump of multiuser databases

David Garamond <lists@zara.6.isreserved.com> writes:

Is there an easier way?

Use a newer version? pg_dumpall hasn't done it that way since 7.2.

regards, tom lane

#3David Garamond
lists@zara.6.isreserved.com
In reply to: Tom Lane (#2)
Re: Restoring dump of multiuser databases

Tom Lane wrote:

David Garamond <lists@zara.6.isreserved.com> writes:

Is there an easier way?

Use a newer version? pg_dumpall hasn't done it that way since 7.2.

I see. I did upgrade from 7.2 to 7.4.

Thanks.
--
dave