CODEWITHSUNDEEP
linuxpostgresqlcronpg-dump
padjee
padjee

Reputation: 265

Zero bytes when backup using pg_dump and pg_dumpall using CRONTAB

I want to backup using crontab with pg_dump. But the file generated is always 0 kb. These are the commands I have used :

* * * * * /usr/lib/postgresql/12/bin/pg_dump -h 127.0.0.1 -Fc db_kp -f /backup-nfs/dump_dbkp_TEST1`date +\%d-\%m-\%y`.sql  
* * * * * /usr/lib/postgresql/12/bin/pg_dump -h 127.0.0.1 -Fc db_kp > /backup-nfs/dump_dbkp_TEST2`date +\%d-\%m-\%y`.sql 
* * * * * /usr/lib/postgresql/12/bin/pg_dump -h 10.100.8.43 -Fc db_kp > /backup-nfs/DMP_dbkp_TEST3`date +\%d-\%m-\%y`.sql 
* * * * * /usr/lib/postgresql/12/bin/pg_dump -h 10.100.8.43 -Fc db_kp -f /backup-nfs/DMP_dbkp_TEST4`date +\%d-\%m-\%y`.sql  
* * * * * pg_dump -h 10.100.8.43 -Fc db_kp -f /backup-nfs/DMP_dbkp_TEST5`date +\%d-\%m-\%y`.bak
* * * * * pg_dump -h 10.100.8.43 -Fc db_kp > /backup-nfs/DMP_dbkp_TEST6`date +\%d-\%m-\%y`.bak

All fail !

I can do just fine without crontab : pg_dump -h 127.0.0.1 -Fc db_kp -f /backup-nfs/dump_dbkp_TESTdate +%d-%m-%y.sql

And this happens with pg_dumpall too.

pg_dump and pg_dumpall are on this directory, I have made sure : /usr/lib/postgresql/12/bin/

What is actually going on?

Help please.

thanks

UPDATE

Finally I solved it. Essentially add this pg_dump --dbname=postgresql://username:[email protected]:5432/mydatabase I read it from here.

Here is my final crontab command :

* * * * * /usr/lib/postgresql/12/bin/pg_dump -h 10.100.8.43 -Fc --dbname=postgresql://[your user]:[password]@10.100.8.43:5432/db_kpx -f /backup-nfs/DMP_TEST`date +\%d-\%m-\%y`.sql

Of course you have to adjust * * * * * according to your need.

Upvotes: 2

Views: 892

Answers (1)

J_H
J_H

Reputation: 20450

The zero-byte file indicates that > redirection is working properly, and that the crontab user has write permission in that directory. Verify that the owner of the zero-byte file is identical to your interactive UID when you tested pg_dump and saw success.

The output is zero bytes because nothing appeared on stdout. But I bet some diagnostic was output to stderr. If your crontab had a MAILTO pointed at your inbox, you would have seen that diagnostic. Consult $ man 5 crontab and note the part that starts

If MAILTO is defined ...

Test sending mail interactively from the command line, to verify mail is correctly configured on your cron server.

In addition to logging stdout with cmd > out.log, you might want to capture stderr:

cmd  > out.log  2> err.log

Upvotes: 1

Related Questions