Run a PostgreSQL Query with Cron
To run a SQL query on a schedule, call psql from cron with -c for an inline query or -f for a .sql file. Store the password in ~/.pgpass so nothing sensitive sits in the crontab.
The crontab line
Run crontab -e and add (top of every hour):
0 * * * * /usr/bin/psql -U myuser -d mydb -c "REFRESH MATERIALIZED VIEW stats;" >> /var/log/psql-cron.log 2>&1
Run a .sql file instead
Use -f for a saved script: /usr/bin/psql -U myuser -d mydb -f /home/user/jobs/report.sql.
Pass the password safely
Create ~/.pgpass with a line host:port:db:user:password and chmod 600 ~/.pgpass. psql reads it automatically, so no password appears in the crontab or process list.
FAQ
How do I run a SQL query on a schedule with cron?
0 * * * * psql -U user -d db -c "..." runs an inline query hourly. Use -f file.sql to run a saved script instead.
How does psql get its password in cron?
Use a ~/.pgpass file (host:port:db:user:password) with chmod 600. psql reads it automatically with no password in the crontab.
Should I use -c or -f with psql in cron?
Use -c for a short inline query and -f /path/script.sql for anything longer or reused. Both work the same from cron.