I am using PostgreSQL 17.2, and I would like to use the Incremental Backup feature.
Here are my conf-file settings:
wal_compression = on
wal_log_hints = on
max_wal_size = 4GB
min_wal_size = 80MB
archive_mode = on
archive_command = 'copy "%p" "D:\WAL files\%f"'
restore_command = 'copy "D:\WAL files\%f" "%p"'
summarize_wal = on
checkpoint_timeout = 1h
archive_timeout = 2h
I hope I haven’t missed anything. My problem is that around 100MB of data is inserted into the database daily, with about 8 records per minute. However, every minute, a 16MB WAL file is created. This means that 960MB of WAL files are generated each day, and within a week, the entire space allocated for WAL files will be filled up.
What do you think I should adjust, or what might I have forgotten? Is there something I can do about it? Maybe delete older files? Or create incremental backups more frequently? If I perform an incremental backup, can I delete the previous WAL files afterward?
I would really appreciate any suggestions!
Extended:
I would like to create an incremental file on a daily basis and perform a full backup approximately once a month (combining the incremental files with the initial base backup).
What’s important to me is:
This database runs on another server, about 7000 km away from me. If anything happens, it would take a very long time to transfer the entire backup file to my location (e.g., if 400 GB of data accumulates, I could never transfer that over the internet).
My plan is to create a base backup, transfer it to my location, and write a program that automatically copies the daily incremental backups to my location as well. This way, the process can be automated.
Currently, my database is small because I’m just starting up this server. I’m estimating about 100 MB of new data daily.
I use WAL files only because they are required for the incremental functionality, and for no other purpose.
3
Firstly, if you are getting a new WAL file every minute (and don’t think you have that much activity) check your checkpoint_timeout setting. That defaults to 5 minutes, but may have been changed for you. If you increase this value that might greatly reduce the number of WAL files per day you create.
Secondly, you need to do some calculations and size storage and network accordingly. If you are adding 100MB of data a day, let’s call that 50GB per year. After 5 years that is 250GB and you’re going to have some mechanism in place to deal with taking a new base-backup and transferring it. It will be required when inevitably your local machine has a glitch.
Thirdly, once you are convinced that you have a good base-backup stored safely AND all the WAL you need also stored safely then you can delete any transferred archived WAL files from your primary db server. PostgreSQL will manage its “active” WAL for local recovery in the case of a problem, the archive is just for recovering on a different machine.
Finally, consider a packaged backup tool – pgbackrest or barman or one of the others. They can be helpful if you don’t want to mess about with a lot of the details of postgresql’s configuration settings, and will usually help with compressing the backups/WAL too. Do make sure that your backup tool knows about postgresql 17’s incremental backups though.
2