Re: How to implement backup protocol

[Richard Huxton <dev(at)archonet(dot)com>]

Andrus wrote:
> So I think that 4.5 hours which requires to create backup is because pg_dump 
> download the whole database  (1 GB) in uncompressed format over slow 
> internet connection.
> Compression level does not affect to this almost at all.

Might well be latency issues too.

> I think I can create backup copy fast in server computer but how to send it 
> to backup computer?

Use scp. Open port 22 and allow only connections from the backup machine 
with a specified user (e.g. "pgbackup").

Alternatively, you might try dumping in a text-format and using rsync to 
 transfer changes.

> 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 ?
> This problably requires implementing some file download protocol.

Just don't try and do it within PG - use the tools the system provides.

> I expect that full database backup created using pd_dump does not have never 
> have any problems on restore.

It's entirely possible to cause problems by restoring as the wrong user 
or with a different character-set / locale.

--
  Richard Huxton
  Archonet Ltd