Re: How to implement backup protocol

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