I have a database I would like to backup every hour. For a given hour,
if the database has not been modified since the last backup, I'd like to
skip this backup.

By 'modified', I mean any DML, DDL, grants or revokes that touch the
database, but not selects. If this is a reasonable concept, (how) can
one tell when a database was last modified?
--
KM

KM wrote:
This is not easy, because the database is also "modified" when you do
nothing at all, for example if there is an automatic checkpoint after
checkpoint_timeout has passed.

I recommend that you use normal online backup as described in
http://www.postgresql.org/docs/8.3/s...archiving.html

You would make a base backup every once in a while and backup the
archived WAL (transaction log) segments every hour. That will minimize
the amount of data you have to backup frequently, and in addition
you can recover to any point of time, not only to the full hours.

How frequently you make a base backup should among other things
depend on the amount of activity - if there is a lot of activity, base backups
should be more often, because otherwise recovery will take painfully long.

Hint: Use archive_timeout if there is little activity on your system, but
you still want WAL files to be archived regularly.

Yours,
Laurenz Albe