0

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:

  1. Generate a pg_basebackup every day at midnight and put the generated files in a folder outside of the data folder
  2. Delete the files older than the created backup from the pg_wal folder
  3. 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?

1 Answer 1

0

There is one crucial thing wrong here: you never, ever delete files from pg_wal. You need to configure archive_mode = on and set archive_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.

5
  • Hi, but when I put it in the backup mode, all connections will be lost, right? pg_basebackup keeps the connections. Another question is, why can I not delete wal files which i won't neet anymore? I could archive the files by myself before I delete them manually or the pg_basebackup will do this and keep only the files after the backup. Mar 9, 2021 at 7:11
  • I don't know from where you got these ideas. No connection will be terminated by starting backup mode. The database will keep working as before. You cannot delete files in pg_wal because PostgreSQL does that. Don't mess with the database files if you want to keep your database working. It is not you that needs the files, it is the database. Mar 9, 2021 at 7:32
  • Alright then I probably misunderstood that part. In general that means, I can keep my idea but for a working PITR I will need to backup the data folder and also the folder where the wal files are located after archiving. My thoughts were, to keep as less as possible files on the VM and create one consistent backup every day (pg_basebackup) so I would only need to keep the wal files newer than the backup created on the VM itself and backup the other files with my netbackup. So nothing will be actually be really deleted. Mar 9, 2021 at 10:55
  • That sounds right. But I would keep more than one backup if I were you. Mar 9, 2021 at 10:58
  • Of course, there will be created one backup every day and those backups will be kept on another server for a very long time :) Mar 9, 2021 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.