CODEWITHSUNDEEP
postgresql
basante
basante

Reputation: 545

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

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"

Upvotes: 25

Views: 35597

Answers (3)

Promise Preston
Promise Preston

Reputation: 28800

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

Upvotes: 0

Diego Tejada
Diego Tejada

Reputation: 1171

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

Upvotes: 21

Bobo
Bobo

Reputation: 615

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

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

Upvotes: 58

Related Questions