Re: How to implement backup protocol

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