Re: Automating backup

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
>
>
>
>
>