CAUTION The answer about changing the UNIX password for "postgres" through "$ sudo passwd postgres" is not preferred, and can even be DANGEROUS!
This is why: By default, the UNIX account "postgres" is locked, which means it cannot be logged in using a password. If you use "sudo passwd postgres", the account is immediately unlocked. Worse, if you set the password to something weak, like "postgres", then you are exposed to a great security danger. For example, there are a number of bots out there trying the username/password combo "postgres/postgres" to log into your UNIX system.
What you should do is follow Chris James's answer:
sudo -u postgres psql postgres
# \password postgres
Enter new password:
To explain it a little bit. There are usually two default ways to login to PostgreSQL server:
By running the "psql" command as a UNIX user (so-called IDENT/PEER authentication), e.g.: sudo -u postgres psql
. Note that sudo -u
does NOT unlock the UNIX user.
by TCP/IP connection using PostgreSQL's own managed username/password (so-called TCP authentication) (i.e., NOT the UNIX password).
So you never want to set the password for UNIX account "postgres". Leave it locked as it is by default.
Of course things can change if you configure it differently from the default setting. For example, one could sync the PostgreSQL password with UNIX password and only allow local logins. That would be beyond the scope of this question.
That Postgres configuration looks right
CentOS by default sets the user directory mode to 700 so check if that's actually the case and if you can touch that file using su as the root user
su - postgres -c "touch /home/myusername/backup/testtouch"
If that does work then try to use verbose logging in postgres and check the postgres log for further errors.
Best Answer
Address the underlying cause and the problem will go away (your underlying problem being someone who didn't know what they were doing and didn't read the Postgres manual set
wal_keep_segments
to an insane value):wal_keep_segments
to a sane value.(10 is usually sane. 100 is not unreasonable).