Debian - Schedule a SQL query with crontab

Question

I wan't to schedule a SQL query in a PostgreSQL database with a crontab.

In terminal, in root, when I run:

psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from terminal');"

It asking me a password. So I add it:

PGPASSWORD="mypassword" psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from terminal and password');"

And when I run the same in the crontab, It doesn't succed:

0 12 * * * PGPASSWORD="mypassword" psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from crontab and password');"

The crontab log return me

nano var/log/syslog

(root) CMD (PGPASSWORD="mypassword" psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from crontab and password');")
CRON[15504]: (CRON) info (No MTA installed, discarding output)
CRON[15677]: (root) CMD (command -v debian-sa1 > /dev/null && debian-sa1 1 1)
crontab[15862]: (root) BEGIN EDIT (root)
systemd[1]: session-60285.scope: Succeeded.
crontab[15862]: (root) END EDIT (root)

Why the crontab job doesn't execute the pgsql command ? How can I schedule a SQL command with crontab ?

Answer 1

Some implementations of cron (I think including the one that comes with Debian) lets you set the environment variables on previous lines:

PGPASSWORD="mypassword" 
0 12 * * * psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from crontab and password');"

---

Does this have to be a cron job? Since you're running Debian, you could make use of systemd's timers.

To implement this in systemd you would write a timer unit that schedules the job and a service unit that runs the job. The service unit can have environment variables set.

#/etc/systemd/system/regularquery.timer
[Unit]
Description="Timer to run SQL query"
After=postgresql.service

[Timer]
OnCalendar=*-*-* 12:00:00
Unit=regularquery.service

[Install]
WantedBy=multi-user.target

#/etc/systemd/system/regularquery.service
[Unit]
Description="SQL Query

[Service]
Type=oneshot
User=postgres
Environment=PGPASSWORD="mypassword"
ExecStart=/usr/bin/psql -U postgres -d my_database -h localhost -p 5432 -c "INSERT INTO schema.table (name) VALUES ('Insert with a command from terminal');"

Try it out with sudo systemctl start regularquery.service and see if it connects fine. You can monitor the output with journalctl -u regularquery.service. When you're happy, get the thing scheduled with sudo systemctl enable --now regularquery.timer

---

Alternatively, I found this in man psql:

       -w
       --no-password
           Never issue a password prompt. If the server requires password
           authentication and a password is not available from other sources
           such as a .pgpass file, the connection attempt will fail. This
           option can be useful in batch jobs and scripts where no user is
           present to enter a password.

This suggests that ~/.pgpass might be a good alternative to an environment variable. It also suggests --no-password is a good switch for a non-user session (i.e. crontab or systemd) as it will NEVER interactively pause for a prompt.