From: | Neil Dugan <postgres(at)butterflystitches(dot)com(dot)au> |
---|---|
To: | Richard Sydney-Smith <richard(at)ibisau(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Automating backup |
Date: | 2006-02-07 00:45:47 |
Message-ID: | 43E7EDBB.8000706@butterflystitches.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Richard Sydney-Smith wrote:
> Hi Doug.
>
> 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 put all the effort into a database design that checks and ensures
> everything except that a backup copy exists!
>
> Essentially I need access to a database record that gives the timestamp
> for the last backup.
> Of course pgdump/vacuum could maintain such timestamps themselves. Is
> that possible? If not then a proc in the database that calls
> pgdump/vacuum and records the event is needed to give me peace of mind.
>
> regards
>
> Richard
>
I have just implemented a backup scheme on my Linux box.
/etc/cron.daily/postgres
su postgres /var/lib/pgsql/maintain
/var/lib/pgsql/maintain
vacuumdb --all --full
pg_dumpall --clean | gzip > /var/lib/pgsql/backups/pg_`date --iso`.gz
# remove any backups older than 30 days (this is a little dangerous)
tmpwatch 720 /var/lib/pgsql/backups
I created a directory
#> mkdir /var/lib/pgsql/backups
I suppose you could get the /var/lib/pgsql/maintain script to generate a
sql statement in a temporary file (e.g. /tmp/setbackupdate.sql) then use
psql to then update a record in your database with a command like (psql
-c /tmp/setbackupdate.sql)
Regards Neil.
> Doug McNaught wrote:
>
>>Richard Sydney-Smith <richard(at)ibisau(dot)com> writes:
>>
>>
>>
>>> pch := pchar('pg_dump -C -h '+host+' -U '+usr+' -p '+pswd+ ' -f
>>>'+bckup_path+' '+dbase);
>>>
>>>to postgres.
>>>
>>>as the operator is obviously logged in how do I
>>>(1) trap their user id
>>>(2) Send the call to pg_dump without knowing their password?
>>>
>>>I expect this is a well worn route and am hoping not to have to
>>>reinvent a wheel.
>>>
>>>
>>
>>I don't think it's "well-worn" at all--everyone I've ever heard of
>>runs pg_dump from a cron script.
>>
>>Why not have a shell script run by the operator that runs pg_dump and
>>then calls psql to insert the log record (assuming the dump succeeds)?
>>Putting the logic inside of the database doesn't seem to buy you
>>anything AFAICS.
>>
>>-Doug
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 4: Have you searched our list archives?
>>
>> http://archives.postgresql.org
>>
>>
>>
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2006-02-07 01:01:41 | Re: Any way to make PG driver obey PrintWarn? |
Previous Message | Tyler MacDonald | 2006-02-06 23:49:36 | Re: Should I use PL/PGSQL or Perl/PGSQL? |