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