0

i'm having troubles trying to make script to auto backup my db of my django app.

This is how i create my db for my app:

sudo -u postgres psql  
CREATE DATABASE dapp;  
CREATE USER backupu WITH PASSWORD 'pass123';  
ALTER ROLE backupu SET client_encoding TO 'utf8';  
ALTER ROLE backupu SET default_transaction_isolation TO 'read committed';  
ALTER ROLE backupu SET timezone TO 'UTC';  
GRANT ALL PRIVILEGES ON DATABASE dapp TO backupu;  
\q

And this is my backup script, backup.sh with chmod 777:

export PGUSER='backupu'  
export PGPASSWORD='pass123'  
TODAY=`date +%Y-%m-%d`  
TIME_NOW=`date +%H:%M`  
ARCH_RESP=$TODAY-$TIME_NOW  
pg_dump dentalapp > /home/backupu/backup/backup_$ARCH_RESP.sql  
find /home/backupu/backup/ -name '*.sql' -mtime +2 -exec rm -f {} \;  
unset PGUSER  
unset PGPASSWORD

But when i run it, i get this error:

pg_dump: [archiver (db)] query failed: ERROR: permission denied for relation django_migrations pg_dump: [archiver (db)] query was: LOCK TABLE public.django_migrations IN ACCESS SHARE MODE

and:

connection to database "dapp" failed: FATAL: Peer"authentication failed for user "backupu"

I tried adding this line to my pg_hba.conf

local   all     backupu     md5

But the error persist, maybe some permissions are missing or need a more step when i create my db. I already checked some other post here in stackoverflow but also without success, Can you help me?

i'm running a local server with ubuntu 14.04, nginx, gunicorn and postgresql 9.3

Vao Tsun
  • 47,234
  • 13
  • 100
  • 132
Kevin Ramirez Zavalza
  • 1,629
  • 1
  • 22
  • 34
  • what I don't understand is why you are setting the transaction isolation level. Shouldn't transaction isolation level be serialize for pg_dump (and isn't that the default)? – e4c5 Aug 15 '16 at 15:31
  • Possible duplicate of [Permission denied for relation](http://stackoverflow.com/questions/15520361/permission-denied-for-relation) – Tomasz Jakub Rup Aug 15 '16 at 15:32

1 Answers1

0

What happens is that you are using a user that has no permission over the django_migrations table.

Use the same data base user that the webapp uses.

Eddy del Valle
  • 197
  • 1
  • 3