Postgresql – restore compressed dump query in postgresql asks password

postgresqlpsqlUbuntu

I'm using ubuntu OS and I took backup using 

sudo -u postgres pg_dump dbname | gzip > filename.gz

in the file path as…

/home/administrator/Documents/DB_Backup# sudo -u postgres pg_dump dbname | gzip > filename.gz

Later, I tried to restore it back using these following below…

 1. gunzip -c filename.gz | psql glialmine
 2. cat filename.gz | gunzip | psql
 3. sudo -u postgres  psql pg_restore -d dbname  filename.gz

all these 3 restore option I used asks password, but I have only one login and I tried it and its not accepting it I also tried root password and linux login password too but its not working.

For first two it asks password like below 

Password:
psql: fe_sendauth: no password supplied

and for 3rd one it asks password and its error for my password like below

 Password for user pg_restore: 
 psql: FATAL:  password authentication failed for user "pg_restore"

Best Answer

Find the postgresql installation folder containing pg_hba.conf. Edit the authentication chapter at the end of this file, adding 

local   all         all                               trust
host    all         all         127.0.0.1/32          trust
host    all         all         ::1/128               trust

and restart. After that postgresql should not ask for the password to authenticate into any account if this is done from the same machine where the postgresql server is running.

Afterwards, change passwords to be sure you know them. Finally, remove the added lines from pg_hba.conf for more security. You can only bypass authentication because you already have root rights on that computer.

Related Question