My goal is to create ERP system which creates backups without any
attendance.
I don'nt know how to automate this 100% and havent found any such sample.
I have a three part approach to backups, all implemented via cron (on a Linux server):
1. On a daily basis I back up the key tables using pgdump.
2. On a weekly basis I also backup the two 'productiion' databases using pgdump.
3. Also on a weekly basis, I do a full backup (dumpall) of the entire PostgreSQL database.
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).
A 'restore' of that database on a different server takes somewhere between 4 and 6 hours.
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.
