Postgresql – Can’t backup a Postgres database: FATAL: Peer authentication failed for user “postgres”

authenticationbackupconfigurationpostgresql

I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3.

Unfortunately, when I try to run pg_dump backups locally on the server itself, I get:

pg_dump --username=postgres -W omega | gzip > omega.10.10.13.gz
pg_dump: [archiver (db)] connection to database "omega" failed:  
FATAL:  Peer authentication failed for user "postgres"

Previously I'd had no password at all for my database, but to try to get around this I actually gave the postgres user a password. Still no dice, peer authentication fails every time.

Here's the settings in my pg_hba.conf file… please tell me what I can do to fix this. Really want to run some backups on my database.

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5 
#host    replication     postgres        ::1/128                 md5

host    all             all             70.89.205.250/24        md5 
host    all             all             23.21.112.163/24        md5 
host    all             all             24.188.1.163/24         md5 

Best Answer

In pg_hba.conf, change this line:

local   all             all                                     peer

to:

local   all             all                                     password

Then you can log in locally using a password.