How to determine if pg_dumpall is still running

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.

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.

StackExchange Database Administrators Q#108425, answer score: 3