Is it possible to schedule pg_dump using pg_cron?

[ploef]

Hi,

Would it be possible to schedule pg_dump with a pg_cron schedule? I tried something like this:

``
SELECT cron.schedule(
'one_time_pg_dump', -- job name
'*/1 * * * *',
$$pg_dump -U myUser -d my_db -f /tmp/dump_$(date +%Y%m%d%H%M%S).sql$$
);

``

But the execution returns the message:

ERROR: syntax error at or near "pg_dump"
LINE 1: pg_dump -U myUser -d my_db -f /tmp/dump_$(date +%Y%m%...
^

[marcoslot]

Not directly, since it cannot run shell commands. You'd need another extension that lets you run pg_dump via a SQL query.

[ploef]

Thanks for the quick response!
Ah that's to bad. I was using pgAgent for that but that's actually way too heavy for what I need. Do you have a suggestion for an extension that I could best use for this?