Permission Issue: Creating postgres back as postgres user

Question

I have following postgres backup script, its a shell script and written to run ans postgres user.

But the problem is postgres user doesn't have permission to write these directories. I as a user don't have sudo on these machines but I have changed the directory to has 755 and added to one of the group that has major permission to do read-write-execute. Since postgres user isn't part of the unix user group I guess I am running into this issue.

My goal is to put this in the cron-tab but prior to that I need to get the script running with proper permission:

#!/bin/bash
# location to store backups 
backup_dir="/location/to/dir"
# 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
databases=`psql -l -t | cut -d'|' -f1 | sed -e 's/ //g' -e '/^$/d'`
for i in $databases; do
  if [ "$i" != "template0" ] && [ "$i" != "template1" ]; then
    echo Dumping $i to $backup_dir$i\_$backup_date
    pg_dump -Fc $i > $backup_dir$i\_$backup_date
  fi
done
find $backup_dir -type f -prune -mtime +$number_of_days -exec rm -f {} \;

Justin Paul Paño · Accepted Answer · 2014-02-06 12:25:18Z

1

Before doing this be sure to login as a super user (sudo su) and try executing these:

useradd -G unix postgres (Add postgres user to unix group)
su postgres (Login as postgres user)
mkdir folder (Go to the directory where postgres needs to write files)

***From this line down is my answer to @find-missing-semicolon question

Just to illustrate an example with a shell script, you can capture the password using the read command and put it to a variable. Here I stored the password in password and echoed it afterwards. I hope this helps.

`#!/bin/bash`

`read -s -p "Password: " password`
`echo $password`

edited Feb 6, 2014 at 12:25

answered Feb 5, 2014 at 16:57

Justin Paul Paño

9365 silver badges7 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

add-semi-colons Over a year ago

Paul Pano I figured out a way to do it without actually using postgres user, i can run pg_dump as the database admin but the problem with that pg_dump -Fc -Uga_db_admin mydatabase --password > $backup_dir\_$backup_date shell script will prompt for a password is there away to avoid that.

Collectives™ on Stack Overflow

Permission Issue: Creating postgres back as postgres user

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related