25

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"

4
  • 7
    pg_dump -U postgres -h localhost dbase -f dbase.sql Commented Feb 10, 2016 at 12:10
  • 1
    "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. Commented Feb 10, 2016 at 14:38
  • @wildplasser summarized all the things. Commented May 14, 2018 at 21:53
  • In windows C:\Users\Amal>pg_dump -U postgres bank > D:\pg_dump\dbase.sql Commented Sep 29, 2021 at 16:03

3 Answers 3

58

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

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

Sign up to request clarification or add additional context in comments.

1 Comment

Saved my life. I've always wanted a good solution, better than psql "dbname='<database_name>' user='<user_name>' password='<user_password>' host='localhost'" -f "<path_to_query_in_sql>"
21

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

Comments

0

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.