CODEWITHSUNDEEP
bashpostgresqlautomationbackup
Mruf
Mruf

Reputation: 808

Postgres backup script on Ubuntu + cron

i try to get an auto backup script running.

The script works perfectly, if i run it by typing "bash backup.su" into the terminal. For every query and operation I have to input the password (database user), but it works fine.

But as soon as I don't use the terminal (eg via cron), it doesn't. It just prints out my logfile without any information.

I think it has something to do with user rights

Terminal output:

Making backup directory in /scripts/2013-09-26-daily 

Performing schema-only backups
------------------------------------------
Password for user backupuser: <- Query getting all databases with certain string in name
The following databases were matched for schema-only backup:

Performing full backups
------------------------------------------
Password for user backupuser: <- Query getting all databases
Plain backup of DB1
Password:
Plain backup of DB2
Password:
Plain backup of AndSoOn
Password:
All database backups complete!

Cron Log:

Making backup directory in /scripts/2013-09-26-daily/

Performing schema-only backups
------------------------------------------
The following databases were matched for schema-only backup:

Performing full backups
------------------------------------------

All database backups complete!

As you can see there seems to be no query executed. Right now i'm using a special backup user, but it also doesn't work with postgres. (With the postgres user he also ask for a password... but an empty one stops the script)

Does anybody have a clue for me? As i said it works perfectly manually, bot doesn't with cron.

Greetings

Martin

Upvotes: 1

Views: 4454

Answers (2)

n2studio
n2studio

Reputation: 306

You can create a .pgpass in the user's home directory that you want to use for the cron job. The .pgpass format should be:

hostname:port:database:username:password

In practice, I found that you need to use a wildcard ("*") for the hostname, port, and database for cron jobs to work. For example, if you are running the cron job as the postgres user and the user home directory is /home/postgres, then create a file called ".pgpass" in /home/postgres with the following content:

*:*:*:postgres:password

Your cron jobs running under the postgres user should then work without needing a password. Official documentation for this password file is here: http://www.postgresql.org/docs/8.4/static/libpq-pgpass.html

Upvotes: 3

krisku
krisku

Reputation: 3993

When running interactively you have a tty, but when running from cron there is no tty attached, i.e. your script has no stdin to read from (that would block the script while waiting for input that can never be entered from anywhere).

You should make your script runnable without a password, e.g. run it under a specific user account and configure postgres to allow that user to connect without a password (i.e. edit pg_hba.conf).

Upvotes: 1

Related Questions