2

Environment:

Postgres 8.4

Debian Squeeze LTS

Recently we switched from a backup solution around hot_backups to using pg_dumpall.

This solution used a pid file as long as the hot_backup was running, once finished, it would remove the pidfile.

For (re)storing the Dump on another server we would use a script to check for the pid, and transfer the Dumpfile if no PIDfile exists.

As the hot_backup was quite easy to spot in case something went wrong (f.e. more than 24h dumptime) Issues which arised were promptly figured out and fixed.

As we now use pg_dumpall i am in the research of how to better determine if the dump is finished or still ongoing.

Currently we use the same PID approach as before.

However i would like to have a query/command/something to check if the dump is still running or not from within postgres.

3

You can see if a dump is running from within PostgreSQL by checking for pg_dump or pg_dumpall as the application_name in pg_stat_activity. It's not perfect though, since the client has to connect to each DB in turn, so there might be a period with no visible connection. I haven't verified whether pg_dumpall maintains its connection throughout, while running the sub pg_dump sessions, or not.

You can use the handy flock command to maintain pid files at the shell level.

Personally, though, I strongly recommend simply rotating your dumps. Dump to a temporary file, and only when the dump is complete replace the old dump with the new one using the mv command. That way there is no window during which the dump file is invalid. If you replace the dump file while it's being read, anything reading the file will continue to read the old dump file it has open and won't care that it's been replaced.

2
  • 1
    OP says he is on PostgreSQL 8.4, so he wouldn't even be able to check application_name in pg_stat_activity -- don't believe that column existed in that system view in 8.4, per the docs. – Josh Kupershmidt Aug 3 '15 at 20:15
  • @JoshKupershmidt Good point. I forget some of the details of ancient releases. That pretty much leaves a pidfile approach (flock or otherwise). – Craig Ringer Aug 3 '15 at 23:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.