Re: Moving a database AND changing the superuser

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
--superuser=username
****

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

******
-X use-set-session-authorization
--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.