'pg_dump fails with error: `FATAL: role "username" does not exist`
I have a db named backupdb, I want to import this to my local rails app so I want to take a dump of it.
When I am running pg_dump backupdb, I am getting below error.
pg_dump: [archiver (db)] connection to database "backupdb" failed: FATAL: role "username" does not exist
what's wrong here. Please help.
I downloaded the db from my email and then trying to create a dump so I can import it to my local rails app
Solution 1:[1]
Try this way, it works!!
$ pg_dump -h localhost -U postgres -Fc mydb > db.dump
Solution 2:[2]
This command will take the backup of complete database
pg_dump -h localhost -U "dbuser" "dbname" -Fc > "pathfilename.backup"
**ex:** pg_dump -h localhost -U mani manidb - Fc > "thamesdb.backup"
for more pg_dump formats please refer to this answer
Solution 3:[3]
You're giving "username" as username, which does not exist. You need to pass a username that exists (postgres would probably do).
add parameters --username=postgres --password and it will ask you for the password for user postgres. (you might have security set to trust in your pg_hba.conf in which case leaving out --password would work.
Solution 4:[4]
If someone has the same problem using intellij, here is what helped me: In my case the Problem was, that i right clicked the datasource, but you have to open it and right click the database itself. So in the image below don't click on localDB. Right click the database name and then do a dump.
Hope this will help someone to solve this confusing UX problem. :D
Solution 5:[5]
I was having the same problem but after trying out different permutations of the command options while referencing the documentation, I was able to get it running with this order of options. The command I used is:
pg_dump.exe -h localhost -U postgres -t "shop*" -t "core*" -t "profiles*" -f "path/to/output/dir_or_file" mydbname
Where:
| option prefix | option | params |
|---|---|---|
| -h | host | localhost |
| -U | username | postgres |
| -t | Tables | Any tables whose name starts with (shop, core, or profiles (NOTE: for django app tables in my case)) |
| -f | file out | path where I want to save the dumped file |
| (End of cmd) | database | mydbname |
Note: as I have multiple versions of postgresql on my computer, I had to call the newest pg_dump executable. (Just for reference)
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | |
| Solution 2 | Community |
| Solution 3 | |
| Solution 4 | |
| Solution 5 | Fuzzlightyear |