I'm trying to backup my Postgres DB to another server but I keep getting denied access.

my pg_hba.conf file looks like this:

# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
# 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               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            md5
#host    replication     postgres        ::1/128                 md5

If I change the postgres setting from PEER to TRUST, i can backup the code but the web site can no longer access the database (something about SSL error, which I know isn't the issue).

When I leave it as is, PEER, the server works - php can access the db - but I get error while trying to run the backup:

root> pg_dump -U postgres tablename > test.sql

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

PHP Apache Web site uses appropriate username and password.

Backup script looks like this: pg_dump -U postgres tablename > test.sql

how can I satisfy both the web site and the backup script so I can run both?

Entire script attempted is this: ssh SERVER "pg_dump -U postgres tablename| gzip -c" > /backup/sqlbackup.sql.gz

taken from Server Fault: ssh remote command-fu


If your PHP Apache web site works with peer, keep this, and let's deal with it.

1. First Method :

As authentication is peer based, you have to specify the hostname (usually localhost) using -h option :

ssh server "pg_dump -h localhost -U postgres | gzip -c" >/backup/sqlbackup.sql.gz

The main issue here is that you will be prompted for the postgres user password. It is a problem for backups automation .

You can refer to this to avoid to be prompted for the password. But i will describe :

  1. Create an hidden file named .pgpass in $HOME
  2. Perform a chmod 600 .pgpass
  3. Edit file .pgpass and add this line : localhost:5432:postgres:postgres:password

2. Second Method :

You could add a specific role for backups and trust it.

Create the backup role in Postgres :


Edit file pg_hba.conf, add role backup and trust it :

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

Restart postgresql

Then you should be able to run backup with the following :

ssh server "pg_dump -U backup postgres | gzip -c" >/backup/sqlbackup.sql.gz
  • If the -h localhost is given, pg_dump will prompt for a password for the user postgres. But if this user relies on a peer connection, it actually doesn't have a password. How would you bypass this?
    – s.k
    Apr 15 '20 at 8:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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