Good day Team.
I would like to have your insights regarding my above-mentioned inquiry.
I'm currently assigned to perform a DBA task and have a look at the incremental backup routine using WAL archives (we're currently using Postgresql for Windows) which will be maintained as a daily backup.
The objective of the daily backup is to have a small file/files of new data or changes in the database that can be played or resumed using the related (nearest) basebackup and afterwards it can be kept and archived for a long time.
Recently, I performed a test on one of the servers and found out that within 1 hour, the overall wal files is around 1GB already as the main DB is just 900+MB if backup using pg_dump.
What would be the best approach for daily backups? We have 2 systems that are 9GB in size already (very heavy to store on a daily basis).
Looking forward to your best recommendation.
Thank you.