I want to create a postgres backup script but I don't want to use the postgres user because the unix system I am in has few restrictions. What I want to do is run this script as a regular user of the unix system (network) on a crontab and use database admin account in the script.
Just to make it clear in the unix system (network) I have user foo
, now we have postgres
user who is default postgres user, and then for the database we have a database admin user my_db_admin
. I script to execute as network user foo
using my_db_admin
user and credentials in the script.
This is what I started with but the problem is when I execute the script it gives an error saying password was not provided.
#!/bin/bash
export PASSWORD="MyPassword"
backup_dir="/BACKUP/LOCATION"
# name of the backup file has the date
backup_date=`date +%d-%m-%Y`
# only keep the backup for 30 days (maintain low storage)
number_of_days=30
pg_dump -Fc -Umy_db_admin MyDatabase -w > $backup_dir\_$backup_date
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;
Here is the error:
pg_dump: [archiver (db)] connection to database "MyDatabase" failed: fe_sendauth: no password supplied
Use a .pgpass
file to save a password for pg_dump
, or set the PGPASSWORD
environment variable. Or modify pg_hba.conf
to allow connection without a password using peer
authentication.
This is covered in the manual:
and a search for the error message would've found relevant information.