Re: How to implement backup protocol

[Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net>]

On Tue, Nov 28, 2006 at 06:01:43PM +0200, Andrus wrote:

> 5. Server has *only* 5432 port open.
>
> pg_read_file() can read only text files and is restricted only to 
> superusers.
> 
> How to add a function pg_read_backup()  to Postgres which creates and 
> returns backup file with download speed ?

You could use an *un*trusted procedural language to create a
function to binary-read the backup from disk and return it
as a bytea field. Not sure how efficient that is, though.

You could then simply do

 select get_backup();

If you allow for parameters you could make it return certain
backups based on, perhaps, timestamp of creation.

 select list_available_backups();

might complete the suite of tools.

One could then always use some hashing tools (mhash with PG
bindings comes to mind) to verify whether a backup has arrived safely:

on local machine: ripemd160(backupfile)

 select yhash.ripemd160(get_backup()) = <local hash>;

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346