Jim Wilson <jim@wreath.com> writes: > Including "CREATE ROLE postgres;" in the dumpall will cause the script to > throw an error.
I believe the reason pg_dumpall does that is to avoid worse failures if the target installation has a different bootstrap superuser name than the source did. One error is easier to ignore than hundreds of 'em.
We could avoid that problem if we wanted to invent and use CREATE OR REPLACE ROLE, but that would have downsides of its own, such as silently overwriting the properties of any roles that already exist in the target (IOW, the cases where you *want* to get that error).
Maybe it'd work to invent C.O.R.R. but only use it for the bootstrap superuser, with plain CREATE ROLE for the rest. Haven't really thought through the consequences of that.
regards, tom lane
Perhaps throw a warning instead of an exception if the user name matches the current user that the CREATE ROLE command is operating under AND the current user is also flagged SUPERUSER could work (ie limited C.R.O.R. like functionality). I don't know if that fits with how postgreSQL code works. My workaround in linux comments out the specific CR command:
pg_dumpall | sed -e 's/CREATE ROLE postgres/--CREATE ROLE postgres/' >/mnt/temp_vol/dumpall.sql
It isn't very intuitive that the CREATE ROLE for the bootstrap SUPERUSER is included in the script considering the primary use of the pg_dumpall command. I suspect that eliminating it from pg_dumpall as in my work around, or ignoring it during restore as mentioned above would be equally (or more) intuitive.
Of course it comes down to "problems" with our own data, but in the case of doing full restores during major version upgrades things go much smoother to run the restore with ON_ERROR_STOP turned on.