We are using dedicated DB and DB Backup Server configured for Warm Standby. Uptill now our Server was generating around 3k Wal files per day and backup was catching up. Recently the load on our Production Server has increased and now it is generating around 4k wal files.
Can't we reduce the no.of wal generations by tuning the checkpoint_segments, wal_buffers.
Now we can see alot of wal files in Backup Server waiting to get injested. They are increasing by around 1k wal files per day. On the backup Server the postgress startup process which is responsible for injesting wal files is alway consuing 99.1 to 99.7 %CPU and IO Wait is staying at 0.0 to 0.1 %.
I am not sure how SAS drives works in RAID 10. But i would like to check whether the archives and data directory resides in a same mount point or not. If both resides in a same mount point, then it will take time to read archives from the mount point, and write to the same mount point.
We are using 15k rpm SAS drives configured in Raid 10 on main as well as Backup Servers. My understanding is that drives speed is a main factor on which the restoration speed depend. But these drives seems capable of handling more load than this.
We are not getting any performance issue in our main server. It is the backup which is lagging behind.
If your pg version is >= 9.0, then try to setup the streaming replication. It's an online replication which is not mandatory to transfer these archives to slave machine.
I believe, you already implemented the archive clean up job. If not, implement this.
I will appreciate if some will help me in pointing out the bottle neck and provide solution.
Consult pgsql-general list also. There you may get better solution for your problem.
