Search Postgresql Archives

Re: Automating backup

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux