Skip site navigation (1) Skip section navigation (2)

Re: Moving a database AND changing the superuser

From: Rich Cullingford <rculling(at)sysd(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Moving a database AND changing the superuser
Date: 2003-12-15 16:57:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Tom Lane wrote:
> Rich Cullingford <rculling(at)sysd(dot)com> writes:
>>I did a pg_dumpall in preparation for moving one of our databases from 
>>PG7.3 to PG7.4, but I just realized I have another problem: that DB 
>>(which has served us faithfully for some time) was created for superuser 
>>'postgres,' whilst our new DBs use a superuser name that's aligned with 
>>our product.
> If you used 7.4 pg_dump, I believe that the dump script does not assume
> any particular superuser name (it says RESET SESSION AUTHENTICATION
> when it wants to get back into superuser state, so as long as you start
> it as a superuser, you're golden).

Hmmm, how do you use 7.4 utilities against a 7.3 DB? Run them out of the 
7.4 bin dir, but with PGDATA, etc., pointing to the old database (which 
must be running, right?)?
> IIRC, 7.3 pg_dump had an option to specify the superuser name to use.

I'm assuming that you don't mean:

-S username

which seems to be for disabling triggers, but perhaps the following as a 
parameter to pg_dumpall:

-X use-set-session-authorization

     Normally, if a (plain-text mode) script generated by pg_dump must 
alter the current database user (e.g., to set correct object 
ownerships), it uses the psql \connect command. This command actually 
opens a new connection, which might require manual interaction (e.g., 
passwords). If you use the -X use-set-session-authorization option, then 
pg_dump will instead output SET SESSION AUTHORIZATION commands. This has 
the same effect, but it requires that the user restoring the database 
from the generated script be a database superuser. This option 
effectively overrides the -R option.

     Since SET SESSION AUTHORIZATION is a standard SQL command, whereas 
\connect only works in psql, this option also enhances the theoretical 
portability of the output script.

     This option is only meaningful for the plain-text format. For the 
other formats, you may specify the option when you call pg_restore.

Others on the list have suggested a global replace of 'postgres' with my 
superuser name, but it's hard to see what effects that would have in a 
2.3G dump file.

Thanks for your help,
                        Rich C.

In response to


pgsql-admin by date

Next:From: Rhaoni Chiu PereiraDate: 2003-12-15 17:01:15
Subject: Unsubscribe
Previous:From: cmeinhardtDate: 2003-12-15 16:46:40
Subject: Unsubscribe

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group