I am wondering how can I generate a dumped database so that it can be restored without requiring the original database username and password?

Ideally I'd like to know the pg_dumpall as well as mysqldump commands to do so.

This can be useful in different circumstances, for example, when one wants to dump a database to be released to the public.

  • MySQL privileges aren't stored in each database. At least for MySQL, the privileges needed to restore a database are unrelated to those needed to dump it (i.e. to restore the database, you need to be able to login to the target MySQL server, and have permission to create a database). – cyberx86 Jan 1 '12 at 5:31

Why would you ever need the original credentials to restore a database? Restoring only requires the credentials of the database you're restoring to, not the original source.

  • Well, at least for this mysql to postgresql conversion I understand that the original credentials are required: groups.drupal.org/node/15793 – alfish Jan 1 '12 at 5:54
  • 1
    That migration script needs the source and destination credentials to do its migration. This has nothing to do with database dumps requiring original credentials to restore. – EEAA Jan 1 '12 at 6:01

Don't use pg_dumpall for postgres - use pg_dump. That will give you a dump that's independent of the original passwords. Then use pg_dumpall -g to get the users and passwords - so you at least know which usernames are supposed to be there.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.