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?

[TheOtherBrian1]

It would be a bit awkward, but you could use the http-psql or pg_net extensions to call a server with the ability to initiate pg_dump.

[sobrinho]

You can try pgpodman to call a docker image that would do it for you.