PostgreSQL: pg_dump: [archiver (db)] connection to database “dbase” failed: FATAL: Peer authentication failed for user “postgres”

19
votes

Solved: I added .pgpass in the home.

I have the line:

host    all             all             127.0.0.1/32            md5

in /etc/postgresql/9.4/main/pg_hba.conf but when I run:

pg_dump -U postgres dbase -f dbase.sql

I get:

pg_dump: [archiver (db)] connection to database "dbase" failed:
FATAL: Peer authentication failed for user "postgres"

3
postgresql
pg_dump -U postgres -h localhost dbase -f dbase.sql – wildplasser
"Peer authentication" means that it's comparing your database username against your Linux username. It should work if you're logged in as postgres. You probably don't want to hit that md5 rule in pg_hba, as the postgres database user generally doesn't have a password. – Nick Barnes
@wildplasser summarized all the things. – Davidson Lima

3 Answers

44
votes

The Problem you have is, that if u dont define the Host, your system will decide.

explicit add "-h localhost", this will fix it

12
votes

If adding -h localhost doesn’t work you can try adding -h 127.0.0.1

pg_dump -h 127.0.0.1 -U <username> -d <database_name> -W > bk_name.sql
0
votes

I encountered this issue when working on a PostgreSQL database on Ubuntu 20.04.

All the PostgreSQL configuration was all good and they have been working fine.

The issue was that I mistakenly modified the ownership of the PostgreSQL files and all other files on the server to a user called deploy.

So each time I try to run the pg_dump operation it fails with the error:

pg_dump: [archiver (db)] connection to database "dbase" failed:
FATAL: Peer authentication failed for user "postgres"

Here's how I solved it:

I installed and set up another PostgreSQL database server on a new VPS.

Next, I made a backup of the PostgreSQL data directory in the old server:

sudo cp /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bk

And copied the backup into the new server and then replaced PostgreSQL data directory in the new server with it. That is I moved out the PostgreSQL data directory in the new server and copied the PostgreSQL data directory backup of the old one into it. You may need to switch to the PostgreSQL user (sudo -u postgres -i) at some point to perform some operation:

sudo systemctl stop postgresql
sudo mv /home/your-username/main.bk /var/lib/postgresql/12/main
sudo -u postgres -i
sudo mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main.bk2
sudo mv /var/lib/postgresql/12/main.bk /var/lib/postgresql/12/main

Finally, I restarted the PostgreSQL database server in the new server:

sudo systemctl restart postgresql

This time when I tried running the pg_dump operation in the new server, it worked fine.

That's all.

I hope this helps