1

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:password@127.0.0.1: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.

padjee
  • 125
  • 2
  • 12
  • 1
    A difference between run by command and run by crontab is the user who does the action. Probably the crontab doesn't have permission to access your database. – rodrigo Oct 06 '22 at 04:33
  • I think so. How do I add the permission ? @rodrigo – padjee Oct 06 '22 at 04:51
  • I would use `-U ` to specify the Postgres database user you wish to connect as. Preferably a user that has the necessary permissions in the database to dump all the objects and data. – Adrian Klaver Oct 06 '22 at 05:01
  • Wouldn't it require password when run ? @Adrian Klaver – padjee Oct 06 '22 at 05:32
  • 1
    1) Depends on how your `pg_hba.conf` is configured. 2) If it requires a password then either [.pgpass](https://www.postgresql.org/docs/current/libpq-pgpass.html), [service file](https://www.postgresql.org/docs/current/libpq-pgservice.html) or [PGPASSWORD](https://www.postgresql.org/docs/current/libpq-envars.html) environment variable could be used. – Adrian Klaver Oct 06 '22 at 14:50

1 Answers1

1

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
J_H
  • 17,926
  • 4
  • 24
  • 44
  • I run pg_dump using user `postgres`. And I have confirmed the 0 kb files are owned by **postgres** by using command `ls -l [filename]`. In fact, all files (the zero and non-zero size) on dir `/backup-nfs` are owned by postgres @J_H – padjee Oct 06 '22 at 04:50