> The weekly backup of the larger of the two databases produces a file that > is about 20GB and takes about an hour and 15 minutes. I then compress it > down to about 4 GB, which takes another hour. However, because that's a > separate task, it doesn't impact the database server as much. (I suspect > all that I/O slows things down a bit, but I haven't noticed any > significant effect in my transaction time reports. That task is run during > the slowest 4 hour period of the week, though). My environment is a bit different. For safety, I need to create backups to separate computer over over internet. 1. Backup computer has client category internet connection (ADSL, 600 KBit/s download speed). 2. Query "SELECT sum( relpages * 8/1000) FROM pg_class" returns 1302 for a database restored from backup. So my data size seems to be approx 1 GB only. 3. Backup file size is 70 M 4. Backup client has all ports closed. 5. Server has *only* 5432 port open. 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. I think I can create backup copy fast in server computer but how to send it to backup computer? 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. > BTW, if you've never actually tested your recovery capabilities, can you > be sure they work? > I did a full-blown test in February or March and found a few loose ends. > And when we had to do the real thing in May (due to a power supply > failure), there were STILL a few loose ends, but we were back online > within 12 hours of when I started the recovery process, and half of that > time was spent completing the setup of the 'backup' server, which I had > been rebuilding. I'm working to lower that downtime and will be doing > another full-blown test in January or February. I expect that full database backup created using pd_dump does not have never have any problems on restore. Andrus.
