1

I want to automate backup of PostgreSQL database using crontab in UNIX. I have tried but it will create 0 bytes backup.

My crontab entry is:

24 * * * *  /home/desktop/myscript.sh

and my sh file contains the following code:

pg_dump -U PostgreSQL -d test > b.backup

It will create the file but the file is empty. Is there any solution? Is there any way to solve this question?

tripleee
  • 175,061
  • 34
  • 275
  • 318
Uday Modha
  • 11
  • 2
  • Check your system log file for error messages from `cron`. Probably your `cron` job needs a password or some other way to authenticate. – tripleee Jun 26 '20 at 14:10
  • Possible duplicate of https://stackoverflow.com/questions/22743548/cronjob-not-running – tripleee Jun 26 '20 at 14:11
  • The proposed duplicate contains a large number of troubleshooting steps which you don't seem to have performed. Please [edit] your question to show what debugging you have done (or probably simply delete it when you manage to solve the probdem on your own while troubleshooting). – tripleee Jun 26 '20 at 14:20
  • do you have any social media or whatsapp contact number so that i can direclty asked to you – Uday Modha Jun 26 '20 at 14:23
  • The primary purpose of Stack Overflow is to build a knowledge base so you can solve your problem yourself based on existing answers. Again, all I can do in private is spoon-feed you the troubleshooting steps one by one. – tripleee Jun 26 '20 at 14:25

2 Answers2

2

Don't assume that any environment variables are set in a cron job; be explicit:

/full/path/to/pg_dump -U postgres -d test > /full/path/to/b.backup

Look for mail in your inbox for failure reports from cron.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • i didn't understand what are you saying ? – Uday Modha Jun 26 '20 at 14:33
  • I am talking about fixing your shell script so that it works. – Laurenz Albe Jun 26 '20 at 14:55
  • Should I write sudo before pg_dump – Uday Modha Jun 26 '20 at 15:12
  • Did I say anything about `sudo`? – Laurenz Albe Jun 26 '20 at 15:13
  • What is the full path of pg_dump? It should be anything? /home/desktop pg_dump...... – Uday Modha Jun 26 '20 at 15:16
  • No offense meant, but I cannot help you, since I don't know your machine and you don't seem to have basic knowledge about computers. You will need to find someone who does, can sit down with you and lead you through the first steps. – Laurenz Albe Jun 26 '20 at 15:20
  • If it's in `/usr/bin/pg_dump` it will probably work without any amendments to the `PATH`. If it's somewehere less standard like `/opt/local/postgres/pg_dump` you will need to use the explicit full path, or update your script to amend the `PATH` variable so it's visible. At the prompt, `type pg_dump` will print the full path to the binary. – tripleee Jun 26 '20 at 15:40
1

You must specify full path to pg_dump

#!/bin/bash
BKPDATE=$(date +%d.%m.%Y-%H:%M:%S)
cd /var/lib/pgsql/12/backups
/usr/pgsql-12/bin/pg_dump -Fc dl_db > DBNAME_$BKPDATE.dmp --verbose 2> LOG_$BKPDATE.log

or you must add PostgreSQL's bin directory to the path like below:

vi /var/lib/pgsql/.pgsql_profile

   export PATH=$PATH:/usr/pgsql-12/bin
Valeh Ağayev
  • 556
  • 4
  • 12