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@ibisau.com> 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
>
>
>
>
>
