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:
# DO NOT DISABLE! # 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 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
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