Automating backup
I would like each database to encapsulate its own backup procedure.
Each database contains a table (fsyslog) which has a record of each
backup taken:
<rowid><Operator><timestamp><destination path>
At the application level I presently
a) check for time since the last backup
b) if older than <x> hrs
(i) perform a vacuum analyze
(ii) force a call to dbbackup
(iii) add a new record into fsyslog
I would like to add a procedure to the database that does the backup and
thus ensure that all applications use the same methodology.
The procedure would take only the backup path as a parameter.
presently I send the command :
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.
thanks very much as always
Richard
Richard Sydney-Smith <richard@ibisau.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
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Doug. <br>
<br>
Many users are haphazard in their approach until
the machine fails and then they expect to be pulled from the poo.<br>
<br>
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!<br>
<br>
Essentially I need access to a database record that gives the
timestamp for the last backup.<br>
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.<br>
<br>
regards<br>
<br>
Richard<br>
<br>
Doug McNaught wrote:
<blockquote cite="mid874q3g20bh.fsf@asmodeus.mcnaught.org" type="cite">
<pre wrap="">Richard Sydney-Smith <a class="moz-txt-link-rfc2396E" href="mailto:richard@ibisau.com"><richard@ibisau.com></a> writes:
</pre>
<blockquote type="cite">
<pre wrap=""> 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.
</pre>
</blockquote>
<pre wrap=""><!---->
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?
<a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a>
</pre>
</blockquote>
</body>
</html>
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:
Show quoted text
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
Import Notes
Reply to msg id not found: 87wtg9ylb1.fsf@asmodeus.mcnaught.org
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.
Show quoted text
Doug McNaught wrote:
Richard Sydney-Smith <richard@ibisau.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?