2006/11/27, John S.Abraham <john.abraham@sysbuild.net>:
>
> Tommaso:
>
> Why not try pg_dumpall, instead?
>
> The file that this command creates allows a complete regeneration of the
> database cluster, including users and user permissions. Even better, its
> already written and you can schedule it using the UNIX/LINUX command 'cron'.
Because, for instance, calling pg_dumpall has a number of shortcomings:
1.) access rights
You have to have a shell/ftp/other account on the machine or make the
network admin give you some other sort of network access (samba, nfs)
so that you can work with backup files.
You also have to be allowed to define cron tasks and have a
potentially large disk space quota.
2.) event based dump
cron is a nice way to make sure a backup gets done, but sometimes
backups make sense in relation to critical events in the system: e.g.
before occasional massive data imports or updates.
In those circumstances, having, say, a special stored procedure which
backs up the whole database and returns the content to the caller over
the network would enable a programmer building an application in *any*
language to add a manual backup feature which the users could then use
at will.
3.) Misc. nuisances
Aside for backup/restore functionality, a user communicates with the
database exclusively with the connection string. Making sure that your
application (even one which isn't multiplatform) will find the pg_*
tools is an additional issue you have to think of and resolve, instead
of sending a request directly to the database server (calling a backup
procedure), like you do generally when you need something from the
RDBMS server.
Cheers,
t.n.a.