Rich Cullingford <[EMAIL PROTECTED]> 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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
