CODEWITHSUNDEEP
postgresqlubuntucron
Uday Modha
Uday Modha

Reputation: 11

automatic backup in postgresql creates the empty backup

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?

Upvotes: 1

Views: 1381

Answers (2)

Valeh Ağayev
Valeh Ağayev

Reputation: 596

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

Upvotes: 1

Laurenz Albe
Laurenz Albe

Reputation: 246568

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.

Upvotes: 3

Related Questions