Thanks Doug. Think hacking the source may be the way to go. I will ask
the Postgres bosses if this the idea is acceptable.
We are only going to store two data items somewhere. One key-timestamp
for each of autovacuum and pgdump
Doug McNaught wrote:
Richard Sydney-Smith <richard@xxxxxxxxxx> writes:
Hi Doug.
When the application runs I want it to KNOW that the user is regularly
backing up the data. Many users are haphazard in their approach until
the machine fails and then they expect to be pulled from the poo.
Done it too many times. I now will get the application to enforce an
additional integrity check. It must be backed up or else! Seems futile
to pull all the effort into a database design that checks and ensures
everything except that a backup copy exists!
Very good points.
Running in a cron job is great if the sysadmin is doing their job but
how can I tell? I want access to a database record that gives me the
timestamp for the last backup.
You could certainly include a standard script that performs your
backup and then inserts into your log table, and have the application
installer create a cronjob that calls that script. The operator could
also run it by hand if necessary.
Does postgres perhaps already have a timestamp for the last time
vacuum was run and the last time a backup was taken. Could
pgdump/vacuum maintain such a record?
Well, anything's possible if you're willing to hack the source code. :)
If you're running autovacuum, you can tell it to log what it does to a
separate logfile, so there'll be log entries when it vacuums tables.
Autovacuum is probably the best way to go for applications like yours
anyway (especially with 8.1, where it's built-in and started
automatically).
As for pg_dump, I'm not aware that it logs anything. If you turned on
full query logging on the server, you'd see the queries that pg_dump
executes, but that would give you pretty big logfiles...
-Doug
