Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 
share|improve this question
2  
To pass peer authentication you have to be logged-in locally with the account you're trying to login in Postgres –  Milen A. Radev Oct 10 '13 at 21:32

2 Answers 2

Either run the script as system user postgres - who has a corresponding db user with peer authentication set up, according to your pg_hba.conf file. Like @Milen already hinted in his comment. Or set up password-less access for your system user, preferably with a .pgpass file.

More details in this closely related answer:
PostgreSQL Scheduled Database Backup.

Or this comprehensive answer about password-less access on SO.

share|improve this 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.