I have the following setup:
- OS: CentOS
- PostgreSQL 12
- Netbackup 8
What I want to achieve:
Configure continuous archiving with the possibility of Point-in-Time Recovery
My current idea:
- Generate a
pg_basebackup
every day at midnight and put the generated files in a folder outside of the data folder - Delete the files older than the created backup from the
pg_wal
folder - Create a Netbackup of the VM
With these three steps I should be able to make a PITR every day starting from the last base backup until the upcoming base backup, and my pg_wal
folder will never grow too much. Also the usage of the internal archiving is not required.
Somehow this sounds very simple and I would like to ask if I am missing something here?
Best Answer
There is one crucial thing wrong here: you never, ever delete files from
pg_wal
. You need to configurearchive_mode = on
and setarchive_command
so that PostgreSQL archives transaction log files when they have been completed.Much simpler than creating a
pg_basebackup
on a local disk and then backing that up would be to backup the data directory directly. You have to get PostgreSQL into backup mode first (pg_start_backup
) and end backup mode once you are done backing up files (pg_stop_backup
). See the documentation for more.